counting

[craigey]

Hi again

if i have a field called sex with the listbox values Male and Female.

How do I go about creating a field of total males & a field of total females. I know you can use a formula such as =COUNTIF(B2:B41,"Male&quot in excel, but i don't know access that well.

Thanks

 

[mikey69]

probably quickest to use two aggregate queries with criteria set to M & F respectively. Aggregate queries are constructed as select queries and then using the icon with the Greek letter Sigma. search the help file for more info.

 

[craigey]

Thanks Mikey.

After a bit of research. I managed to get the aggregate query to work by looking for "male" Or "female", and using the count and expression queries.

The problem I've got now is that I need to know the number of female's and the number of males. I know that I could just look for male's in the same way as finding the total. I could then subtract males from the total to get the number of females.

Only problem is that when I try to use the count query to count the males. The expression in the total field changes from "male" Or "female" to ("male" Or "female&quot And "male" which returns no results to either males or to the total.

I can e-mail the DB if it's easier for anyone to look at & understand.

 

[mikey69]

Send it to youmike@mweb.co.za