Conditional sums in queries

[eislander]

Hi all,

How would I go about implementing a conditional sum/count in one field in Access 2000? For example, let's say customer C001 has two different types of transactions in April and May (A,B). What I need to do is get into one record the following:

C001:count_of_A_Apr,count_of_B_APR, count_of_A_May, count_of_B_May

I've tried this by making my first query field [customer #] and then something like "COUNT_A_APR: count(iif(TRXN="A" and month="April",1,0))

What I get is a count of [customer #].

Any suggestions?

 

[scriverb]

Check out the DCount function. You can create two extra columns in your query each using DCount to select specific records based upon the criteria expression and ultimately counting the records selected.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[dhookom]

A conditional count can most efficiently done by summing the absolute value of the condition.

For instance to count the number of males and females into separate columns by department

SELECT Department, Sum(Abs([Gender]="F")) As NumFemales, Sum(Abs([Gender]="M")) As NumMales
FROM tblEmployees
GROUP BY Department;

If you wanted to expand the condition to only FullTime employees:
SELECT Department, Sum(Abs([Gender]="F" AND [Type]="F-T")) As NumFTFemales, Sum(Abs([Gender]="M" AND [Type]="F-T")) )) As NumFTMales
FROM tblEmployees
GROUP BY Department;


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[eislander]

Thank you both. I'm sure both suggestions are in the right direction, but I went ahead and joined through queries using an intermediate table. It's not quite as elegant, but I don't have the time to experiment.

Thanks again.