Zero value for queries that bring back no results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChuckW - 23 Nov 2005 14:53 GMT

Hi,

I am running a series of queries that measure weekly sales for various
products. Some of them do not have any sales for the week and bring back no
results. I then create make tables out of these and import them into another
Access database and do a combined sales report. I want either the query or
the make table to insert a zero as a value for a product that had not sales
for the week. I would also like for it to remain a number as a format rather
than changing it to a text value. Can someone help with how to insert a zero
in queries that yield not results?

Thanks,

Chuck

Signature

Chuck W

Reply to this Message

Allen Browne - 23 Nov 2005 15:40 GMT

The solution will depend on your query.

Switch the query to SQL View (View menu, from query design view). I'm
assuming you will see something like:
SELECT ... Sum([tblSale].[SaleAmount]) AS SumOfAmount ...

If so, you can use Nz() to specify a zero when there is no amount. To ensure
the result is currency, use CCur() as well, so you will have:

SELECT ... CCur(Nz(Sum([tblSale].[SaleAmount]),0)) AS SumOfAmount ...

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I am running a series of queries that measure weekly sales for various
> products. Some of them do not have any sales for the week and bring back
[quoted text clipped - 14 lines]
>
> Chuck

Reply to this Message

ChuckW - 23 Nov 2005 16:32 GMT

Allen,

Thanks for your help. This doesn't appear to be working though. What I am
doing is using a date restrictor to get all all transactions involving a
certain product. There is a field in this transaction table called amount. I
then sum all records returned from this first query and then and create a
make table. Since my first query is not returning an records, the NZ
function doesn't create the zero.

Any thoughts?

Thanks,

Signature

Chuck W

> The solution will depend on your query.
>
[quoted text clipped - 25 lines]
> >
> > Chuck

Reply to this Message

Allen Browne - 23 Nov 2005 16:45 GMT

So there is no record at all?

Sounds like your query contains multiple tables, and you need an outer join.
Double-click the line joining the 2 tables in the upper pane of the query
design window. Access pops up a dialog offering 3 choices, with the first
(inner join) as the default. You need one of the other two (left or right
join.)

Once you get that going, you can then use Nz() behind the scenes to convert
the null to a zero.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 44 lines]
>> >
>> > Chuck

Reply to this Message