SQL query

[Guest]

I have a card controlled entrance door which updates an access database every
time it is used. tblUsers contains everyone who uses the door, tblEvents
contains a log of every pass through the door. tblEvents.SubAddr contains the
direction of travel - 1 for entry, 2 for exit.

I want to generate a query to show who is inside the building at the current
time - so essentially, I want a list of all users whose last direction of
travel is incoming. Here's what I've got so far:

SELECT Events.EventTime, Events.Address, Events.SubAddr, Events.UserID,
Users.Firstname, Users.Surname
FROM Events INNER JOIN Users ON Events.UserID=Users.UserID
Order By Events.UserID, EventTime Desc

This gives me a list of every event for every user. I need to filter this so
I only get the top record for each user. Bonus karma if that can then be
filtered to show only the users whose last Events.SubAddr=1

I suspect this isn't going to be a single statement solution, but I'd love
someone to prove me wrong.

 

[Brendan Reynolds]

SELECT Events.EventTime, Events.Address, Events.SubAddr, Events.UserID,
Users.FirstName, Users.Surname
FROM Events INNER JOIN Users ON Events.UserID = Users.UserID
WHERE ((((SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID))=(SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID AND SubAddr = 1)))
ORDER BY Events.UserID, Events.EventTime DESC;

BTW: As this query will return only one record per UserID, I think the
second column in the ORDER BY clause is redundant.

 

[Guest]

Exactly what I was looking for! Thank You Brendan!

Tim

SELECT Events.EventTime, Events.Address, Events.SubAddr, Events.UserID,
Users.FirstName, Users.Surname
FROM Events INNER JOIN Users ON Events.UserID = Users.UserID
WHERE ((((SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID))=(SELECT Max([EventTime]) FROM Events WHERE UserID =
Users.UserID AND SubAddr = 1)))
ORDER BY Events.UserID, Events.EventTime DESC;

BTW: As this query will return only one record per UserID, I think the
second column in the ORDER BY clause is redundant.

--
Brendan Reynolds

I have a card controlled entrance door which updates an access database
every
time it is used. tblUsers contains everyone who uses the door, tblEvents
contains a log of every pass through the door. tblEvents.SubAddr contains
the
direction of travel - 1 for entry, 2 for exit.

I want to generate a query to show who is inside the building at the
current
time - so essentially, I want a list of all users whose last direction of
travel is incoming. Here's what I've got so far:

SELECT Events.EventTime, Events.Address, Events.SubAddr, Events.UserID,
Users.Firstname, Users.Surname
FROM Events INNER JOIN Users ON Events.UserID=Users.UserID
Order By Events.UserID, EventTime Desc

This gives me a list of every event for every user. I need to filter this
so
I only get the top record for each user. Bonus karma if that can then be
filtered to show only the users whose last Events.SubAddr=1

I suspect this isn't going to be a single statement solution, but I'd love
someone to prove me wrong.