Fill zero sales months with 0

perlfan

Registered User.
Local time
Today, 10:55
Joined
May 26, 2009
Messages
192
Hi there,
I am pulling sales results per customer and month. For presenting the numbers in a diagram I would like to show 0 sales for months in which no sales were generated. At the moment the query results only the months in which there are sales > 0. Is there a trick that I don't know? Thanks for help!!
FRANK

SELECT Year([InvoiceDate]) & ' ' & Month([InvoiceDate]) AS [Month], totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]) AS Ausdr1
FROM Invoices INNER JOIN customers ON Invoices.CustomerID = customers.CustomerID
WHERE (((Invoices.InvoiceDate) Between #1/1/2013# And #7/1/2013#) AND ((customers.CustomerID)=145))
GROUP BY totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]), Year([InvoiceDate]), Month([InvoiceDate])
ORDER BY Year([InvoiceDate]) DESC , Month([InvoiceDate]) DESC;
 
u can use iif(count[CustomerID]<1,0,count[CustomerID]
 
Sorry, but I don't quite understand! Could you give a hint how to implement this? Thanks a lot - FRANK
 
I tried this query:
SELECT Year([InvoiceDate]) & ' ' & Month([InvoiceDate]) AS [Month], IIf(IsNull([customers]![CustomerID]),0,[customers]![CustomerID]) AS Umsatz
FROM Invoices INNER JOIN customers ON Invoices.CustomerID = customers.CustomerID
WHERE (((Invoices.InvoiceDate) Between #1/1/2013# And #7/31/2013#) AND ((customers.CustomerID)=145))
GROUP BY Year([InvoiceDate]) & ' ' & Month([InvoiceDate]), IIf(IsNull([customers]![CustomerID]),0,[customers]![CustomerID]);

I hoped to get a list with:
January 145
February 145
MArch 0
April 0
May 145
etc.

Instead it still skips the zeros and only shows the non zero/null lines:
January 145
February 145
May 145

Thank you for help! FRANK
 

Users who are viewing this thread

Back
Top Bottom