Comparing records by year in a report

T

Tiegris78

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:]));
 
T

Tiegris78

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 said:
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
(e-mail address removed)


Tiegris78 said:
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:]));
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sort order in report detail section 2
Finding the Date nearest a given date 1
Dates in a form for filtering Report query 4
Matching sales records to updated prices, by date 5
Report by date range 6
Showing a report with records between two dates 18
Problem Joining Tables In A Query 2
How to filter data in a report. 1

Top