multi access query based on date, user, device
I have an extract of data which I've imported into access which represents represents login/logoff activity of users/pc's. We have some suspicions that officers may be sharing accounts & passwords, which is a breach of policy. I wanted a way in access to query the data , to list all rows of data, ordered by date, whereby on the same day, the same account is logged into more than 1 unique machines. At present the key columns are field1 (the date, in the format dd/mm/yyyy), field 6 (the PC the account is logged onto, which in the format PC12345 for example), and field3 (the users account, in the format of text, username, e.g. jbloggs). If the account is logged into more than one unique PC, on the same day, I would like to see those records for further investigation. What would be the best way to filter the data to list all rows which fit this criteria.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Something like:
This would identify the Dates and Users who logged onto more than one PC on the same day. To get the actual PCs' they were logged onto, you would use:
SELECT T.[DateField], T,[User]
FROM (SELECT DISTINCT [DateField], [User], [Account]) as T
GroupBy T.[DateField], T.[User]
Having Count(T.[Account]) > 1
This would identify the Dates and Users who logged onto more than one PC on the same day. To get the actual PCs' they were logged onto, you would use:
SELECT yourTable.*
FROM yourTable as T
INNER JOIN (
SELECT T.[DateField], T,[User]
FROM (SELECT DISTINCT [DateField], [User], [Account]) as T
GroupBy T.[DateField], T.[User]
Having Count(T.[Account]) > 1) as Dups
ON T.[DateField] = Dups.[DateField]
AND T.User = Dups.User
Premium Content
You need an Expert Office subscription to comment.Start Free Trial