Count two columns in xtb query

[krets]

I have this query:

TRANSFORM Val(Nz(Count(qryMgrAIT.Key),0)) AS CountOfKey
SELECT qryMgrAIT.tblAITInfo.TechRxEInit, qryMgrAIT.CellNum, qryMgrAIT.Location, Count(qryMgrAIT.Key) AS [Total Of Key]
FROM qryMgrAIT
GROUP BY qryMgrAIT.tblAITInfo.TechRxEInit, qryMgrAIT.CellNum, qryMgrAIT.Location
PIVOT qryMgrAIT.ErrorCode In ("Wrong Drug","Strength","Quantity","Missed PT Notes","Sig (Affecting Dose)","Wrong PT (Same Name)","Wrong PT (Family)","Wrong PT","Wrong Dr. Address","Wrong Ques. Asked","DAW","Refills","Write Date","Sig (Not Affecting Dose)","Wrong MD/NP/PA in Sig","Rx Omitted","Rx Should Be???","TSTF","Non-Link Image","Right Drug Wrong Form","Allergy","DC Date","Days Supply","No Call Card","Order Count","Credit Hold Procedure","Routing");

It counts each type of error made by pharmacy technicians and lists them by tech initials. There is also a column called "Severity" that ranks the severity as major or minor.

Is it possible to have a count of major vs minor errors in there as well?

 

[lameid]

No. A crosstab allows for exactly one value.

If on the other hand you want a query for major and one for minor, you could add severity to a where clause.

 

[krets]

I solved this by making two crosstab queries:

1. ErrorCode counted for each TechRxEInit
2. Severity counted for each TechRxEInit

I then created a third query to join the two crosstab queries on TechRxEInit.

Seems to be working OK.

 

[lameid]

I see, I thought you wanted to know the number of major and minor errors for each column from the pivot in your first crosstab.

Glad to see you got the results you wanted.

 

[PHV]

Did you see that ?
faq701-4524

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886