Cross tab table

[Guest]

Following is my SQL and it is base on a query that I sort by date criteria.
The problem is when I change the date and there is no data for some
inspectors the cross tab does not list those inspectors in the table.

TRANSFORM Nz(Count([15 < 07].[DATE REFERRED]),0) AS [CountOfDATE REFERRED]
SELECT [15 < 07].InspectorID, Count([15 < 07].[DATE REFERRED]) AS [Total Of
<15]
FROM [15 < 07]
GROUP BY [15 < 07].InspectorID
PIVOT [15 < 07].LEVELS;

I get the following results which is good.

InspectorID Total Of <15 0 5 10 11 12 13 14
Brian Kimball 1 0 0 0 0 0 0 1
Christie Jones Ringfield 61 0 1 19 17 16 8 0
India Carlton 64 1 0 15 22 20 6 0
Sharon Malone 63 0 0 14 23 21 5 0

When I change the date criteria I get the following

InspectorID Total Of <15 10 11
Brian Kimball 1 1 0
Christie Jones Ringfield 2 1 1

I need all inspectorID that are listed in the first example.
How can I get accomplish this ?

 

[John Spencer]

Do you have a table with the inspectors in it? If not, what you want can be
done using nested queries.

Assuming you do have a list of the inspectors in a table called Inspectors,
your query would look something like the following.

TRANSFORM Nz(Count([15 < 07].[DATE REFERRED]),0) AS [CountOfDATE REFERRED]
SELECT Inspectors.InspectorID
, Count([15 < 07].[DATE REFERRED]) AS [Total Of <15]
FROM Inspectors LEFT JOIN [15 < 07]
ON Inspectors.InspectorID = [15 < 07].InspectorID
GROUP BY Inspectors.InspectorID
PIVOT [15 < 07].LEVELS;

If you don't have a table that lists all the inspectors (one record for
each), you could create a query that would get a unique list of inspectors
and using that saved query in place of the Inspectors table, you should be
able to generate the desired outcome.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..