If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting records matching criteria
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; -- Regards, OssieMac |
#2
|
|||
|
|||
Counting records matching criteria
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; -- Build a little, test a little. "OssieMac" wrote: 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; -- Regards, OssieMac |
#3
|
|||
|
|||
Counting records matching criteria
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 OssieMac wrote: 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; |
#4
|
|||
|
|||
Counting records matching criteria
OssieMac wrote:
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 -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
Counting records matching criteria
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; -- Regards, OssieMac "Marshall Barton" wrote: OssieMac wrote: 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 -- Marsh MVP [MS Access] . |
Thread Tools | |
Display Modes | |
|
|