Rolling averages chart

[irethedo]

I have a database that keeps track of how many parts of a certain color are produced an a particular date.

I would like to display a month by month bar chart in a report that displays a 12 month running total of a given part based on the date that I input into a query.

The table simply has three fields, The date, the color, and the quantity produced on that date.

I put together the following query for the Row Source of my bar chart, but it only displays each item in the table in the chart and does not segregrate them into quantities for each given month:

---------
SELECT .color, Sum(.qty) AS SumOfqty, .date
FROM
GROUP BY .color, .date, (Year([date])*12+Month([date])-1)
ORDER BY (Year([date])*12+Month([date])-1);

---------
How would I structure a query to show the quantities for each month (from one year before a given date) and create a bar chart to display this?

Thanks

 

[Crowley16]

you need to first get the data you want to work with, then sort them out as you want...

something like:

select month, colour, sum(qty)
from table
where date between stDate and stDate + 365
group by month, colour

--------------------
Procrastinate Now!

 

[irethedo]

Thank you Crowley-

Where do I get the value for month?

 

[irethedo]

irethedo (TechnicalUser) 2 Feb 07 11:01
Thank you Crowley-

Where do I get the value for month?

 

[irethedo]

irethedo (TechnicalUser) 27 Feb 07 11:01

Thank you Crowley-

Where do I get the value for month?

 

[mbarkley9872]

This is close to what you're looking for

SELECT DateSerial(Year(![date]),Month(![date]),1) AS MonthDate, .color, Sum(Nz(!qty)) AS SumOfQty,
FROM
GROUP BY DateSerial(Year(![date]),Month(![date]),1), .color
ORDER BY DateSerial(Year(![date]),Month(![date]),1);

You should also consider renaming your date field.

Mike