What's the expression to be added...........................

[GraeagleBill]

Looking at the snippet screenshot (below) of a queries design view, I need to add, I think, two expressions to the query that returns the number of records where PCCode = "N" and separately the number of records where PCCode = "R"



I tried this: NofN: Sum([PCCode]=Chr(78)) but I get an error. What's the proper expression to get the counts?

[davegri]

Give this a try

SELECT DISTINCTROW [tblMembers].PCCode, Count([tblMembers].PCCode) AS CountOfPCCode
FROM [tblMembers]
WHERE PCCode = "N" or PCCode = "R" 
GROUP BY [tblMembers].PCCode;

[GraeagleBill]

I will try to incorporate your suggestion into the query, while at the same time breaking up the count approach to produce both a "CountOfPCCodeN" and "CountOfPCCodeR". Something like CountOfPCCodeN: Count([tblMembers].PCCode = 'N') and CountOfPCCodeR: Count([tblMembers].PCCode = 'R'). I'll post back one way or another.

[pbaldy]

You can Sum an IIf():

sum(IIf(PCCode = "R", 1,0))

[GraeagleBill]

I tried your suggestion as an expression, but as you can see in the screenshot it failed.

[pbaldy]

The Sum makes it a totals query, so you'd want to select that and the appropriate option for each field (Group By, Where, etc).