Counting records matching criteria

[OssieMac]

I have the following queries that count records matching particular criteria.
One counts ProdPrefix="C" and
the other counts ProdPrefix="M".

Output is in 2 columns for each query as follows
DonorName ProdPrefixC
DonorName ProdPrefixM

How can I put the whole lot together to get an output with 3 columns as
follows

DonorName ProdPrefixC ProdPrefixM

SQL generated in query design for each query.
SELECT DonatedProducts.DonorName, Count(DonatedProducts.ProdPrefix) AS
CountPrefixC
FROM DonatedProducts
WHERE (((DonatedProducts.ProdPrefix)="C"))
GROUP BY DonatedProducts.DonorName;

SELECT DonatedProducts.DonorName, Count(DonatedProducts.ProdPrefix) AS
CountPrefixM
FROM DonatedProducts
WHERE (((DonatedProducts.ProdPrefix)="M"))
GROUP BY DonatedProducts.DonorName;

 

[KARL DEWEY]

Try this --

SELECT DonatedProducts.DonorName, Sum(IIF([DonatedProducts].[ProdPrefix], =
"C", 1,0) AS CountPrefixC, Sum(IIF([DonatedProducts].[ProdPrefix], = "M",
1,0) AS CountPrefixM
FROM DonatedProducts
GROUP BY DonatedProducts.DonorName;

 

[John Spencer]

SELECT DonatedProducts.DonorName
, Sum(Abs(ProdPrefix="C")) AS CountPrefixC
, Sum(Abs(ProdPrefix="M")) AS CountPrefixM
FROM DonatedProducts
WHERE DonatedProducts.ProdPrefix IN ("C","M")
GROUP BY DonatedProducts.DonorName;

This works since ProdPrefix = "C" will return -1 or 0, so you sum up those and
get a negative number of the count. Abs strips off the negative sign.

Or another way
SELECT DonatedProducts.DonorName
, Count(IIF(ProdPrefix="C",1,Null)) AS CountPrefixC
, Count(IIF(ProdPrefix="M",1,Null)) AS CountPrefixM
FROM DonatedProducts
WHERE DonatedProducts.ProdPrefix IN ("C","M")
GROUP BY DonatedProducts.DonorName;

This work because COUNT counts the presence of a value, but does not count
nulls. The IIF statement returns 1 (or any other value you want) if true and
null if false.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I have the following queries that count records matching particular criteria.
One counts ProdPrefix="C" and
the other counts ProdPrefix="M".

Output is in 2 columns for each query as follows
DonorName ProdPrefixC
DonorName ProdPrefixM

How can I put the whole lot together to get an output with 3 columns as
follows

DonorName ProdPrefixC ProdPrefixM

SQL generated in query design for each query.
SELECT DonatedProducts.DonorName, Count(DonatedProducts.ProdPrefix) AS
CountPrefixC
FROM DonatedProducts
WHERE (((DonatedProducts.ProdPrefix)="C"))
GROUP BY DonatedProducts.DonorName;

SELECT DonatedProducts.DonorName, Count(DonatedProducts.ProdPrefix) AS
CountPrefixM
FROM DonatedProducts
WHERE (((DonatedProducts.ProdPrefix)="M"))
GROUP BY DonatedProducts.DonorName;

SELECT DonorName,
Sum(IIf(ProdPrefix="C",1,0)) AS CountPrefixC,
Sum(IIf(ProdPrefix="M",1,0)) AS CountPrefixM,
FROM DonatedProducts
GROUP BY DonorName

 

[OssieMac]

Thank you Karl, John and Marshall. I really appreciate the help. I now have
it working in this simple mode although I have to add some more fields but
hopefully I will be OK with that.

In case anyone else wants to use the examples at a later date there were
syntax errors in answers by Karl and Marshall. (Karl's had commas preceding
the = signs and Marshall's had a comma after AS CountPrefixM. I was able to
fix them so I'll post the correct syntax. Please don't take it as an attack
on you because I really do appreciate the help. I am only trying to continue
the help for others.

SELECT DonatedProducts.DonorName, Sum(IIf(ProdPrefix="C",1,0)) AS
CountPrefixC, Sum(IIf(ProdPrefix="M",1,0)) AS CountPrefixM
FROM DonatedProducts
GROUP BY DonatedProducts.DonorName;