Counting text values

[Fish218]

I want to run a query that counts one text value "n" in a table and also counts all other possible text values other than "n" but without grouping them into each individual text value.



For example:

In a table I have 10 "n" values, 10 "a" values, 5 "b" values, and 1 "c" value. i want the query to return a single line showing the count of "n" = 10 and the count of "a" and "b" and "c" = 16. Any suggestions?


n not n
10 16

[ketbdnetbp]

Fish218 - If the alpha values are in one specific field, you could create a query based on the table that contains the alpha field. Then add two calculated fields/columns.
Col1 - N: IIf([fieldnamewithalphavalues]= "n",1,0)
Col2- O: IIF([fieldnamewithalphavalues]<> "n",1,0)

Next, create a new query based upon the first, click the summary button, then sum the fields N and O. Last, run the total/aggregrate query.

This should produce the desired results. However, if the alpha values are not in a specific field or the field contains other elements, this method will not work.

All the best,

Jim

[uncletreetrunk]

Hey fish,

I think you can use the DCount function so in your query in the field box:

DCount("FieldName","tblName","Type = 'n' ")

Source:http://www.techonthenet.com/access/f...ain/dcount.php

[Fish218]

The two seperate column IIF statement worked well! Thanks.