Unmatched Query returns zero records

[thedawn]

Hi There

I currently use an unmatched query in access that counts the number of records that meet the unmatched criteria. Sometimes this query will return no records.

I need to use the results of this query in a following query that totals two queries together (the one mentioned above and another similar query, They could both return no records so will need to do the same with the other query).

What can I do to get this query to return 0 instead of zero number of records

 

[jdraw]

What can I do to get this query to return 0 instead of zero number of records


You could explain what this quote means to you with an example to help readers understand.

Well you could show the SQL for the query(ies) involved.

 

[thedawn]

Sorry - it was just an initial question.

The query is as follows and sometimes it will return records but sometimes it will have no records

SELECT Count([EMPLOYEE DETAILS].[People No]) AS [CountOfPeople No]
FROM [EMPLOYEE DETAILS] LEFT JOIN [People attended allergen] ON [EMPLOYEE DETAILS].[People No] = [People attended allergen].[People No]
WHERE ((([EMPLOYEE DETAILS].[Labour Level]) Not In (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxxxxxxxxxxxxxxxxx,xxxxxxxxxxxxxxxxxx)))
GROUP BY [People attended allergen].[People No], [EMPLOYEE DETAILS].[Food Manager], [EMPLOYEE DETAILS].[Currently away from business], [EMPLOYEE DETAILS].[Date Left]
HAVING ((([People attended allergen].[People No]) Is Null) AND (([EMPLOYEE DETAILS].[Food Manager])=Yes) AND (([EMPLOYEE DETAILS].[Currently away from business])=No And ([EMPLOYEE DETAILS].[Currently away from business])=No) AND (([EMPLOYEE DETAILS].[Date Left]) Is Null));

The attachment is what shows when there is no records returned by the query and it isn't Null it is no records returned

Thanks

 

[arnelgp]

if in the Where Clause, there
is no record returned, then
there is nothing to Count for,
am i correct?

 

[thedawn]

Yes that's correct - I have got myself that confused now that I just can't work out what is the way around this

 

[thedawn]

Sorry for updating this but I am just wondering if I have asked the question incorrectly - what I want to do if possible is detect whether this query returns zero results even in another query. The reason I ask is that when I try any test on this query I get a blank records. I have tried IsNull, Nz, IsEmpty, IsMissing and am still getting blanks

 

[thedawn]

Hi There I have now solved this. I took the count off this query and left it as an unmatched query. I then created another query and left joined to the original EMPLOYEE DETAILS table and used the single output field from the original query as the destination field in the new query and counted this field. This gave me a zero. So I needed an additional step but I am now able to sum any of the queries even ones that return no records