Query for products ordered by month(s) and ytd

**goofy78270** · 10-15-2009, 06:21 PM

I am looking to create a single query for the following:

I would like to capture a list of all products and their descriptions with ytd total, previous month, current month, and projected for next month.

All information is held in the same view as follows:
Product
ProductDescription
DisburseDate

**split_atom18** · 10-16-2009, 11:37 AM

This is how I would do it.

SQL Code:

Code:

SELECT ProductData.Product, ProductData.ProductDescription, Sum(IIf([ProductData]![DisburseDate]<=Date() And Year([ProductData]![DisburseDate])>=Year(Date()),[Qty],0)) AS YTD, Sum(IIf(Month([ProductData]![DisburseDate])=Month(Date()),[Qty],0)) AS [Current Month], Sum(IIf(Month([ProductData]![DisburseDate])=(Month(Date())+1),[Qty],0)) AS [Predicted Next Month]
FROM ProductData
GROUP BY ProductData.Product, ProductData.ProductDescription;

You said you wanted totals, I assumed you had to have a Qty field.

Hope this helps,

Dan