I'm having a problem getting some Access SQL to do what I want for reporting. I have a SCANS table that captures timestamps of student scans for attendance purposes. I want to grab, for each user id that has a scan, the lowest date and the highest date (for a given date range, but I'll worry about that later). To start, I'm just trying to get the highest (max) date, but I also need all the other fields each that record, which is where I'm having the problem. First, here's some sample data:

Code:
user_id	timestamp		station	building
1	01/01/1999 12:00:00 am	1	1
1	05/19/2000 11:56:10 pm	2	2
1	05/19/2000 11:57:47 pm	2	2
5	09/27/1999 12:00:00 am	1	1
5	05/19/2000 11:56:10 pm	2	2
5	05/19/2000 11:57:47 pm	2	2
6	05/19/2000 12:00:00 am	1	1
6	05/19/2000 11:56:10 pm	2	2
6	05/19/2000 11:57:47 pm	2	2
7	05/19/2000 11:56:10 pm	2	2
7	05/19/2000 11:57:47 pm	2	2
The user id of the user who was scanned, the timestamp of the scan, and the station and building where it happened (there's more, but I left them out here for example).

Anyway, when I do this query:

SELECT QSA.user_id, Max(QSA.timestamp) AS MaxDate
FROM Q_SCANS_ALL AS QSA
GROUP BY QSA.user_id;

I get the correct results:

Code:
user_id	MaxDate
1	05/19/00 11:57:47 PM
5	05/19/00 11:57:47 PM
6	05/19/00 11:57:47 PM
7	05/19/00 11:57:47 PM
but I need all the fields. So when I do a query like this:

SELECT QSA.user_id, Max(QSA.timestamp) AS MaxDate, QSA.station, QSA.building
FROM Q_SCANS_ALL AS QSA
GROUP BY QSA.user_id, QSA.station, QSA.building;

(you're required to include all the shown fields in the GROUP BY if they're not in an aggregate function like MAX), I get this:

Code:
user_id	MaxDate			station	building
1	01/01/99 12:00:00 AM	1	1
1	05/19/00 11:57:47 PM	2	2
5	09/27/99 12:00:00 AM	1	1
5	05/19/00 11:57:47 PM	2	2
6	05/19/00 12:00:00 AM	1	1
6	05/19/00 11:57:47 PM	2	2
7	05/19/00 11:57:47 PM	2	2
Because it considers each complete record that does not have identical info in the GROUP BY fields to be a different group, each difference in station and building creates a new group for that user and a new MAX date. What I want is to group by just the user id, but still return all the fields in the matching record that has the MAX date in it.

I've tried various things, like using HAVING or some sort of ORDER BY or a subquery, but nothing seems like it'll do what I need. Can anyone think of anything that might help...I didn't think this fairly simple thing would be such a pain. I don't want to have to do this programmatically if I can avoid it.

Thanks,
-JoeyCode