count either male or female from one column in query

[chedd via AccessMonster.com]

Hi

I have a column in access which records either male or female from a drop
down menu. I would like to run a query to show how many females and males we
have recorded. At present i only know how to do a total count of both?

Please can you help or irect myself in the right direction.

Thanks

Chedd

 

[Bob Barrows]

Hi

I have a column in access which records either male or female from a
drop down menu. I would like to run a query to show how many females
and males we have recorded. At present i only know how to do a total
count of both?

Please can you help or irect myself in the right direction.

A few details would have helped (are you storing M and F? or Male and
Female? or 1 and 2?) I will assume you are storing M for male and F for
female. Revise this example if that is not the case:

Select
Sum(iif([Sex]="M',1,0)) as TotalMales,
Sum(iif([Sex]="F',1,0)) as TotalFemales,
etc.

 

[Michel Walsh]

With Jet, you can use a crosstab:


TRANSFORM COUNT(*)
SELECT "all"
FROM tableName
GROUP BY "all"
PIVOT sex


or, why not, a standard total query using sum:



SELECT ABS(SUM(sex= "male")) As CountOfMale,
ABS(SUM(sex="female")) AS CountOfFemale
FROM table





Indeed, there is no GROUP BY clause, in that case, since the whole table
would become the single group you are interested by. You also know the
explicit values ("male", "female"). If the field is BOOLEAN rather than
TEXT, assuming true (or check) is male, and false (uncheck) is female:



SELECT "all", ABS(SUM(sex= true)) As CountOfMale,
ABS(SUM(sex=false)) AS CountOfFemale
FROM table






Vanderghast, Access MVP