Display 0 if no data is available

[joeserrone]

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

 

[speakers_86]

Sounds like you need the nz() function.

nz([field to display],"Value to display when null")

 

[joeserrone]

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

 

[speakers_86]

If there are no records between the selected dates, then expand the date range. Other than that, I'm not sure what your asking.

 

[Simon_MT]

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

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

So substitute this rational to your volume problem.

Simon

 

[joeserrone]

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?

 

[Simon_MT]

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