2 Replies Latest reply on May 10, 2011 1:05 AM by RestaurantCharlie

# Calculate time between related records

### Title

Calculate time between related records

### Post

I have a Tests table with 3 fields:

1. TestDate
2. Test (specifies which test taken)
3. Student

A particular student may take multiple different tests, and I need to calculate the time between their most recent test and the one prior.  I've been trying the GetNthRecord function, but I can't figure out 2 things:

1. I can pull the date from the previous record, but I cannot figure out how to write the calculation so that it returns the correct number.  (For instance, if the previous test was on the same date, it should return a zero.  It does not)
2. The previous record is not always for the same student, and I want to calculate the difference in time between records of the same student.

Any help would be appreciated.

Thanks,

Bob

• ###### 1. Re: Calculate time between related records

Just figured out problem #1.  Now the big issue is calculating this only between records of the same student.

• ###### 2. Re: Calculate time between related records

Have you used a self join for this calculation?

Include the student in the self join and use the second table occurence in your calculation.

If this doesn't make any sense, please explain your problem in more detail so as to provide more precise pointers.