Comparing records by year in a report

I am trying to create a report that will compare the length of time a task
takes to be completed to the length of time it took to be completed the year
before. I have been able to create a query that will generate the data for
current records and then another query that prompts for a specific time range
for the criteria. I have not been able to create another query that can take
that date range from the 2nd and pull the records from the 1st from the year
before the specified date range of the 2nd. I know there is a way I can do
this, but I'm stuck on how.

SQL Statement for Query1
SELECT [Insurer Contacts].[Store #], [Insurer Contacts].[Claim #], [Claim
Dates].[Measure Date], [Measure Date]-[Start Date] AS Expr10, [Claim
Dates].[ITEL Rec], [ITEL Rec]-[Measure Date] AS Expr2, [Claim
Dates].[Estimate Downloaded], [Estimate Downloaded]-[Measure Date] AS Expr3,
[Claim Dates].[Order Date], [Claim Dates].[Estimate Approval], [Order
Date]-[Estimate Approval] AS Expr4, [Claim Dates].[Install Date], [Install
Date]-[Order Date] AS Expr5, [Claim Dates].[ATP Sent], [ATP Sent]-[Install
Date] AS Expr6, [Claim Dates].[Job Costed], [Job Costed]-[Install Date] AS
Expr7, [Claim Dates].[Pymnt Applied], [Pymnt Applied]-[Job Costed] AS Expr9,
[Job Costed]-[Start Date] AS Expr8, [Insurance Status].[Insurance $],
[Insurance Status].[Written Sales], [Sales $ Query].Expr1, [Claim
Dates].[Start Date]
FROM (([Insurer Contacts] INNER JOIN [Sales $ Query] ON [Insurer
Contacts].[Claim #] = [Sales $ Query].[Claim #]) LEFT JOIN [Insurance Status]
ON [Insurer Contacts].[Claim #] = [Insurance Status].[Claim #]) LEFT JOIN
[Claim Dates] ON [Insurer Contacts].[Claim #] = [Claim Dates].[Claim #];

SQL Statement for Query2
SELECT [Insurer Contacts].[Store #], [Insurer Contacts].[Claim #], [Claim
Dates].[Measure Date], [Claim Dates]![Measure Date]-[Claim Dates]![Start
Date] AS Expr10, [Claim Dates].[ITEL Rec], [Claim Dates]![ITEL Rec]-[Claim
Dates]![Measure Date] AS Expr2, [Claim Dates].[Estimate Downloaded], [Claim
Dates]![Estimate Downloaded]-[Claim Dates]![Measure Date] AS Expr3, [Claim
Dates].[Order Date], [Claim Dates].[Estimate Approval], [Claim Dates]![Order
Date]-[Claim Dates]![Estimate Approval] AS Expr4, [Claim Dates].[Install
Date], [Claim Dates]![Install Date]-[Claim Dates]![Order Date] AS Expr5,
[Claim Dates].[ATP Sent], [Claim Dates]![ATP Sent]-[Claim Dates]![Install
Date] AS Expr6, [Claim Dates].[Job Costed], [Claim Dates]![Job Costed]-[Claim
Dates]![Install Date] AS Expr7, [Claim Dates].[Pymnt Applied], [Claim
Dates]![Pymnt Applied]-[Claim Dates]![Job Costed] AS Expr9, [Claim
Dates]![Job Costed]-[Claim Dates]![Start Date] AS Expr8, [Insurance
Status].[Insurance $], [Insurance Status].[Written Sales], [Sales $
Query].Expr1, [Claim Dates].[Start Date]
FROM (([Insurer Contacts] INNER JOIN [Sales $ Query] ON [Insurer
Contacts].[Claim #] = [Sales $ Query].[Claim #]) LEFT JOIN [Insurance Status]
ON [Insurer Contacts].[Claim #] = [Insurance Status].[Claim #]) LEFT JOIN
[Claim Dates] ON [Insurer Contacts].[Claim #] = [Claim Dates].[Claim #]
WHERE ((([Claim Dates].[Start Date]) Between [Type the beginning date:] And
[Type the ending date:]));

How about a different approach? Consider a crosstab report. You would have
current year and last year at the top of two columns and your list of tasks
down the left side. At the intersection of row and column would be the
length of time the task took to be completed. If desired you could have a
third column showing the difference.

A crosstab report is based on a crosstab query. When you go to create a new
query you get a choice of query type. Choose crosstab and follow the
directions.

Steve



"Tiegris78" wrote in message
...
I am trying to create a report that will compare the length of time a task
takes to be completed to the length of time it took to be completed the
year
before. I have been able to create a query that will generate the data for
current records and then another query that prompts for a specific time
range
for the criteria. I have not been able to create another query that can
take
that date range from the 2nd and pull the records from the 1st from the
year
before the specified date range of the 2nd. I know there is a way I can do
this, but I'm stuck on how.

SQL Statement for Query1
SELECT [Insurer Contacts].[Store #], [Insurer Contacts].[Claim #], [Claim
Dates].[Measure Date], [Measure Date]-[Start Date] AS Expr10, [Claim
Dates].[ITEL Rec], [ITEL Rec]-[Measure Date] AS Expr2, [Claim
Dates].[Estimate Downloaded], [Estimate Downloaded]-[Measure Date] AS
Expr3,
[Claim Dates].[Order Date], [Claim Dates].[Estimate Approval], [Order
Date]-[Estimate Approval] AS Expr4, [Claim Dates].[Install Date], [Install
Date]-[Order Date] AS Expr5, [Claim Dates].[ATP Sent], [ATP Sent]-[Install
Date] AS Expr6, [Claim Dates].[Job Costed], [Job Costed]-[Install Date] AS
Expr7, [Claim Dates].[Pymnt Applied], [Pymnt Applied]-[Job Costed] AS
Expr9,
[Job Costed]-[Start Date] AS Expr8, [Insurance Status].[Insurance $],
[Insurance Status].[Written Sales], [Sales $ Query].Expr1, [Claim
Dates].[Start Date]
FROM (([Insurer Contacts] INNER JOIN [Sales $ Query] ON [Insurer
Contacts].[Claim #] = [Sales $ Query].[Claim #]) LEFT JOIN [Insurance
Status]
ON [Insurer Contacts].[Claim #] = [Insurance Status].[Claim #]) LEFT JOIN
[Claim Dates] ON [Insurer Contacts].[Claim #] = [Claim Dates].[Claim #];

SQL Statement for Query2
SELECT [Insurer Contacts].[Store #], [Insurer Contacts].[Claim #], [Claim
Dates].[Measure Date], [Claim Dates]![Measure Date]-[Claim Dates]![Start
Date] AS Expr10, [Claim Dates].[ITEL Rec], [Claim Dates]![ITEL Rec]-[Claim
Dates]![Measure Date] AS Expr2, [Claim Dates].[Estimate Downloaded],
[Claim
Dates]![Estimate Downloaded]-[Claim Dates]![Measure Date] AS Expr3, [Claim
Dates].[Order Date], [Claim Dates].[Estimate Approval], [Claim
Dates]![Order
Date]-[Claim Dates]![Estimate Approval] AS Expr4, [Claim Dates].[Install
Date], [Claim Dates]![Install Date]-[Claim Dates]![Order Date] AS Expr5,
[Claim Dates].[ATP Sent], [Claim Dates]![ATP Sent]-[Claim Dates]![Install
Date] AS Expr6, [Claim Dates].[Job Costed], [Claim Dates]![Job
Costed]-[Claim
Dates]![Install Date] AS Expr7, [Claim Dates].[Pymnt Applied], [Claim
Dates]![Pymnt Applied]-[Claim Dates]![Job Costed] AS Expr9, [Claim
Dates]![Job Costed]-[Claim Dates]![Start Date] AS Expr8, [Insurance
Status].[Insurance $], [Insurance Status].[Written Sales], [Sales $
Query].Expr1, [Claim Dates].[Start Date]
FROM (([Insurer Contacts] INNER JOIN [Sales $ Query] ON [Insurer
Contacts].[Claim #] = [Sales $ Query].[Claim #]) LEFT JOIN [Insurance
Status]
ON [Insurer Contacts].[Claim #] = [Insurance Status].[Claim #]) LEFT JOIN
[Claim Dates] ON [Insurer Contacts].[Claim #] = [Claim Dates].[Claim #]
WHERE ((([Claim Dates].[Start Date]) Between [Type the beginning date:]
And
[Type the ending date:]));

Don't I all ready of to have the data seperated by year in this crosstab?
That's the problem I'm having...when I set a criteria for previous year and
will not show any records. I guess I'm not exactly sure what you're trying to
tell me...

"Steve" wrote:

How about a different approach? Consider a crosstab report. You would have
current year and last year at the top of two columns and your list of tasks
down the left side. At the intersection of row and column would be the
length of time the task took to be completed. If desired you could have a
third column showing the difference.

A crosstab report is based on a crosstab query. When you go to create a new
query you get a choice of query type. Choose crosstab and follow the
directions.

Steve



"Tiegris78" wrote in message
...
I am trying to create a report that will compare the length of time a task
takes to be completed to the length of time it took to be completed the
year
before. I have been able to create a query that will generate the data for
current records and then another query that prompts for a specific time
range
for the criteria. I have not been able to create another query that can
take
that date range from the 2nd and pull the records from the 1st from the
year
before the specified date range of the 2nd. I know there is a way I can do
this, but I'm stuck on how.

SQL Statement for Query1
SELECT [Insurer Contacts].[Store #], [Insurer Contacts].[Claim #], [Claim
Dates].[Measure Date], [Measure Date]-[Start Date] AS Expr10, [Claim
Dates].[ITEL Rec], [ITEL Rec]-[Measure Date] AS Expr2, [Claim
Dates].[Estimate Downloaded], [Estimate Downloaded]-[Measure Date] AS
Expr3,
[Claim Dates].[Order Date], [Claim Dates].[Estimate Approval], [Order
Date]-[Estimate Approval] AS Expr4, [Claim Dates].[Install Date], [Install
Date]-[Order Date] AS Expr5, [Claim Dates].[ATP Sent], [ATP Sent]-[Install
Date] AS Expr6, [Claim Dates].[Job Costed], [Job Costed]-[Install Date] AS
Expr7, [Claim Dates].[Pymnt Applied], [Pymnt Applied]-[Job Costed] AS
Expr9,
[Job Costed]-[Start Date] AS Expr8, [Insurance Status].[Insurance $],
[Insurance Status].[Written Sales], [Sales $ Query].Expr1, [Claim
Dates].[Start Date]
FROM (([Insurer Contacts] INNER JOIN [Sales $ Query] ON [Insurer
Contacts].[Claim #] = [Sales $ Query].[Claim #]) LEFT JOIN [Insurance
Status]
ON [Insurer Contacts].[Claim #] = [Insurance Status].[Claim #]) LEFT JOIN
[Claim Dates] ON [Insurer Contacts].[Claim #] = [Claim Dates].[Claim #];

SQL Statement for Query2
SELECT [Insurer Contacts].[Store #], [Insurer Contacts].[Claim #], [Claim
Dates].[Measure Date], [Claim Dates]![Measure Date]-[Claim Dates]![Start
Date] AS Expr10, [Claim Dates].[ITEL Rec], [Claim Dates]![ITEL Rec]-[Claim
Dates]![Measure Date] AS Expr2, [Claim Dates].[Estimate Downloaded],
[Claim
Dates]![Estimate Downloaded]-[Claim Dates]![Measure Date] AS Expr3, [Claim
Dates].[Order Date], [Claim Dates].[Estimate Approval], [Claim
Dates]![Order
Date]-[Claim Dates]![Estimate Approval] AS Expr4, [Claim Dates].[Install
Date], [Claim Dates]![Install Date]-[Claim Dates]![Order Date] AS Expr5,
[Claim Dates].[ATP Sent], [Claim Dates]![ATP Sent]-[Claim Dates]![Install
Date] AS Expr6, [Claim Dates].[Job Costed], [Claim Dates]![Job
Costed]-[Claim
Dates]![Install Date] AS Expr7, [Claim Dates].[Pymnt Applied], [Claim
Dates]![Pymnt Applied]-[Claim Dates]![Job Costed] AS Expr9, [Claim
Dates]![Job Costed]-[Claim Dates]![Start Date] AS Expr8, [Insurance
Status].[Insurance $], [Insurance Status].[Written Sales], [Sales $
Query].Expr1, [Claim Dates].[Start Date]
FROM (([Insurer Contacts] INNER JOIN [Sales $ Query] ON [Insurer
Contacts].[Claim #] = [Sales $ Query].[Claim #]) LEFT JOIN [Insurance
Status]
ON [Insurer Contacts].[Claim #] = [Insurance Status].[Claim #]) LEFT JOIN
[Claim Dates] ON [Insurer Contacts].[Claim #] = [Claim Dates].[Claim #]
WHERE ((([Claim Dates].[Start Date]) Between [Type the beginning date:]
And
[Type the ending date:]));

I think Steve has in mind something like this sample:

http://www.accessmvp.com/TWickerath/...tabExample.zip



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Tiegris78" wrote:

Don't I all ready of to have the data seperated by year in this crosstab?
That's the problem I'm having...when I set a criteria for previous year and
will not show any records. I guess I'm not exactly sure what you're trying to
tell me...

Your table should look like:
TblTask
TaskID
Task Description
Other descriptive fields about a tsk

TblTaskCompletionTime
TaskCompletionTimeID
TaskID
DatePerformed
CompletionTime

If you're trying to make the comparison between last year and this year, you
need a record in TblTaskCompletionTime for last year and one for this year.
Your crosstab query will include both tables. Rows will be defined as
TaskDescription and Columns as DatePerformed. The Value will be set as
CompletionTime.

Steve



"Tiegris78" wrote in message
...
Don't I all ready of to have the data seperated by year in this crosstab?
That's the problem I'm having...when I set a criteria for previous year
and
will not show any records. I guess I'm not exactly sure what you're trying
to
tell me...

"Steve" wrote:

How about a different approach? Consider a crosstab report. You would
have
current year and last year at the top of two columns and your list of
tasks
down the left side. At the intersection of row and column would be the
length of time the task took to be completed. If desired you could have a
third column showing the difference.

A crosstab report is based on a crosstab query. When you go to create a
new
query you get a choice of query type. Choose crosstab and follow the
directions.

Steve



"Tiegris78" wrote in message
...
I am trying to create a report that will compare the length of time a
task
takes to be completed to the length of time it took to be completed the
year
before. I have been able to create a query that will generate the data
for
current records and then another query that prompts for a specific time
range
for the criteria. I have not been able to create another query that can
take
that date range from the 2nd and pull the records from the 1st from the
year
before the specified date range of the 2nd. I know there is a way I can
do
this, but I'm stuck on how.

SQL Statement for Query1
SELECT [Insurer Contacts].[Store #], [Insurer Contacts].[Claim #],
[Claim
Dates].[Measure Date], [Measure Date]-[Start Date] AS Expr10, [Claim
Dates].[ITEL Rec], [ITEL Rec]-[Measure Date] AS Expr2, [Claim
Dates].[Estimate Downloaded], [Estimate Downloaded]-[Measure Date] AS
Expr3,
[Claim Dates].[Order Date], [Claim Dates].[Estimate Approval], [Order
Date]-[Estimate Approval] AS Expr4, [Claim Dates].[Install Date],
[Install
Date]-[Order Date] AS Expr5, [Claim Dates].[ATP Sent], [ATP
Sent]-[Install
Date] AS Expr6, [Claim Dates].[Job Costed], [Job Costed]-[Install Date]
AS
Expr7, [Claim Dates].[Pymnt Applied], [Pymnt Applied]-[Job Costed] AS
Expr9,
[Job Costed]-[Start Date] AS Expr8, [Insurance Status].[Insurance $],
[Insurance Status].[Written Sales], [Sales $ Query].Expr1, [Claim
Dates].[Start Date]
FROM (([Insurer Contacts] INNER JOIN [Sales $ Query] ON [Insurer
Contacts].[Claim #] = [Sales $ Query].[Claim #]) LEFT JOIN [Insurance
Status]
ON [Insurer Contacts].[Claim #] = [Insurance Status].[Claim #]) LEFT
JOIN
[Claim Dates] ON [Insurer Contacts].[Claim #] = [Claim Dates].[Claim
#];

SQL Statement for Query2
SELECT [Insurer Contacts].[Store #], [Insurer Contacts].[Claim #],
[Claim
Dates].[Measure Date], [Claim Dates]![Measure Date]-[Claim
Dates]![Start
Date] AS Expr10, [Claim Dates].[ITEL Rec], [Claim Dates]![ITEL
Rec]-[Claim
Dates]![Measure Date] AS Expr2, [Claim Dates].[Estimate Downloaded],
[Claim
Dates]![Estimate Downloaded]-[Claim Dates]![Measure Date] AS Expr3,
[Claim
Dates].[Order Date], [Claim Dates].[Estimate Approval], [Claim
Dates]![Order
Date]-[Claim Dates]![Estimate Approval] AS Expr4, [Claim
Dates].[Install
Date], [Claim Dates]![Install Date]-[Claim Dates]![Order Date] AS
Expr5,
[Claim Dates].[ATP Sent], [Claim Dates]![ATP Sent]-[Claim
Dates]![Install
Date] AS Expr6, [Claim Dates].[Job Costed], [Claim Dates]![Job
Costed]-[Claim
Dates]![Install Date] AS Expr7, [Claim Dates].[Pymnt Applied], [Claim
Dates]![Pymnt Applied]-[Claim Dates]![Job Costed] AS Expr9, [Claim
Dates]![Job Costed]-[Claim Dates]![Start Date] AS Expr8, [Insurance
Status].[Insurance $], [Insurance Status].[Written Sales], [Sales $
Query].Expr1, [Claim Dates].[Start Date]
FROM (([Insurer Contacts] INNER JOIN [Sales $ Query] ON [Insurer
Contacts].[Claim #] = [Sales $ Query].[Claim #]) LEFT JOIN [Insurance
Status]
ON [Insurer Contacts].[Claim #] = [Insurance Status].[Claim #]) LEFT
JOIN
[Claim Dates] ON [Insurer Contacts].[Claim #] = [Claim Dates].[Claim #]
WHERE ((([Claim Dates].[Start Date]) Between [Type the beginning date:]
And
[Type the ending date:]));