Generate a Fire Register report

Question

Hello, I am stuck figuring out a working SQL Query for the fallowing:
I need to generate a Fire Register report (how many people are still inside the building) based on an Access database that records login/logout events along with some metadata. The Access DB looks like this:

+----+---------------------+---------+---------+------+
| id | date                | action  | success | user |
+----+---------------------+---------+---------+------+
|  1 | 2009-04-28 02:00:00 |   login |       1 | Nick |
|  2 | 2009-04-28 03:00:00 |  logout |       1 | Nick |
|  3 | 2009-04-28 04:00:00 |   login |       1 | Nick |
|  4 | 2009-04-28 04:00:00 |  logout |       1 | Nick |
|  5 | 2009-04-28 04:00:00 |   login |       1 | Nick |
|  6 | 2009-04-28 07:00:00 |   login |       1 | John |
|  7 | 2009-04-28 07:30:00 |   login |       1 | Sue  |
|  8 | 2009-04-28 08:00:00 |  logout |       1 | John |
+----+---------------------+---------+---------+------+

During the day there can be multiple login/logout actions. When administrator runs the report, it's only limited for current day and needs to list all users where the last known action for this user is login and success=1, meaning that this person is currently in the building.

On the data above, Nick and Sue must be pointed out as still being inside the building.

Answer 1

**sorry typo in the original

SELECT     f.[User], la.MostRecent, f.[action]
FROM         
(
   SELECT 
       MAX(ID) AS MaXID, 
       MAX(Date) MostRecent, 
       success, 
       [USER] 
   FROM          
       Fire
   WHERE
       Success = 1
   GROUP BY 
       success, [USER] 
) AS la
JOIN Fire f
  ON la.MaxID = f.ID
WHERE     ([action] = 'login')

Answer 2

This would work in SQL, but you'd have to try it with Access.

select *
from events e1
where action = login
      and success = 1
      and date = (select max(date) from events e2 where e1.user = e2.user)

Answer 3

Another approach to the problem:

SELECT
     T1.user
FROM
     Some_Table T1
LEFT OUTER JOIN Some_Table T2 ON
     T2.user = T1.user AND
     T2.success = 1 AND
     T2.date > T1.date
WHERE
     T1.success = 1 AND
     T1.action = 'login' AND
     T2.id IS NULL

This assumes that you only care about successful actions. Also, if a user has two actions on the same EXACT date AND time then it might not act as expected.

Answer 4

This assumes the ID's are always incremented by a positive number and are unique.

SELECT LogTable.* 
FROM LogTable 
INNER JOIN (Select User, Max(ID) AS LastID 
                    FROM LogTable 
                   GROUP BY LogTable.User
                  ) as LastLog
ON LogTable.User = LastLog.User 
           AND LogTable.ID = LastLog.LastID 
WHERE LogTable.success = 1 AND LogTable.action = 'login';

Based on assumption, you don't have to worry about a datetime not being unique.

Hope people are not in the habit of holding the door open for others who don't log in.