How to populate a zero if query results are blank.

AWHN
AWHN used Ask the Experts™
on
I have created a count query, and if there are no results, my report does not show.  How can I create a statement that will return a zero in my query, if there are no query results?  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
jadedataMS Access Systems Creator

Commented:
in design view -- ColumnName:  nz([FieldName],0)

in sql -- nz([FieldName],0) as ColumnName

change the object names of things as needed
A count query like this?

SELECT somefield, Count(*) AS n FROM sometable GROUP BY somefield

Use this instead:

SELECT somefield, Nz(Count(*)) AS n FROM sometable GROUP BY somefield

In the query grid, that becomes:

    n: Nz(Count(<something>))
    Expression    <----- NOT Count, Sum, etc.

Cheers!
(°v°)

Author

Commented:
I have multiple queries.  One pulls all data with a specific decision code, then I use another query to get a count of those codes.  Below are the two queries.  Where should I be placing the solution?

1st query:
INSERT INTO Appeals ( Auth_Number, Decision_Code, Decision_Date )
SELECT [KBI Table].Auth_Number, [KBI Table].Decision_Code, [KBI Table].Decision_Date
FROM [KBI Table]
WHERE ((([KBI Table].Decision_Code)="apd" Or ([KBI Table].Decision_Code)="apm" Or ([KBI Table].Decision_Code)="aa" Or ([KBI Table].Decision_Code)="apa"));

2nd query
SELECT Count(Appeals.Auth_Number) AS CountOfAuth_Number, Appeals.Decision_Code
FROM Appeals
GROUP BY Appeals.Decision_Code
HAVING (((Appeals.Decision_Code)="apa" Or (Appeals.Decision_Code)="aa" Or (Appeals.Decision_Code)="grva"));

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

2nd query
SELECT Nz(Count(Appeals.Auth_Number),0) AS CountOfAuth_Number, .......

(°v°)

Author

Commented:
Ok, I tried that in the 2nd query, and still nothing.  I created a report using that 2nd query, and still nothing shows, because there are not results.  Is there anything else I can do?
If you get no rows, this will not work. It was meant for rows where the result would be Null.

I'm not sure how you would report rows that don't exist, though. I guess I have misunderstood something here...

(°v°)
jadedataMS Access Systems Creator

Commented:
You may need to start with a table of values that represent all the possibles that you need to report.

A left join to the current data on that key will produce a null that you can use for this purpose

Author

Commented:
How can I show zero, or null if there are no results from my 1st query? Therefore, my 2nd query has nothing to count.  Is there a way to state, if there are no results, enter zero?

Author

Commented:
jadedata,

I have a table with all codes i need to report.  Can you explain this left join you are talking about?
Yes, but enter zero where? That's the whole point. We can't enter zero in a non-existent row. If you are talking about a control on a report, and you have made certain that this control exists whether there is data or not, use the same trick there:

    = Nz( [CountOfAuth_Number], 0 )

(°v°)
Top Expert 2006

Commented:
u could create 3 seperate count queries with unions that return 0, then use that as a inner query
eg


SELECT SUM(CountOfAuth_Number), Decision_Code
FROM (
    SELECT Count(Appeals.Auth_Number) AS CountOfAuth_Number, Appeals.Decision_Code
    From Appeals
    where Decision_Code = "apa"
    GROUP BY Appeals.Decision_Code
    Union all
    SELECT TOP 1 0, 'apa'
    From Appeals
   
    Union all
   
    SELECT Count(Appeals.Auth_Number) AS CountOfAuth_Number, Appeals.Decision_Code
    From Appeals
    where Decision_Code = "aa"
    GROUP BY Appeals.Decision_Code
    Union all
    SELECT TOP 1 0, 'aa'
    From Appeals
   
    Union all
   
    SELECT Count(Appeals.Auth_Number) AS CountOfAuth_Number, Appeals.Decision_Code
    From Appeals
    where Decision_Code = "grva"
    GROUP BY Appeals.Decision_Code
    Union all
    SELECT TOP 1 0, 'grva'
    From Appeals
)
GROUP BY Decision_Code
Top Expert 2006

Commented:
I did it this way because if u had no records for one particular decision code then no row is returned.
And because no row is returned, u cant get anything

so I added a union whereby I just dumped a zero

I had to do this 3 times, one for each filter

Then I treated that like a table, and sumed the counts
You should then get zero where filter doesnt satisfy

hope this makes sense


jadedataMS Access Systems Creator
Commented:
Select  TC.Code, nz(RQD.CountOfCodes,0)
FROM TblOfCodes AS TC LEFT JOIN ResultQueryData AS RQD ON TC.Code=RQD.Code

does that help some?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial