Find Duplicates in one field AND where another field is unmatched

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to write a select statement that would find Duplicate Employee 's
where Terminal is not equal. So out of my example data ID 1 & 2 would be
returned

Example Data Set

ID EMPLOYEE TERMINAL
-------------------------------------
1 Chris 111
2 Chris 123
3 George 222
4 George 222
5 Tom 567

I'm using Access XP (10)
Thanks
Chris
 
Try something like

SELECT T1.ID, T1.EMPLOYEE, T1.TERMINAL
FROM TableName As T1
Where T1.EMPLOYEE In (SELECT T2.EMPLOYEE FROM TableName As T2 Where
T2.EMPLOYEE = T1.EMPLOYEE And T2.TERMINAL <> T1.TERMINAL)
 
I don't think this will work as it appears that you are comparing the data in
two seperate tables? I have the one table.

Chris
 
Perhaps, something like the following

SELECT ID, Employee, Terminal
FROM SomeTable
WHERE SomeTable.Employee IN
(SELECT D.Employee
FROM (SELECT Distinct Employee, Terminal
FROM SomeTable) as D
GROUP BY D.Employee
HAVING Count(D.Employee)>1)
 
Maybe this will help. Here is my SQL so far...

SELECT tbl_MultipleLogonImport.ID, tbl_MultipleLogonImport.[User name],
tbl_MultipleLogonImport.[User Master Maintenance: User],
tbl_MultipleLogonImport.[USR41-LOGON_TIME], tbl_MultipleLogonImport.Terminal,
tbl_MultipleLogonImport.TermID, tbl_MultipleLogonImport.[USR41-LOGON_DATE]
FROM tbl_MultipleLogonImport;
 
I used only one table in my example, the TableName is one, It just used once
as T1 and second time as T2.

If you provided the right fields name, copy this SQL to a query, and change
the TableName with the name of your table
 
It might help, IF you had specified which fields were the ones you wanted to
match on and which fields you needed


Having field names that require the brackets (spaces in names) will probably
require that you do three queries. One to get unique values and then using
that in your table to get the records you want.

Q_Distinct

SELECT Distinct [User Name], [Your Other Field]
FROM tbl_MultipleLogonImport

Q_Count
SELECT [User Name]
FROM Q_Distinct
GROUP BY [User Name]
HAVING Count([User Name]) > 1

Finally
SELECT *
FROM tbl_MultipleLogonImport Inner Join Q_Count
ON tbl_MultipleLogonImport.[User Name] = Q_Count.[User Name]
 
Back
Top