Calculating difference between date fields in multiple records

[mcumm02]

Hello,



I am looking for help in making a query to return the number of days that have elapsed from payment transaction to payment transaction in a given account. So, for example, if one account has 10 payment transactions, I would like to know how many days elapsed between payments 1 and 2, 2 and 3, 3 and 4, etc. This calculation would be repeated for all payment transactions in a given account, and repeated for subsequent accounts in the table.

The table has a number of fields, but I believe that the most relevant ones are:

AccountNumber

TransactionDate

TransactionType (please note that there are a number of transaction types in the table, but I only want to consider transactions where this type = "Payment")

Please also note that this is not my table, and it does not currently have a primary key. I could add one, though, if that would be helpful, but could use a hand in figuring out how to do that.

Thank you in advance. Any help is very much appreciated.

Best,

Michael

[pbaldy]

See if this works for you:

http://allenbrowne.com/subquery-01.html#AnotherRecord

[mcumm02]

Thanks! I'll give that a shot!

- Michael

[pbaldy]

No problem, and welcome to the site by the way!