Duplicate tag #

[mr moe]

Hi All,
I have a table that has a device column and a tag # column, I want to pull out a report that shows duplicate tag # for different devices, the tag # can only be assigned to one device, I received an excel sheet that has to be fixed, I have the sheet in an access table. Please help. For example: device 123 and device 345 have tag #abc. I can’t simply create a query to show duplicate for tag# because I can have duplicate # only if it’s the same device.

 

[EMP]

Try this query.

SELECT *
FROM [TableName]
WHERE [Device] & " " & [Tag#] in (Select [Device] & " " & [Tag#] from [TableName] group by [Device],[Tag#] having Count("*")>1);


^

 

[mr moe]

Thanks, just tried it but not working, it's hanging up..

 

[georgedwilkinson]

How many rows are in the table? It shouldn't take long unless you have a ton of data.

 

[mr moe]

there is about 10,000, but still a problem, I am showing the duplicates, but i'm showing extra records, for example if a tag # is showing twice or more for different device #, then this is a problem and i want to show it, but if i have more than one tag # that belong to more than one device with the same device # then i want to ignore that, i think i have to create to query or subqruey inside one qurey where I want to show tag # count more than 1 and device # count less than 2 in this case I only will see unique devices with same tag #, man this is so hard to do... one tag # can only be assinged to one device , but in my table i have some duplicat records that i dont' want to show in the query, for example i have multiple records for the same tag # and device #, i only want to show the duplicate tag # for different devices. thanks