DUPLICATE RECORDS with different Id

[wmsalamanca]

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
SSN	ID
123456789​	l654
123456789​	T458

Query output
SSN	id1	id2
123456789​	l654	T458

 

[theDBguy]

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]

I under stand the how to do the duplicate query, but I don't understand the second query.

 

[theDBguy]

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.

 

[wmsalamanca]

Yes, but how will i be able to pull them into two columns?

 

[theDBguy]

Yes, but how will i be able to pull them into two columns?

Try using a Crosstab Query.

 

[wmsalamanca]

I did the the first table, when I join them to the original table I getting the same results of the original table.

Try using a Crosstab Query.

Try using a Crosstab Query.

I tried doing a crosstab query but when I put the id as column is not the solution.

 

[plog]

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:

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,

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:

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