How to list a Count which is 0

[RobertSOakes]

I am trying to write a fairly complicated SQL statement in Microsoft Access and I don’t quite know how to do it.

The Basic Challenge: I need to query for individuals who have received 4 or fewer employee evaluations within a given time period. This works just fine as long they have as they have at least one evaluation, but fails if they don’t have any (the vast majority of the group). Does anyone know of any way to get it to include the results for individuals who have zero?

Here is the code I’ve put together so far:

SELECT [CSU Staff].ID, [Last Name] & ', ' & [First Name] AS FullName, [CSU Staff].[Currently Employed], Count([EvalID]) AS [Evaluation Count]

FROM [CSU Staff] LEFT JOIN [Surgical Employee Evaluations] ON [CSU Staff].ID = [Surgical Employee Evaluations].[Peer Evaluated]

WHERE ((([Surgical Employee Evaluations].Date)>#12/29/2006#))

GROUP BY [CSU Staff].ID, [Last Name] & ', ' & [First Name], [CSU Staff].[Currently Employed]

HAVING ((([CSU Staff].[Currently Employed])=Yes))

ORDER BY [Last Name] & ', ' & [First Name];


I thought I may have had better luck writing a Subquery statement, but this is proving beyond my rather meager abilities. Also, I will eventually need to make this date dependant, but I have already written some Visual Basic which will automatically construct the date ranges. I just need to get Acess to Include Individuals who do not have any evaluations.

Any advice or ideas would be greatly appreciated.

 

[shift2076]

Are the values for those individuals zero, or are they null values? If so, I believe you have to use the Nz function to incorporate them into mathmatical functions.

Matt

 

[RobertSOakes]

Are the values for those individuals zero, or are they null values? If so, I believe you have to use the Nz function to incorporate them into mathmatical functions.

Uhm, I know this will sound stupid, but I don't know the answer to that question. Right now, the data is stored in two different tables. I have one table which contains all of my employee information and a second which includes just the evaluation data. The evaluation data is linked to the employee data by the employee's ID number. As written, I am only the Ids of completed evaluations to try and get an idea of how many have been compelted for a given individual. I want their name to fall off the available list in a form if that number is greater than 5.

However, the names of people who have not had an evaluation completed for them won't show up in the list with a count of zero. I guess, in a way, I am trying to get it to count non-existant records and tell me nothing is there. is that different than trying to count a null value in a record?

Rob

 

[pbaldy]

Since you've said the second table wouldn't contain any records for that person, nz won't help here. The LEFT JOIN should return a count of 0 for anyone in the CSU Staff table without a record in the other. However, it may fall over when you add the date criteria. If you're only trying to look at how many evaluations since a certain date, you'll probably have to create a query that returns those from the second table, then use that instead of the second table in this query.