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