Creating A Query to Count Gender and Ethnicity (1 Viewer)

[Dnalor]

Hello all,

So, I'm having trouble creating a query to give me a count of all the males with a certain ethnicity and females with a certain ethnicity. I have a table that is setup for those two categories. It has the ethnicity and gender listed with check boxes (requested by the user) and when ever I run the query with the count total, for example : Caucasian Males, it returns all Caucasians whether male or female. I've tried a few different ways to get this done, but with no success. Anyone know of an easy way to get this info? Here is a screenshot of my table if it helps:

 

[pr2-eugin]

For some reason my guts tell me you have a fundamental DB Design issue. You should be having is, two tables.

tblEthnicity
ethnicID - PK
ethnicity

tblPerson
personID - PK
ethnicID - FK
nameOfPerson
ageOfPerson
gender

So your sample data will be
tblEthnicity

ethnicID    ethnicity
1            Caucasian
2            African American

tblPerson

personID    ethnicID    nameOfPerson    ageOfPerson    gender
1                1        John            25            M
2                1        Sara            27            F
3                2        Mike            29            M

Then your query will be as simple as using a GROUP BY clause.

 

[Dnalor]

Your gut is correct. The database I have inherited is quite the fundamental design nightmare. I'm working slowly but surely to correct everything while also making the user happy. Thanks for the input. I will try to split it up that way. Thanks for the help!