Results 1 to 2 of 2
2005-04-15, 13:51 #1
- Join Date
- Apr 2005
- Thanked 0 Times in 0 Posts
Reports: Comparing Data (Access 2003)
I have a monthly report that is generated from a query. The report shows all permits issued within a certain period (specified by the user with a form;enter beginnning date, enter end date, click ok). The report works fine. I have totals in the report footer that all work.
The problem is, that I need to show other data as well, to compare to the data being collected in the report. In other words, I'm querying the data from the table for the activity within a period of a month and displaying the totals. However, I also need to show the activity to date for the entire year, as well as the activity from the same period last year, and the year to date from last year. For example, for March, I need the March totals, March Year to Date totals, March 2004 totals, and March 2004 Year to Date totals.
I've attached a copy of the report as it looks when done in Excel.
All of the data required is stored in the same table.
Is there a way to generate this report from my table? Does anyone know of samples that I could download that might use similar reports?(I've searched the net but have had no luck.)
Any help would be greatly appreciated.
Thanks in advance.
2005-04-15, 14:09 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Reports: Comparing Data (Access 2003)
Say that the text boxes to specify the date range are txtStartDate and txtEndDate, on a form frmSelect. The query you have now probably has something like this in the criteria line for the date field:
Between [Forms]![frmSelect]![txtStartDate] And [Forms]![frmSelect]![txtEndDate]
Make three copies of the query.
In the first copy, change the criteria to
Between DateSerial(Year([Forms]![frmSelect]![txtEndDate]),1,1) And [Forms]![frmSelect]![txtEndDate]
This provides the year-to-date figures.
In the second copy, change the criteria to
Between DateAdd("yyyy",-1,[Forms]![frmSelect]![txtStartDate]) And DateAdd("yyyy",-1,[Forms]![frmSelect]![txtEndDate])
This provides the figures for the same period last year.
In the third copy, change the criteria to
Between DateSerial(Year([Forms]![frmSelect]![txtEndDate])-1,1,1) And DateAdd("yyyy",-1,[Forms]![frmSelect]![txtEndDate])
This provides the year-to-date figures for last year.
Now create a new query and add the four queries. Join them on the appropriate field (Type?). Add the fields you need and give them descriptive names, Use this query as record source for your report.