help with query design

[Guest]

I have to create a query that will have three fields; MonthYear, MonthSales,
Previous12MonthSaleTotal. Any ideas how I can generate the
Previous12MonthSaleTotal sum amount? For instance:

The MonthYear field values would be like "JAN 06", the MonthSales is the
total sales for that month. Easy enough. However, The
Previous12MonthSaleTotal needs to be a sum of the previous 12 months sales
total not including the current months total. Any suggestions on how to
accomplisth this?

Thanks in advance!

 

[Duane Hookom]

Do you have any tables and fields you care to share with us?

 

[Guest]

sorry.

SELECT Format([invdte],"mmm yy") AS MonthYear, Sum(ReportData.extprice) AS
TotalSales, "Dont Know How" AS Previous12MonthSaleTotal
FROM ReportData
GROUP BY Format([invdte],"mmm yy")

I was thinking of creating a function that creates the same recordset as
above query. I would then loop through recordset and sum up the previous 12
months total. So the new query would look like:

SELECT Format([invdte],"mmm yy") AS MonthYear, Sum(ReportData.extprice) AS
TotalSales, fCalcLast12MonthSales([invdte]) AS Previous12MonthSaleTotal
FROM ReportData
GROUP BY Format([invdte],"mmm yy")

Any sugestions? Thanks.

 

[Duane Hookom]

Your query isn't limited to any single month. Would you expect to see a
Previous12MonthSaleTotal for each month. For instance for the record with
MonthYear of "Apr 05" would you expect to see the total extprice for May 04
through Apr 05?

--
Duane Hookom
MS Access MVP
--

sorry.

SELECT Format([invdte],"mmm yy") AS MonthYear, Sum(ReportData.extprice) AS
TotalSales, "Dont Know How" AS Previous12MonthSaleTotal
FROM ReportData
GROUP BY Format([invdte],"mmm yy")

I was thinking of creating a function that creates the same recordset as
above query. I would then loop through recordset and sum up the previous
12
months total. So the new query would look like:

SELECT Format([invdte],"mmm yy") AS MonthYear, Sum(ReportData.extprice) AS
TotalSales, fCalcLast12MonthSales([invdte]) AS Previous12MonthSaleTotal
FROM ReportData
GROUP BY Format([invdte],"mmm yy")

Any sugestions? Thanks.

Do you have any tables and fields you care to share with us?