can anyone provide a query for this

[madhur]

Hello
I have a table with this format:

Transaction NO DateBegin

1 1/1/2004 12:20:03
2 1/1/2004 12:20:09
4 1/1/2004 12:20:11
6 1/1/2004 12:20:14
7 1/1/2004 12:20:15

I want to write a query which will arrange all the records in the
ascending order(by transaction number) and give the seconds between the
current transaction and the next transaction.


For example, the above data should return me

Transaction NO DateBegin TimeTaken

1 1/1/2004 12:20:03 0
2 1/1/2004 12:20:09 6
4 1/1/2004 12:20:11 2
6 1/1/2004 12:20:14 3
7 1/1/2004 12:20:15 1

Can anyone provide me a hint to this.
I am working on MS Access 2000.

Thanks in advance

Madhur

 

[Michel Walsh]

Hi,



SELECT a.TransactionNo,
MIN(b.TransactionNo) As NextTransaction,
MIN(b.DateBegin)-MIN(a.DateBegin) As ElapsedTime

FROM myTable As a INNER JOIN myTable As b
ON b.TransactionNo > a.TransactionNo

GROUP BY a.TransactionNo




That is based on the assumption that if transactionNo increase, then the
DateBegin also increase. Indeed, by the join, b.xxx refers to transactionNo
further in time, but MIN just keep the first one (occurring after
a.TransactionNo), for each (GROUP BY) a.TransactionNo.




Hoping it may help,
Vanderghast, Access MVP