multi access query based on date, user, device

pma111
pma111 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Something like:

SELECT T.[DateField], T,[User]
FROM (SELECT DISTINCT [DateField], [User], [Account]) as T
GroupBy T.[DateField], T.[User]
Having Count(T.[Account]) > 1

Open in new window


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

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial