Solved User login / logoff activity in access 2016 (1 Viewer)

[Ihk]

Hi,
In multiuser login system, activity is recorded (login or logoff) in an access table below.
I have following columns.

Time stamp is the same column, but logon / logoff in different column.
How can I filter current logged in users, who have not yet logged of. I am not that much experience with queries.
If some one can help to filter.
Thanks

 

[June7]

I would not save data this way. I would save date/time into Login and Logoff fields of same record. I would have code find the user login record that does not have logoff value then populate with logoff date/time. Therefore when you want to know who did not log off just filter for records where Logoff Is Null.

Otherwise, try this: build aggregate query that counts Login and Logoff records for each user. Where count is not equal, someone has not logged out.

Really don't need two fields for activity type - just have 1 field with two possible values: Logon, Logoff.

 

[theDBguy]

You could also try a Totals query to return the Max date for each user and then check if it's a Login.

 

[CJ_London]

agree with other comments, just have one column to indicate login/off but with your current structure your query might be something like

SELECT UserIDD
FROM UsersActivity A INNER JOIN (SELECT UserIDD, max(timestamp) as Latest FROM UsersActivity GROUP BY UserIDD) AS L ON A.UserIDD=L.UserIDD AND A.timestamp=L.Latest
WHERE ActivityLogon is not null

 

[arnelgp]

SELECT subQuery.UserIDD, "Currently Login" AS Status
FROM
(SELECT UsersActivity.UserIDD,
(SELECT MAX(TimeStamp) FROM UsersActivity AS T
    WHERE T.UserIDD = UsersActivity.UserIDD
    AND NOT (T.ActivityLogin Is Null)) AS MaxLogin,
(SELECT MAX(TimeStamp) FROM UsersActivity AS T
    WHERE T.UserIDD = UsersActivity.UserIDD
    AND NOT (T.ActivityLogoff Is Null)) AS MaxLogoff
FROM UsersActivity
GROUP BY UsersActivity.UserIDD)  AS subQuery
WHERE (((subQuery.MaxLogin)>Nz([subQuery].[MaxLogoff],1)));

 

[Ihk]

SELECT subQuery.UserIDD, "Currently Login" AS Status
FROM
(SELECT UsersActivity.UserIDD,
(SELECT MAX(TimeStamp) FROM UsersActivity AS T
    WHERE T.UserIDD = UsersActivity.UserIDD
    AND NOT (T.ActivityLogin Is Null)) AS MaxLogin,
(SELECT MAX(TimeStamp) FROM UsersActivity AS T
    WHERE T.UserIDD = UsersActivity.UserIDD
    AND NOT (T.ActivityLogoff Is Null)) AS MaxLogoff
FROM UsersActivity
GROUP BY UsersActivity.UserIDD)  AS subQuery
WHERE (((subQuery.MaxLogin)>Nz([subQuery].[MaxLogoff],1)));

I am very thankful, without any further change, this code worked perfectly. great love.