It depends what columns you want returned. If you only want the person and
the latest date you can group by the person and return the
MAX(AttendanceDate), restricting the query on the date range. Note that this
will not work if you enter the criteria in the MAX(AttendanceDate) column in
query design view as this creates a HAVING clause not a WHERE clause. A
HAVING clause operates on the data after the grouping, whereas you want the
criteria to operate on all rows grouping, for which a WHERE clause is
necessary. In design view you have to add the AttendanceDate column twice
to the design grid and select 'Where' in the 'Total' row of the second
instance. The query would thus be along these lines:
SELECT Attendees.AttendeeID, FirstName, LastName,
MAX(AttendanceDate) AS LatestDate
FROM Attendances INNER JOIN Attendees
ON Attendees.AttendeeID = Attendances.AttendeeID
WHERE AttendanceDate BETWEEN #01/01/2007# AND DATE()
GROUP BY Attendees.AttendeeID, FirstName.LastName;
The above works because the values of AttendeeID, FirstName.LastName will
always be the same for each person. If however you wanted to include
column(s) which may differ in different rows for the same person
(ReasonForAttendance in the example below) you'll need to use a subquery to
establish the latest date in the range, e.g.
SELECT Attendees.AttendeeID, FirstName, LastName,
AttendanceDate, ReasonForAttendance
FROM Attendances AS A1 INNER JOIN Attendees
ON Attendees.AttendeeID = A1.AttendeeID
WHERE AttendanceDate =
(SELECT MAX(AttendanceDate)
FROM Attendances AS A2
WHERE A2.AttendeeID = A1.AttendeeID
AND AttendanceDate BETWEEN #01/01/2007# AND DATE());
Note how the aliases A1 and A2 are used to distinguish the two instances of
the Attendances table, allowing the subquery to be correlated with the outer
query so that it returns the latest data within the range for each attendee.
The outer query is then restricted to the rows for each attendee where the
AttendanceDate matches the value returned by the subquery for the attendee in
question.
BTW assuming not attendances are recorded after the current date the
criterion could more simply be >= #01/01/2007#.
Ken Sheridan
Stafford, England
