By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,524 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Apr 10 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 634
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
Apr 10 '08 #2

P: 2
It's just in the query builder, not in code (I'm not great with SQL!)
Thanks
Apr 10 '08 #3

Expert 100+
P: 634
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.
Apr 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.