CODEWITHSUNDEEP
sqlms-accessms-access-2007
user1132612
user1132612

Reputation: 77

MS Access 2007 : Count Query

I have created a database which will count the amount of procedures a person has completed and then a query will display the number of times they recieved a particular score for a particular procedure. These scores being "N" "B" and "C". I use the count function to work this out.

The procedures are selected from a table. Within the query would it be possible for me to output the procedure name [Adult Procedure] in the first column even if there is no data for that procedure.

Here is the query i have produced but it only outputs data for procedures that have data. I would like it to print all procedure names and if no data is contained to simply add zero to the column.

Here is a link to an image the results of the query http://www.flickr.com/photos/mattcripps/6641851977/ I want the first column to include all procedures or which there are about 20.

TRANSFORM Count(tblEntryData.[Entry ID]) AS [CountOfEntry ID]
SELECT tblEntryData.[Adult Procedure], Count(tblEntryData.[Entry ID]) AS [Total Of Entry ID]
FROM tblEntryData
GROUP BY tblEntryData.[Adult Procedure]
PIVOT tblEntryData.Grade;

Thanks in advance

Upvotes: 0

Views: 476

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

If the a table of possible [Adult Procedure] existed all you would need is a LEFT JOIN. This would be the recommended solution

TRANSFORM Count(ed.[Entry ID]) AS [CountOfEntry ID]
SELECT ap.[Adult Procedure], Count(ed.[Entry ID]) AS [Total Of Entry ID]
FROM tblAdultProcedure ap
     LEFT JOIN tblEntryData ed
     ON ap.[Adult Procedure ID]  = ed.[Adult Procedure ID]
GROUP BY ap.[Adult Procedure]
PIVOT ed.Grade;

However it is still possible to do it without using another table by deriving the table via another SQL statement (although it will be slower)

TRANSFORM Count(ed.[Entry ID]) AS [CountOfEntry ID]
SELECT ap.[Adult Procedure], Count(ed.[Entry ID]) AS [Total Of Entry ID]
FROM (SELECT DISTINCT [Adult Procedure] FROM tblEntryData) ap
     LEFT JOIN tblEntryData ed
     ON ap.[Adult Procedure]  = ed.[Adult Procedure]
GROUP BY ap.[Adult Procedure]
PIVOT ed.Grade;

Note: you can also put SELECT DISTINCT [Adult Procedure] FROM tblEntryData into a query and then use as you would a table.

Upvotes: 1

Related Questions