Zero value for queries that bring back no results

[Guest]

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

 

[Allen Browne]

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 ...

 

[Guest]

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,
--
Chuck W

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 ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
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

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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,
--
Chuck W

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 ...

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