Calculations between records?

[kmacd6951]

Does Access have the ability to calculate the difference between a field in one record, to a field in another record?

For example, a record contains the date case was created and the date the note was entered on the case. I use the difference between these two date/times to calculate the response time.

What I want to try to do next is look at the time of the note on record #1 and calculate the difference between that and the same field in record #2 - to give me the time between case updates?

Can this be done, or can you only make comparisons within a record(line)?

Thanks

 

[SydneyGeek]

Yep, can be done -- there are several different approaches. One is to work with forms and subforms, because they let you grab summary data from a subform (the individual case responses) and plonk it in a field on the main form. You can then...
1. Ensure that all new records created in the subform have the Case Start Date entered by default or
2. Create an expression in the subform that subtracts Start Date from the Response Date.

Alternatively, you can use code to timestamp the field. With a bit more detail on your setup, we can give more info on the best way to proceed.

Denis

 

[kmacd6951]

Thanks Denis!

What I am doing is analyzing response time to customer requests in a call center. I need to look at the difference between the time a note was entered and the time the next note on the case was entered.
I only want to compare records when the case # is the same (no need to compare reponse time between 2 different cases). I can tell where a new case starts based on column H (0 = new case, 1=new note on same case)

So, here are 3 recods for one case.
Column E tells me when it's a new case ID (=0). If column H is a 0, I need to get the time lapse between column F and column D.
If H=1, I want to get the difference between the F in record 2 and F in record 1, (in this case 5/14/04 5:29PM - 5/14/04 4:26PM)

A B C D E
1188972 Billing van$smgl 5/14/04 4:20 PM mbrace
F G H
5/14/04 4:26 PM 6 0

1188972 Billing van$smgl 5/14/04 4:20 PM mbrace 5/14/04 5:29 PM 69 1

1188972 Billing van$smgl 5/14/04 4:20 PM mbrace 5/17/04 8:18 AM 223 1


I have the code all set for calculating the time lapse. I am only able to do it when the the field are all in the same record however.

Does that help clarify what I am trying to do? Or make it more confusing??

Thanks