Multiple Date ranges in report/query?

[Franky G]

Hi,

I would like to produce a report (actually need a graph, but haven't even looked at that!) that will allow me to compare data from 2 years side by side. I want to total various fields (9 in total) recieved in a 4 week period. Then I want to see cumulative totals over the year, for both years. The data is just counts of various applications, fees recieved etc during a financial period (4 week normally, but there are a few anomilies)

I'm trying to compare 2001 financial year to this years, so far. If I could even get Access to produce the numbers, I would be happy to export to Excel and produce graphs that way.

I could manually enter the date criteria for every financial period and just take a note of the results, plug them into Excel and produce a graph that way, but I just know Access can do it much quicker!

Any suggestions on how I can write a query (wasn't sure whether to post in queries or reports) which will look at multiple date ranges? I was thinking of populating a table with the dates I have already entered into Excel and getting Access to use these in a query..using some kind of loop function??

Thanks for any help,
Franky

 

[RichMorrison]

Franky,

I have done this type of thing. The best method seems to be:
1) make a query to collect and sum your data for the previous year
2) make a query to collect and sum for the current year
3) make a third query that joins queries 1 and 2. I assume you can group by a product or "application" or whatever and join on that.

This assumes that the product or application is present in both the current and previous year. If not, query 3 will not produce output for a specific product.

You wind up with a query that shows year over year. If you want a range within each year (Jan to Mar) then you will have to get the appropriate creteria into queries 1 and 2.

HTH,
RichM

 

[Pat Hartman]

You could use a crosstab query with the year part of the activity date as the column header.