Help Solving Problem

[Guest]

I have a table that holds a customer's history, Example:

Customer Account Job_Type Completed_Job
7801-123456-01 8 6/1/06
7801-123456-01 7 5/15/06
7801-123456-01 3 4/1/06
7801-123456-01 7 3/29/06
7801-123456-01 7 3/25/06

What I need to find is that If the Job Type = 7 then Subract this Job's
Completed Date minus the previous Job Completed Date. For example, on line 2
I have a Job type 7 completed on 5/15/06 minus the previous job on 4/1/06
equals 44 days since the last visit.

Thanks in advanced!

 

[Marshall Barton]

I have a table that holds a customer's history, Example:

Customer Account Job_Type Completed_Job
7801-123456-01 8 6/1/06
7801-123456-01 7 5/15/06
7801-123456-01 3 4/1/06
7801-123456-01 7 3/29/06
7801-123456-01 7 3/25/06

What I need to find is that If the Job Type = 7 then Subract this Job's
Completed Date minus the previous Job Completed Date. For example, on line 2
I have a Job type 7 completed on 5/15/06 minus the previous job on 4/1/06
equals 44 days since the last visit.

SELECT T.[Customer Account],
T.Job_Type,
T.Completed_Job,
DateDiff("d", (SELECT Max(X.Completed_Job)
FROM table As X
WHERE X.Completed_Job < T.Completed_Job
AND X.[Customer Account] = T.[Customer Account]),
T.Completed_Job) As DaysSince
FROM table As T

 

[Guest]

Marshall,

This query takes too long to run and uses all my pc resources to get the
result. Is there a more "economical" way to get the information without
"disabling" my PC?

I have a table that holds a customer's history, Example:

Customer Account Job_Type Completed_Job
7801-123456-01 8 6/1/06
7801-123456-01 7 5/15/06
7801-123456-01 3 4/1/06
7801-123456-01 7 3/29/06
7801-123456-01 7 3/25/06

What I need to find is that If the Job Type = 7 then Subract this Job's
Completed Date minus the previous Job Completed Date. For example, on line 2
I have a Job type 7 completed on 5/15/06 minus the previous job on 4/1/06
equals 44 days since the last visit.

SELECT T.[Customer Account],
T.Job_Type,
T.Completed_Job,
DateDiff("d", (SELECT Max(X.Completed_Job)
FROM table As X
WHERE X.Completed_Job < T.Completed_Job
AND X.[Customer Account] = T.[Customer Account]),
T.Completed_Job) As DaysSince
FROM table As T

 

[Marshall Barton]

Maybe someone else has a good idea, but all I can think of
is to make sure the fields in the nested WHERE clause are
indexed.
--
Marsh
MVP [MS Access]

This query takes too long to run and uses all my pc resources to get the
result. Is there a more "economical" way to get the information without
"disabling" my PC?

I have a table that holds a customer's history, Example:

Customer Account Job_Type Completed_Job
7801-123456-01 8 6/1/06
7801-123456-01 7 5/15/06
7801-123456-01 3 4/1/06
7801-123456-01 7 3/29/06
7801-123456-01 7 3/25/06

What I need to find is that If the Job Type = 7 then Subract this Job's
Completed Date minus the previous Job Completed Date. For example, on line 2
I have a Job type 7 completed on 5/15/06 minus the previous job on 4/1/06
equals 44 days since the last visit.

SELECT T.[Customer Account],
T.Job_Type,
T.Completed_Job,
DateDiff("d", (SELECT Max(X.Completed_Job)
FROM table As X
WHERE X.Completed_Job < T.Completed_Job
AND X.[Customer Account] = T.[Customer Account]),
T.Completed_Job) As DaysSince
FROM table As T