Time Lapse Calculations - how to?

[deborahyr]

I designed an Access query to give me the maximum date of an activity by activity and now would like to get the number of days between activities. How do I do this?

ReqID, Activity Date, Activity Name, Days Lapse
__________________
1001, 02/01/2006, email sent, 0
1001, 03/23/2006, response received, 51
____________

51 days between 2/1 and 3/23.

 

[Golom]

Select S.ReqID, S.[Activity Date], R.[Activity Date],
       DateDiff("d", S.[Activity Date], R.[Activity Date]) As [Days Elapsed]

From myTable S INNER JOIN myTable As R
     ON  S.ReqID = R.ReqID

Where S.[Activity Name] = 'email sent'
  AND R.[Activity Name] = 'response received'

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[deborahyr]

Thanks, Golom - but I'm more confused than ever! Do I literally cut and paste your 'coding' above? Is there an expression I can use? I provided an example- Activity Name will not be a part of my results. Just looking to capture those Reqs that have greater than/equal to 30 day lapse in time between any activity. Can I do this?

 

[Golom]

Select S.ReqID, S.[Activity Date] As [First Date], 
       R.[Activity Date] As [Second Date],
       DateDiff("d", S.[Activity Date], R.[Activity Date]) As [Days Elapsed]

From myTable S INNER JOIN myTable As R
     ON  S.ReqID = R.ReqID

Where R.[Activity Date] = 
      (Select MIN([Activity Date]) From myTable X
       Where X.[Activity Date] > S.[Activity Date]
         AND X.ReqID           = S.ReqID)

  AND DateDiff("d", S.[Activity Date], R.[Activity Date]) >= 30

Just paste into SQL view in a query and change "myTable" to the name of your real table. Change field names too if they are not what you have in your table.


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]