Display 0 if no data is available

joeserrone

The cat of the cul-de-sac
Local time
Today, 19:44
Joined
Dec 17, 2006
Messages
164
Hello Everyone,
I'm attaching a sample of my database here, I would like to see if the query I created can place a 0 volume and display the Site even if no data is present in the Table. For example on 04/21/09 there is no data available for Orlando and Philadelphia in Queue 03 only India has data for that queue on that day. How can I still display Orlando 0 and Philadelphia 0 on 04/21/09
Thanks
 

Attachments

Sounds like you need the nz() function.

nz([field to display],"Value to display when null")
 
I thought that at first too and I tried to put
Total: NZ([Volume],0) but didn't produce the result I wanted. I guess because for 04/21/09 there is no data at all for the Orlando and Philadelphia Site. Any ideas how I can overcome this>?????


Thanks
 
If there are no records between the selected dates, then expand the date range. Other than that, I'm not sure what your asking.
 
You create two queries, the one I'm going to concentrate on is the "outer query".

To give you an example Debtors Balances will have an Invoice but may not have an payments so your "outer query" has the Client Table left join to Payments. So it includes all Clients and any payments with zero for none using

Code:
Foreign Pay: Sum(IIf(IsNull([Bank Trans Detail Code]),0,[Bank Trans Detail Value]))

So substitute this rational to your volume problem.

Simon
 
Makes sense but how can I generate a query that will show the dates volume is not existent when there are no entries for that day in the table?
 
The idea is that the first and second queries all the records you require are contained in the queries and the Dates Volume value is <> 0 if records are present.

Simon
 

Users who are viewing this thread

Back
Top Bottom