By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,187 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

Highest Value For Each Person In the Database

P: n/a
Hi

Here's my problem.

Let's say i have a table which records whenever a user logs on to a
computer system.

So someone logs on and it records something like Fred, 10th March 2004
19:54

If Fred logs on again it will write another record to the database.

Same with Sally and Barbara and Bill and Jim etc.

So each user will have multiple records.

How do I do a query that shows me each user and the last time they
logged in (ie the highest LoggedIn value for that user).

By the way, if there's better ways to organise the data that's not an
issue as this is downloaded data I'm dealing with.

Many thanks

Al
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
SELECT tbl_YourTable.UserID, Max(YourTable.TimeIn) AS Latest_In
FROM YourTable
GROUP BY YourTable.UserID;

"Alun" <al******@blueyonder.co.uk> wrote in message
news:3c**************************@posting.google.c om...
Hi

Here's my problem.

Let's say i have a table which records whenever a user logs on to a
computer system.

So someone logs on and it records something like Fred, 10th March 2004
19:54

If Fred logs on again it will write another record to the database.

Same with Sally and Barbara and Bill and Jim etc.

So each user will have multiple records.

How do I do a query that shows me each user and the last time they
logged in (ie the highest LoggedIn value for that user).

By the way, if there's better ways to organise the data that's not an
issue as this is downloaded data I'm dealing with.

Many thanks

Al

Nov 13 '05 #2

P: n/a
Doh hope I'm not posting this twice. Sorry if I am but am not sure my
other post got through.

Just to say thanks Mark that works a treat.

One other thing though. If I need to return an additional field (this
dataset has no UserIDs!) that is not part of the GROUP BY, is that
possible?

Again, thanks

Al

Mark wrote:
SELECT tbl_YourTable.UserID, Max(YourTable.TimeIn) AS Latest_In
FROM YourTable
GROUP BY YourTable.UserID;

"Alun" <al******@blueyonder.co.uk> wrote in message
news:3c**************************@posting.google.c om...
Hi

Here's my problem.

Let's say i have a table which records whenever a user logs on to a
computer system.

So someone logs on and it records something like Fred, 10th March 2004 19:54

If Fred logs on again it will write another record to the database.

Same with Sally and Barbara and Bill and Jim etc.

So each user will have multiple records.

How do I do a query that shows me each user and the last time they
logged in (ie the highest LoggedIn value for that user).

By the way, if there's better ways to organise the data that's not an issue as this is downloaded data I'm dealing with.

Many thanks

Al


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.