Calculation Query with Criteria

[czarj]

Hi all,

Currently I have an access database that stores a variety of healthcare records. It contains fields such as race and sex that I would like to count by a given criteria. More specifically lets say the sex field contains only three numbers, 0(=males), 1(=females) and 2(=not collected). How can I count the number of of 0's, the number of 1's and number of 2's through a query?

I have been able to do one or the other (ie. just count 0's), but when 've tried to enter multiple expressions (either in design view or SQL view) I get any number of errors. Here is an example of the SQL I have been trying to use:

SELECT Count(temp.Sex) AS Sex1
FROM temp
WHERE (((temp.Sex)=0));

Thanks for you help,

CzarJ



--- You must not fight too often with one enemy, or you will teach him all your tricks of war.

 

[PHV]

Something like this ?
SELECT Sex, Count(*) As CountOf
FROM temp
GROUP BY Sex;
Or this ?
SELECT Sum(IIf(Sex=0, 1, 0)) As Sex0, Sum(IIf(Sex=1, 1, 0)) As Sex1, Sum(IIf(Sex=2, 1, 0)) As Sex2, Count(*) As Total
FROM temp;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[czarj]

thanks, that second SQL statement worked really well.

~CzarJ


--- You must not fight too often with one enemy, or you will teach him all your tricks of war.