Identify Twins

[IrishRed]

Hello All,
I feel like what I am looking to do is probably simple but I just can not
seem to figure it out myself. I would appreciate any suggestions.

I have a table that has SSN, first name and date of birth. The SSN can exist
more than once as it represents the head of the family. Here is a sample of
the data:

SSN FirstName DOB
123456789 Joe 07/11/1960
123456789 Kim 02/01/1980
123456789 Kevin 02/01/1980

I need to create a query to identify the records with twins and possibly
move them to a separate table via a make table query or something. In the end
I want to strip out the records for any tiwns (identified by same SSN and
DOB).

Was thinking of doing a count query to identify the twins and then was at a
loss for how to proceed from there. If I am overthinking this, please
straighten me out on it.

Thanks for your time.

 

[John W. Vinson]

Hello All,
I feel like what I am looking to do is probably simple but I just can not
seem to figure it out myself. I would appreciate any suggestions.

I have a table that has SSN, first name and date of birth. The SSN can exist
more than once as it represents the head of the family. Here is a sample of
the data:

SSN FirstName DOB
123456789 Joe 07/11/1960
123456789 Kim 02/01/1980
123456789 Kevin 02/01/1980

I need to create a query to identify the records with twins and possibly
move them to a separate table via a make table query or something. In the end
I want to strip out the records for any tiwns (identified by same SSN and
DOB).

Was thinking of doing a count query to identify the twins and then was at a
loss for how to proceed from there. If I am overthinking this, please
straighten me out on it.

Thanks for your time.

A "Self Join" query will work here. The SQL would be

SELECT A.SSN, A.DOB, A.FirstName, B.Firstname
FROM Yourtable AS A
INNER JOIN yourtable AS B
ON A.SSN = B.SSN
AND A.DOB = B.DOB
WHERE A.Firstname > B.Firstname;

I use > rather than <> because otherwise it would give you two records - Kim
and Kevin, and then Kevin and Kim.

 

[Ken Sheridan]

I would advise against moving the twinned rows from one table into another.
Its not good database design to separate into two or more tables subsets of
the same entity type which share all the same attributes. There would also
be maintenance implications; if one twin dies for instance you'd have not
only to delete a row from the twins table but move a row back into the
non-twins table.

The only reason for splitting them would be if twins and non-twins has
attributes unique to each, i.e. if twins is a sub-type of people, in which
case the solution would be not to move the rows, but to create two additional
tables to model each sub-type, relating them in one-to-one relationships to
the current people table on the SSN and DOB columns. Each of these sub-type
tables would contain, in addition to the two key columns, columns to model
the attributes unique to each sub-type. Otherwise the correct approach would
be to keep the current table as is, and return the twins in a query like this:

SELECT *
FROM YourTable As T1
WHERE EXISTS
(SELECT *
FROM YourTable AS T2
WHERE T2.SSN = T1.SSN
AND T2.DOB = T1.DOB
AND T2.FirstName <> T1.FirstName)
ORDER BY SSN;

and the non-twins with a query like this:

SELECT *
FROM YourTable As T1
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS T2
WHERE T2.SSN = T1.SSN
AND T2.DOB = T1.DOB
AND T2.FirstName <> T1.FirstName)
ORDER BY SSN;

Both queries are updatable, so can be used in exactly the same way as a base
table, including as the RecordSource for a form for editing data.

Ken Sheridan
Stafford, England

 

[IrishRed]

Thank you both. I learned so much from this one post. I really appreciate it.
Thanks for the great advice.