Year to Date Sales for current and last year

[tabtech]

Hello to all,

I have two queries in Access 2002 that provide monthly sales by customer, one for 2004 and one for 2005. Each query contains these fields:

CustomerName, Year, Month, SalesAmnt.

Some customers may not have sales in a given month, so there will be no record for that month.
I want to write a query that will give me this years YTD sales (which is easy because I just have to sum sales for 2005)and last years sales for the same period (this is were i am having trouble because if I join the two queries by month and one customer has no sales in 2/04 but has sales in 2/05, this month will not be in either the YTD total). If I could show a record for every month for each customer, even if there were zero sales, it would work. But, like I said, if there were no sales for a customer in a given month, there will be no record.
Any help would be greatly appreciated.

Mike

 

[lespaul]

you need to left join into your customer table so that even customer wil no sales will show up.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[tabtech]

Thanks Lespaul,

My the problem is not that i need all customers to appear even if they have no sales. Please read my post carefully. Customers that had no sales at all last year or this year I don't care about. What I am looking for is a way to calculate year to date sales for 2004 based on todays date.

Mike

 

[dhookom]

I'm not sure why you would use your two queries. If I want ytd from last year for the Orders/Orders Details in NorthWind, I would use:
[tt][blue]
SELECT Orders.CustomerID,
Sum((Abs([OrderDate] Between DateSerial(Year(Date())-1,1,1) And DateAdd("yyyy",-1,Date()))*[UnitPrice]*[Quantity])) AS LastYTD
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID;
[/blue][/tt]
You can add another column that provides the current year to date.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]