Need date to appear when sum is zero

C

ChuckW

Hi,

I run a report that lists the sales week to date, month
to date and year to date by combining three separate
queries. I am combining the three based on today's date
(a field I have renamed Week_Of). The problem is that
today is November 1st and there are no sales yet. So my
monthly sales query below returns no records. When I
then try to joing these with my weekly and yearly sales
which do have a record, then there are no records total
since it is a one to one join. What I would like to do
is to have my query return a value of 0 for sales in a
situation like today and have today's date listed. Can
anyone help?

Thanks,

Chuck


SELECT Sum(MonthlyRevenue1.Amount) AS SumOfAmount, Date()
AS Week_Of INTO EHPMonthlyRevenue
FROM MonthlyRevenue1
GROUP BY Date();
 
G

Guest

hi,
use the Nz functions (null to zero)
SELECT Sum(Nz(MonthlyRevenue1.Amount)) AS SumOfAmount
look up the Nz function in help for more info.
 
M

Marshall Barton

ChuckW said:
I run a report that lists the sales week to date, month
to date and year to date by combining three separate
queries. I am combining the three based on today's date
(a field I have renamed Week_Of). The problem is that
today is November 1st and there are no sales yet. So my
monthly sales query below returns no records. When I
then try to joing these with my weekly and yearly sales
which do have a record, then there are no records total
since it is a one to one join. What I would like to do
is to have my query return a value of 0 for sales in a
situation like today and have today's date listed.


Use an outer (Left or Right) join in the query instead of
Inner Join.
 
T

Tom Ellison

Dear Chuck:

May I draw your attention to another thread you started in which I
made considerable effort? It was:

Cross Tab analysis for retention

dated 11/8

Thank you,
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top