Show only duplicates 1

[thefourthwall]

I'm working with a select query and want to show just the duplicate values of Employee_ID. After first searching here I attempted to start the duplicates query wizard in Access but that wizard seems to be unavailable.

I want to show just the duplicates, and how many instances of them there are, but am having trouble with the results; my SQL knowledge is still sparse. The code is:

SELECT tblDevice.Cell_Number, tblEmployee.LAST_NAME, tblEmployee.FIRST_NAME, tblEmployee.MIDDLE_INITIAL, tblEmployee.EMPLOYEE_ID, tblDevice.Spare, tblDevice.Aircard, tblDevice.PTTyn, tblDevice.PushToTalkOnly, tblDevice.WPSyn, tblDevice.ESN, tblDevice.SIM, tblDevice.MSNorPIN, tblDevice.IMEI, tblDevice.Direct_Connect_Number, tblDevice.Comments, tblDevice.Model, tblDevice.Carrier, tblDevice.Date_Last_Change, tblDevice.EID, tblEmployee.NAME_SUFFIX, tblEmployee.TZS_CODE, tblEmployee.TITLE_CODE, tblEmployee.AD_USER_NAME, tblEmployee.TZS_DESCRIPTION, tblEmployee.TITLE_DESCRIPTION, tblEmployee.SEX_CODE
FROM tblEmployee INNER JOIN tblDevice ON tblEmployee.EMPLOYEE_ID = tblDevice.EID
ORDER BY tblEmployee.LAST_NAME, tblEmployee.FIRST_NAME, tblEmployee.MIDDLE_INITIAL, tblEmployee.EMPLOYEE_ID;

I want to show just the entries with duplicate EMPLOYEE_ID values to know which users are assigned more than one cell phone, but need help. Thanks.

 

[adalger]

You'll want to start with something like this:

SELECT tblDevice.EID, Count(tblDevice.Cell_Number) AS NumPhones from tblDevice GROUP BY tblDevice.EID HAVING Count(tblDevice.Cell_Number)>1

This will give you a list of all your employee IDs that have more than one cell number, and how many they have. You can use this as the basis for further information extraction.

TMTOWDI - it's not just for Perl any more

 

[traingamer]

You need to add a where clause to find the employees that have more than one entry in the tblDevice table. Try this:

WHERE (((tblEmployee.EMPLOYEE_ID) In (SELECT [EID] FROM [tblDevice] As Tmp GROUP BY [EID] HAVING Count(*)>1 )))

above your ORDER BY clause.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[thefourthwall]

traingamer,

perfect - that's exactly what I was looking for! Thank you.

 

[thefourthwall]

adalger,

Thanks for your reply - that started me thinking in a better direction.