Year Comparison Report

[Tiegris78]

I am trying to get my report to automically compare the data from a specified
date range to data from the previous year in the same specified date range.
I currently have 2 queries created. One is for the current year w/a specified
date range and the other I am trying to set up to show the previous years
totals. How can I get this to work?

 

[KARL DEWEY]

It would help if you posted the SQL of your queries.
In what way do you want to compare the data? Side by side, percent
increase/decrease, etc.

Probable all you need is a single query like this --
SELECT SomeField, NextField, Format([YourDateField], "yyyy") AS
Report_Year, Sum([SomethingElse]) AS Year_Total
FROM YourTable
WHERE [YourDateField] Between CVDate([Enter start date of this year]) AND
CVDate([Enter end date of this year]) OR [YourDateField] Between
DateAdd("yyyy", -1, CVDate([Enter start date of this year])) AND
DateAdd("yyyy", -1, CVDate([Enter end date of this year]))
GROUP BY SomeField, NextField, Format([YourDateField], "yyyy");

 

[Tiegris78]

Have under the criteria of the start date:
Between [Type the beginning date:] And [Type the ending date:]
For the current year
In the report I am showing the average time it takes to complete tasks for
the records specified in the above date range. I want to show in a column
next to it the average time it took to complete the same tasks the year
before.

It would help if you posted the SQL of your queries.
In what way do you want to compare the data? Side by side, percent
increase/decrease, etc.

Probable all you need is a single query like this --
SELECT SomeField, NextField, Format([YourDateField], "yyyy") AS
Report_Year, Sum([SomethingElse]) AS Year_Total
FROM YourTable
WHERE [YourDateField] Between CVDate([Enter start date of this year]) AND
CVDate([Enter end date of this year]) OR [YourDateField] Between
DateAdd("yyyy", -1, CVDate([Enter start date of this year])) AND
DateAdd("yyyy", -1, CVDate([Enter end date of this year]))
GROUP BY SomeField, NextField, Format([YourDateField], "yyyy");

--
Build a little, test a little.

I am trying to get my report to automically compare the data from a specified
date range to data from the previous year in the same specified date range.
I currently have 2 queries created. One is for the current year w/a specified
date range and the other I am trying to set up to show the previous years
totals. How can I get this to work?

 

[KARL DEWEY]

Post the SQL of your query by opening in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
Build a little, test a little.

Have under the criteria of the start date:
Between [Type the beginning date:] And [Type the ending date:]
For the current year
In the report I am showing the average time it takes to complete tasks for
the records specified in the above date range. I want to show in a column
next to it the average time it took to complete the same tasks the year
before.

It would help if you posted the SQL of your queries.
In what way do you want to compare the data? Side by side, percent
increase/decrease, etc.

Probable all you need is a single query like this --
SELECT SomeField, NextField, Format([YourDateField], "yyyy") AS
Report_Year, Sum([SomethingElse]) AS Year_Total
FROM YourTable
WHERE [YourDateField] Between CVDate([Enter start date of this year]) AND
CVDate([Enter end date of this year]) OR [YourDateField] Between
DateAdd("yyyy", -1, CVDate([Enter start date of this year])) AND
DateAdd("yyyy", -1, CVDate([Enter end date of this year]))
GROUP BY SomeField, NextField, Format([YourDateField], "yyyy");

--
Build a little, test a little.

I am trying to get my report to automically compare the data from a specified
date range to data from the previous year in the same specified date range.
I currently have 2 queries created. One is for the current year w/a specified
date range and the other I am trying to set up to show the previous years
totals. How can I get this to work?

 

[John W. Vinson]

I am trying to get my report to automically compare the data from a specified
date range to data from the previous year in the same specified date range.
I currently have 2 queries created. One is for the current year w/a specified
date range and the other I am trying to set up to show the previous years
totals. How can I get this to work?

Karl's questions about how you want this presented are apropos, but... one
possibility would be to have a Form with two Subforms (or a Report with two
Subreports), based on queries referencing two textboxes on an unbound form
(frmCrit), let's call them txtStart and txtEnd. The first subform would have
criteria like

= Forms![frmCrit]![txtStart] AND <= Forms![frmCrit]![txtEnd]

and the second

= DateAdd("yyyy", -1, Forms![frmCrit]![txtStart]) AND <= DateAdd("yyyy", -1, Forms![frmCrit]![txtEnd])

The two subforms could be otherwise identical or could have "Selected year"
and "Previous year" labels in the header, or however you would like to have
them distinguished.

 

[Tiegris78]

I tried this, but it said the expression was too complex...

I am trying to get my report to automically compare the data from a specified
date range to data from the previous year in the same specified date range.
I currently have 2 queries created. One is for the current year w/a specified
date range and the other I am trying to set up to show the previous years
totals. How can I get this to work?

Karl's questions about how you want this presented are apropos, but... one
possibility would be to have a Form with two Subforms (or a Report with two
Subreports), based on queries referencing two textboxes on an unbound form
(frmCrit), let's call them txtStart and txtEnd. The first subform would have
criteria like

= Forms![frmCrit]![txtStart] AND <= Forms![frmCrit]![txtEnd]

and the second

= DateAdd("yyyy", -1, Forms![frmCrit]![txtStart]) AND <= DateAdd("yyyy", -1, Forms![frmCrit]![txtEnd])

The two subforms could be otherwise identical or could have "Selected year"
and "Previous year" labels in the header, or however you would like to have
them distinguished.

 

[John W. Vinson]

I tried this, but it said the expression was too complex...

I am trying to get my report to automically compare the data from a specified
date range to data from the previous year in the same specified date range.
I currently have 2 queries created. One is for the current year w/a specified
date range and the other I am trying to set up to show the previous years
totals. How can I get this to work?

Karl's questions about how you want this presented are apropos, but... one
possibility would be to have a Form with two Subforms (or a Report with two
Subreports), based on queries referencing two textboxes on an unbound form
(frmCrit), let's call them txtStart and txtEnd. The first subform would have
criteria like

= Forms![frmCrit]![txtStart] AND <= Forms![frmCrit]![txtEnd]

and the second

= DateAdd("yyyy", -1, Forms![frmCrit]![txtStart]) AND <= DateAdd("yyyy", -1, Forms![frmCrit]![txtEnd])

The two subforms could be otherwise identical or could have "Selected year"
and "Previous year" labels in the header, or however you would like to have
them distinguished.

Try making Forms![frmCrit]![txtStart] and Forms![frmCrit]![txtEnd] explicit
Parameters. Right click the grey background of the tables, and choose
Parameters; copy and paste the actual parameters (with your form name and
control names) into the left column and select Date/Time from the right.