Calculate difference of 2 dates in same field but different record

[Guest]

How would I calculate the difference between two dates in the same field
(SubmittedDate) but on different records? For example, Client A submits
accounts for collections on 12/1/05 (record ID 1). Client A also submits
accounts for collections on 1/15/06 (records ID 2). How can I calculate the
number of days between these 2 dates. I know how to do this in Excel, but
can't figure it out in Access.

Thanks!

 

[Tom Ellison]

Dear Tamara:

When you do this in Excel, there are several things you must know:

1. When you are looking at the "previous row" is that row for the same
Client? If not, the difference would be meaningless, right?

2. The rows must be sorted in exactly the same way, presumably by Client,
then by Date. If not sorted by Date then the previous row might not be the
previous submission (let alone for the same client).

I believe you will agree these conditions are necessary. In Access, the
same conditions are also paramount, but they are specified in a different
way. The rows of data are no longer in any specific order except as you
specify, and this is done explicitly in the query:

SELECT Client, SubmittedDate,
(SELECT MAX(SubmittedDate)
FROM YourTable T1
WHERE T1.Client = T.Client
AND T1.SubmittedDate < T.SubmittedDate)
AS PreviousDate
FROM YourTable T
ORDER BY Client, SubmittedDate

By Client, I refer to something unique that uniquely identifies clients.
Replace "YourTable" with the actual name of, well, your table.

The above is a "correlated subquery" and uses "aliasing" for the table name.
You may look up these terms to better study what is being done.

There is a problem if there are two submissions on the same SubmittedDate.
Both will reference the same previously dated submission as the previous
submission. There may be some "tie breaker" you would want to use to put
them into a unique order.

Tom Ellison