Microsoft Access - Counting Gender (Male/Female)

Posted on 2003-11-11
Last Modified: 2006-11-17
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
Question by:MLaner9
LVL 77

Accepted Solution

peter57r earned 125 total points
ID: 9729027
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


LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9729213
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.

LVL 39

Expert Comment

ID: 10025310
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.

EE Cleanup Volunteer

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now