Transitive Property

[Leviathan via AccessMonster.com]

All,

I'm doing some matching of data in Access and I'm running into an issue with
consolidation. Basically, I need to find a way to show that if A = B and B =
C then A = C.

See the following example:

ID Consolidated ID Consolidation Reason
1 1 1 = Same SSN
2 1 1 = Same SSN
3 3 2 = Same First Name, Last Name,
Date of Birth
4 3 2 = Same First Name, Last Name,
Date of Birth
2 2 3 = Same First Name, Last Name,
Date of Death
3 2 3 = Same First Name, Last Name,
Date of Death

Basically, this needs to reflect the following in the end:

ID Consolidated ID
1 1
2 1
3 1
4 1

Any idea how I can do this?

Thanks!

 

[KARL DEWEY]

What does your actual table structure look like? Table and field names with
datatype and even sample data would help (SSN use 3 digits in sample).
How do you want to display the matching?
You are using the term 'consolidation' but I do know what you want to
consolidate.

 

[Leviathan via AccessMonster.com]

This would be easier to show in Excel, but I'll try to type it out here:

Original Table:
ID LN FN DOB DOD SSN
1 DOE JOHN 1/1/1950 1/1/1980 123456789
2 DOES JOHN 1/1/1951 1/1/1981 123456789
3 DOES JOHN 1/1/1952 1/1/1981 123456781
4 DOES JOHN 1/1/1952 1/1/1982 123456783

Consolidation Table:
ID Consolidation ID Consolidation Reason
1 1 Same SSN
2 1 Same SSN
3 3 Same FN, LN, DoB
4 3 Same FN, LN, DoB
2 2 Same FN, LN, DoD
3 2 Same FN, LN, DoD

NOTE: Consolidation ID is the minimum "ID" value for a match. The hierarchy
for matches starts with (1) SSN (highest confidence), (2) FN, LN, DoB, (3) FN,
LN, DoD (lowest confidence)

Required Final Consolidation Table:
ID Consolidation ID
1 1
2 1
3 1
4 1

NOTE: 3 rolls into 1 because 2 matched to 1 by SSN and also 3 by FN, LN, DoD;
4 rolls into 1 because 4 rolls into 3, which rolls into 2, which rolls into 1

Thanks,
James

What does your actual table structure look like? Table and field names with
datatype and even sample data would help (SSN use 3 digits in sample).
How do you want to display the matching?
You are using the term 'consolidation' but I do know what you want to
consolidate.

All,

[quoted text clipped - 27 lines]

Thanks!

 

[KARL DEWEY]

Try this for a starting point --SELECT Leviathan.ID, Leviathan_1.ID AS
[Consolidation ID]
FROM Leviathan INNER JOIN Leviathan AS Leviathan_1 ON Leviathan.SSN =
Leviathan_1.SSN
WHERE (((Leviathan.ID)<[Leviathan_1].[ID]) AND
((Leviathan_1.ID)<>[Leviathan].[ID]))
ORDER BY Leviathan.ID

UNION ALL SELECT Leviathan.ID, Leviathan_1.ID AS [Consolidation ID]
FROM Leviathan INNER JOIN Leviathan AS Leviathan_1 ON (Leviathan.DOB =
Leviathan_1.DOB) AND (Leviathan.FN = Leviathan_1.FN) AND (Leviathan.LN =
Leviathan_1.LN)
WHERE (((Leviathan.ID)<[Leviathan_1].[ID]) AND
((Leviathan_1.ID)<>[Leviathan].[ID]))
ORDER BY Leviathan.ID

UNION ALL SELECT Leviathan.ID, Leviathan_1.ID AS [Consolidation ID]
FROM Leviathan INNER JOIN Leviathan AS Leviathan_1 ON (Leviathan.DOD =
Leviathan_1.DOD) AND (Leviathan.FN = Leviathan_1.FN) AND (Leviathan.LN =
Leviathan_1.LN)
WHERE (((Leviathan.ID)<[Leviathan_1].[ID]) AND
((Leviathan_1.ID)<>[Leviathan].[ID]))
ORDER BY Leviathan.ID;

It gives this for result --
ID Consolidation ID
1 2
2 3
3 4

This would be easier to show in Excel, but I'll try to type it out here:

Original Table:
ID LN FN DOB DOD SSN
1 DOE JOHN 1/1/1950 1/1/1980 123456789
2 DOES JOHN 1/1/1951 1/1/1981 123456789
3 DOES JOHN 1/1/1952 1/1/1981 123456781
4 DOES JOHN 1/1/1952 1/1/1982 123456783

Consolidation Table:
ID Consolidation ID Consolidation Reason
1 1 Same SSN
2 1 Same SSN
3 3 Same FN, LN, DoB
4 3 Same FN, LN, DoB
2 2 Same FN, LN, DoD
3 2 Same FN, LN, DoD

NOTE: Consolidation ID is the minimum "ID" value for a match. The hierarchy
for matches starts with (1) SSN (highest confidence), (2) FN, LN, DoB, (3) FN,
LN, DoD (lowest confidence)

Required Final Consolidation Table:
ID Consolidation ID
1 1
2 1
3 1
4 1

NOTE: 3 rolls into 1 because 2 matched to 1 by SSN and also 3 by FN, LN, DoD;
4 rolls into 1 because 4 rolls into 3, which rolls into 2, which rolls into 1

Thanks,
James

What does your actual table structure look like? Table and field names with
datatype and even sample data would help (SSN use 3 digits in sample).
How do you want to display the matching?
You are using the term 'consolidation' but I do know what you want to
consolidate.

All,

[quoted text clipped - 27 lines]

Thanks!