Desperately need help - stressed - counting in Access

[danlindley]

hi there;

hope you can help, i have a query that displays all the people in my database who are male, then another to for all the people who are female.

Rather than displaying in a form the word "male" or "female" written however many times, i want it to return a number, so it can tell me how many people there are that are male and how many are female.

I have tried using this SQL (count function) thingy and all i get is problems when i try and work it.

I am hopeless at this so could anyone offer me a way to filter data and count the results rather than listing them?

i'll want to use the results in a form (to display) and then again in a report, so i may need to write it to another table for storage, which is cool.

please help, seriously stressed.

Dan

[vass0922]

select sex, count(sex)
from tablename
group by (sex)

this will return

female 30
male 40

or something like that
but that is the generic format of the count function
maybe post up the sql so we can look at it


You have to remember when using the COUNT function, you have to use the GROUP BY clause. Once you do that, anything in teh SELECT part must also be in teh group by.

[danlindley]

Thank you,

after a hell of a lot of gramatical changes, i finally got it to work, and this i am grateful for. my problems originated with a semicolon that access placed in the code, and i got a lot of "punctuation incorrect" errors.

Thanks.

[Tekk]

Yeah access automatically places ';' in SQL statements at the end (or what it assumes to be the end). Can sometimes cause things to screw up.