DUPLICATE RECORDS with different Id (1 Viewer)

wmsalamanca

New member
Local time
Today, 16:44
Joined
May 27, 2014
Messages
11
I have a table with duplicate record on SSN but the id is different. I need to create a query that will have ssn and the id side by side. please see sample.

Table1
SSNID
123456789​
l654
123456789​
T458




Query output
SSNid1id2
123456789​
l654T458
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:44
Joined
Oct 29, 2018
Messages
21,358
You may have to use more than one query for this. For example, create one query to list the duplicate ssn and then use another query to add the ids.
 

wmsalamanca

New member
Local time
Today, 16:44
Joined
May 27, 2014
Messages
11
I under stand the how to do the duplicate query, but I don't understand the second query.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:44
Joined
Oct 29, 2018
Messages
21,358
I under stand the how to do the duplicate query, but I don't understand the second query.
Once you have a list of duplicate SSNs, you should be able to create another query to JOIN that query to the original table, so you can pull the IDs.
 

plog

Banishment Pending
Local time
Today, 15:44
Joined
May 11, 2011
Messages
11,611
This can be accomplished with a cross tab, however you need a few other queries to prep the data for that cross tab. Here's what you need:

Code:
SELECT Table1.SSN, Table1.ID
FROM Table1
GROUP BY Table1.SSN, Table1.ID;

Paste that into a new query and name it 'sub1'. It gets all the unique SSN/ID combinations. Next,

Code:
SELECT sub1.SSN, sub1.ID, DCount("[ID]","sub1","[SSN]='" & [SSN] & "' AND  [ID]>='" & [ID] & "'") AS N
FROM sub1;

Paste that into a new query and name it 'sub2'. For the crosstab to work you must assign an order to every unique ID, so that it nows which is ID1 and ID2. Finally the below crosstab will give you the results you want:

Code:
TRANSFORM Max(sub2.ID) AS MaxOfID
SELECT sub2.SSN
FROM sub2
GROUP BY sub2.SSN
PIVOT "ID" & [N];
 

Users who are viewing this thread

Top Bottom