Count different values in one field

[krets]

I was wondering if there is a way to create a query that would return the counts of each given value within one column. The example that I am working with is a database for tracking technician errors in a pharmacy. The technicians are assigned to work groups called cells. I've written a query to join several tables so I end up with:

TechInit(key) | CellNum | AITDate | RxNum(prescription number)| Severity | ErrorCode

Right now the Severity field is a combo with values of "Major" and "Minor". However, this could change to a High, Med, Low situation in the near future.

What the pharmacists would like to see is a report of the number of errors committed per cell with a count of the number of Major errors vs the number of Minor errors.

Is there a way to do that within a query?

 

[Crowley16]

yes, with the juditious use of the group by clause...

select
column1, count(column1)
from
table
group by
column1

--------------------
Procrastinate Now!

 

[krets]

That counts the values but I would also like to group that by cell number so it would look like:

            | Total Errors | # of Maj | # of Min |
Cell Number |       #      |     #    |     #    |

I can get the total errors with a crosstab but I'm not sure how to count the individual values of "Major" and "Minor".

 

[PHV]

A starting point:
SELECT CellNum, Count(*) AS [Total Errors], Sum(IIf(Severity='Major',1,0)) AS [# of Maj]], Sum(IIf(Severity='Minor',1,0)) AS [# of Min]
FROM yourTable
GROUP BY CellNum

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

 

[krets]

Here's the query I was able to create after a little more Googling:

TRANSFORM Val(Nz(Count(qryAITbyCell.Key),0)) AS CountofKey
SELECT qryAITbyCell.CellNum, Count(qryAITbyCell.Key) AS Total
FROM qryAITbyCell
GROUP BY qryAITbyCell.CellNum
PIVOT qryAITbyCell.Severity In ("Major","Minor");

PHV: Is there a difference in performance or other significant factor that would make using your query a better idea?