find duplicates - sort of (1 Viewer)

[cyberpac9]

say i have a table called tblEmployees and i want to find people who have the same name but different employee numbers, can that be done with a query or will that require VBA code? i won't know the names ahead of time, i don't know who has the same name.

for example, columns:
first_name, last_name, employee_num, phone_num, office_num

and i want it to return James Smith with employee_num = 1234 and James Smith with employee_num = 9876.

 

[lightray]

Click the New button, and use the find Duplicates Wizard

 

[cyberpac9]

well, it's not that easy...i was hoping to avoid this part, but here goes...we are using access as a frontend to sql server and thus do not have that capability...i have to enter the query manually...

 

[lightray]

Okay! Here's the sql for testing for Duplicates from a DB I have, you can adapt.

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.EmployeeID, tblEmployees.HomePhone, tblEmployees.WorkPhone
FROM tblEmployees
WHERE (((tblEmployees.LastName) In (SELECT [LastName] FROM [tblEmployees] As Tmp GROUP BY [LastName],[FirstName] HAVING Count(*)>1  And [FirstName] = [tblEmployees].[FirstName])))
ORDER BY tblEmployees.LastName, tblEmployees.FirstName;

Haven't tested it as I don't have any duplicates.
Let me know how you get on. lightray

 

[cyberpac9]

duh! i must've been having a brain fart...a sub-select...geez! thanks, i got it..

and we don't really have duplicates either.....they are different, just trying to find out entries that are essentially the same....

thanks again for your help...

 

[Mike375]

I would first have the first name and second name joined....

James Smith becomes in another field....JamesSmith.......perhaps call the new calculated field FL for firstname last name.

Here is the criteria on a field CL Surname that was done by the duplicate query wizard and for a table called MainTable

In (SELECT [CL Surname] FROM [MainTable] As Tmp GROUP BY [CL Surname] HAVING Count(*)>1 )