Date Calculation between Min & Max

[Robbie Doo]

If there is a Date field in a query is it possible to calculate the #of Days
between the latest visit date and the one before that for the same client?

Client Attendance #of Days
#1 10/5/2008
#2 10/7/2008
#1 11/3/2008 28
#2 11/4/2008 27

 

[John W. Vinson]

If there is a Date field in a query is it possible to calculate the #of Days
between the latest visit date and the one before that for the same client?

Client Attendance #of Days
#1 10/5/2008
#2 10/7/2008
#1 11/3/2008 28
#2 11/4/2008 27

You'll need a self join, a subquery or a DLookUp to find the previous record:

SELECT A.Client, A.Attendance, B.Attendance, DateDiff("d", A.Attendance,
B.Attendance)
FROM tablename AS A
INNER JOIN tablename AS B
ON A.Client = B.Client
WHERE B.Attendance = (SELECT Min([Attendance]) FROM tablename AS C
WHERE C.Attendance > A.Attendance);