Access Calculation - Subtract Index(t) from Index (t-1)

[Demab]

Okay, I have an access database with data in rows containing names and times of arrival for multiple sets (shows). If the set id is the same, I need to subtract the time field from the previous time field (to get interarrival times), and if not, I want to leave it blank. I know this is an easy formula in Excel, but does anyone know how to write it in SQL for Access?

Excel Code: IF(SetID4=SetID5, Time5-Time4, " ")

Thanks!

 

[Andrew Fergus]

Hi, you should be able to do this with a combination of a ranking query and a dlookup function - the ranking query puts the records into the correct sequence and the dlookup function can refer to the previous line in the query. I haven't tested it but these links should help :
Ranking Records in a Query
Using DLookup
MrExcel thread that might help - see the 4th post
HTH, Andrew.