Include NIL/Empty values in a query/report using WHERE

[jasocke2]

What I have, I think is quite a simple question, I have looked around for quite a while but can't seem to find the solution.
What I have is a database with 1 table, the fields that I am concentrating on are:

ID (Auto number)
Date Received (date)
Shop Name (Text)
Error Log A (Yes/No)
Error Log B (Yes/No)
Error Log C (Yes/No)

What I want is a report as follows:

| Shop name | Number of Applications received | Total clean | % clean |
Example 1 4 1 25%
Example 2 3 0 100%
Example 3 0 0 N/A
Example 4 2 2 0%

I have started to create some queries to do this but what I am facing is that when I do a count of all the records which have NO errors, it doesn't return the data which don’t meet that criteria (ie. if a shop has no error free applications like Example 4 above)
Within the query: for Error Log A,B & C I am putting it as a 'where' clause with the criteria to be '0' (to indicate NO) so it counts the number of shops which have fully clean applications (No errors for A.B,and C) however if a shop has had no clean applications, no result comes back for that shop.
Is there any way to get around this so I can produce the report as above?

I have no problem getting the number of applications a shop has received, it's getting how many of those where 'clean applications' (no having any errors).

Thanks in advance

[MikeTheBike]

Hi

I am putting it as a 'where' clause with the criteria to be '0' (to indicate NO) so it counts the number of shops which have fully clean applications

This is a little ambiguous, perhaps you could post, at least, the full WHERE clause syntax to give us more of a clue !?

MTB

[jasocke2]

It's just in the query builder, not in code (I'm not great with SQL!)
Thanks

[MikeTheBike]

It's just in the query builder, not in code (I'm not great with SQL!)
Thanks

Hi again

If you Right Click on the top half of the Query Designer and select 'SQL View' if will change to an SQL view (surprise, surprise!). You can copy this text string and paste it in your post, then we can have look at it.

In that event perhaps some else will oblige as I am on hols for the next week.



MTB


ps. To change back to design view Right Click the 'SQL View' Title Bar as select as required.