Query,Count function (1 Viewer)

[MAnneken]

I am trying to get a query to return a value of 0. The COUNT function works as long as a value exists in the field.

The table is called SSRI
The field is named TS-B
TS-B can have 5 different values NO,NTA,NDP,MP,SP
TS-B is required to have one of those 5 values.
If a set up a query with column 1: Field TS-B, Table SSRI, Total- "Group by", criteria "MP" and column 2: Field TS-B, Table SSRI,Total- "Count", this query works to total the rows that have MP if MP exists.

How can I get this query to return a "0", if there are not any entries for MP?

Thanks for your help

 

[Jon K]

You should use WHERE instead of GROUP BY.

Column 1:
Field: NumOfMP: TS-B; Table: SSR1; Total: Count; Show

Column 2:
Field: TS-B; Table: SSR1; Total: Where; Criteria: "MP"


Or you can directly type in the SQL View:

SELECT Count([TS-B]) AS NumOfMP
FROM SSR1
WHERE [TS-B]="MP";

 

[Pat Hartman]

Why not count all the values in a single query?

Query1:
Select [TS-B], Count(*) As YourCount
From SSRI
Group By [TS-B];

If you always want a row even when one of the values has no entries, create a table that includes all possible values for [TS-B]. Then join this table to the above totals query with a Right Join.

Select Query1.[TS-B], Nz(Query1.YourCount) As YourCount
From tblLookup Inner Join Query1 on Query1.[TS-B] = tblLookup.[TS-B];