force query to show month with no data

  • Thread starter Thread starter DavidW
  • Start date Start date
D

DavidW

I have got a query that caculates the sum of a field using Nz, also in the
same query I have got a month field and a yearfield.
What I am trying to do is get the query to show all months, which it does
when there is data present for each month, but if you delete the data, it
shows nothing for that month.
Can a query be force to show nulls as zeros for a month if no entries are
detected.
In the query also I am setting criteria for the year and also for a range of
months like >4, which gives me jan, feb, march.

What I have got now is like the following
monthfield yearfield nz([totalgal],0)
1 2003 10
2 2003 15

What I would like to end up with is
monthfield yearfield nz([totalgal],0)
1 2003 10
2 2003 15
3 2003 0

Thanks
David
 
The short answer is no.

Queries are row-based. They have no way to substitute a value for a row
that doesn't exist!

You can 'simulate' this data by using UNION ... like so:

\\\
SELECT monthfield As Month, yearfield As Year, nz(totalgal,0) As Total
UNION
SELECT '3' As Month, '2003' As Year, 0 As Total
///

However, you will still have to create some logic in your program to
control/automate the addition of these 'dummy' select statements. Otherwise
you have to do it all by hand every time and I KNOW you don't want to do
that...

i.e. if march, april, may, june were all missing, your complete statement
would need to look like this:

\\\
SELECT monthfield As Month, yearfield As Year, nz(totalgal,0) As Total
UNION
SELECT '3' As Month, '2003' As Year, 0 As Total
UNION
SELECT '4' As Month, '2003' As Year, 0 As Total
UNION
SELECT '5' As Month, '2003' As Year, 0 As Total
UNION
SELECT '6' As Month, '2003' As Year, 0 As Total
///

so you would probably want to write some VBA code to automatically generate
that statement for you...

-----------------------------

Another way I have approached similar problems in the past: Put the report
data in a temp table and add 'dummy' records directly to the table. Then
run your report off the temp table. It's basically the same concept, just
that now you are _explicitly_ creating the temp table. (Conceptually, it's
the same as what you're doing with the UNION technique - creating temp data
that will just be used to populate this report, then disposed of...)

HTH! Good luck! =)
 
Back
Top