Percentage (1 Viewer)

[cktcPeterson]

I have a query with list of students and and field that they pick yes or no.

I want to create a query/report to where I have a list of student and the course they sigend up for, and ther number of yes over the total of participants.


Example
Course
55 (yes)/150 (no)

How would I go about setting this up?

 

[CJ_London]

Depends on your table design and relationships but a broad answer would be to use an aggregate query, group on course , sum the yeses and count the records

select course, sum(abs(yeses)) as numberyes, count(*) as numbercount
From mytable
Group by course

then another query based on this to divide one by the other

 

[The_Doc_Man]

Not to mention that there is another aggregate called AVG that would fit in with the "SELECT" clause.

MS Access: Avg Function

This MSAccess tutorial explains how to use the Access Avg function with syntax and examples. The Microsoft Access Avg function returns the average (arithmetic mean) of a set of numeric values in a select query.

www.techonthenet.com

 

[Pat Hartman]

I have a query with list of students and and field that they pick yes or no.

This is not the way the tables should be defined.

You need a minimum of three tables. Students, Courses, StudentCourses.
The Students table contains a row for each Student. The Courses table contains a row for each course. StudentCourses contains a row for each student in a particular course. This is a typical many-many relationship. The count would be obtained from StudentCourses. You would join to the Courses table to get a description of the Course name. The query might look like

Select tblCourses.CourseName, Count(*) as StudentsEnrolled
From tblCourses Inner Join tblStudentCourses on tblCourses.CourseID = tblStudentCourses.CourseID
Group by tblCourses.CourseName;

This sample database shows how a m-m works.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk