Rolling Averages in Access Charts - Same week for different years

[chantil]

I wonder if someone can help me? I have data for a number of diseases which I have turned into bar charts which show the number of cases per week for the past year. This is using Access 2003.

I'd like to add a rolling average to this graph which shows the average of the number of cases for the SAME week for the past 3 years. (so week 1 would have a bar for cases in 2011 and a point for the average of week 1 totals between 2008-2010).

I have been unable to locate anything which would help me do this, or indeed even if this is possible.

I'm completely new to Access graphs (prefering Excel), so any help you could give me would be great. In the end I hope to be able to put all the graphs in an automatic report which could be run weekly.

Thanks

 

[dhookom]

You could try build a query with the values you want to chart and use the query as the Row Source of the chart.

Duane
Hook'D on Access
MS Access MVP

 

[chantil]

Thanks for this.

Can you use multiple queries as sources of data for the graphs, or would you then have to have another query that pulls in tboth he raw data per week, and the rolling average for that week from the other query?

If so, would it be possible for you to provide me with an idea of how to put that query together? So far all our attempts to do this have failed.

Thanks so much for your help.

 

[dhookom]

I think it would be easiest to divide the task into multiple queries that build to the final result. I would first create a query that totals the number of cases per week and year for the past four years. Then use this to create a query for the current year only. Create a query that averages the weekly numbers for the remaining three years. Combine these two queries into a final query that joins the Week fields.

Duane
Hook'D on Access
MS Access MVP