Microsoft Access - Counting Gender (Male/Female)

How do you create a query that will count the amount of males in a class and the amount of females in a class and display them on a single query that displays CLASS NAME, GENDER, and NUMBER OF STUDENTS
MLaner9Asked:
Who is Participating?
 
peter57rCommented:
Drop this into the SQL view of a new query.


Select [Class name], [Gender], Count([lastname]) as NumberOfStudents
From MyTable
Group By [Class Name], [Gender]

Substitute you own table name and field to count

Pete

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Don't accept this as an answer.  I am using Pete's solution to make a list of student with gender totals on the top of list for class selected:

Select [Class name], [Gender] From MyTable Union Select "<Total (Male/Female):" As [Class name], DCount("*","MyTable","Gender='Male'") & "/" & DCount("*","MyTable","Gender=Female") As Gender From MyTable Where [Class name] = IIF(NZ(Forms!MyForm!txtClassName,"")<>"",[Class name],Forms!MyForm!txtClassName) Order By [Class name]

If you leave txtClassName on form MyForm blank, it will make list for all classes.

Mike
0
 
stevbeCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: peter57r {http:#9729027}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.