Don't Include Duplicates

[mcflyy]

I'm trying to create one query that will return one address for one
matching SSN. No Duplicates. The following SQL (as created by Access
'02 in Query Design View) represents a query that returns an almost
perfect result.

SELECT DISTINCT [003_200-T_Prm-Fltrd_Frmtd_CGNS_Parts].SSN,
[003_300-T_Prm-Fltrd_Frmtd_FIDO_FF].ResAddr_Line1,
[003_300-T_Prm-Fltrd_Frmtd_FIDO_FF].ResAddr_AptNum,
[003_300-T_Prm-Fltrd_Frmtd_FIDO_FF].ResAddr_Line2, many more
fields.........
FROM [003_300-T_Prm-Fltrd_Frmtd_FIDO_FF] INNER JOIN
[003_200-T_Prm-Fltrd_Frmtd_CGNS_Parts] ON
[003_300-T_Prm-Fltrd_Frmtd_FIDO_FF].Part_Num =
[003_200-T_Prm-Fltrd_Frmtd_CGNS_Parts].Part_Num
WHERE ((([003_200-T_Prm-Fltrd_Frmtd_CGNS_Parts].SSN) Is Not Null And
([003_200-T_Prm-Fltrd_Frmtd_CGNS_Parts].SSN)<>"000000000"));


But the following query shows that there are still 181 duplicate SSN's
in the previous query's result, or 362 records that I don't to be
returned by the above query.

SELECT First([All Non Null and Non Zero SSNs with Addresses
Unique].SSN) AS [SSN Field], Count([All Non Null and Non Zero SSNs with
Addresses Unique].SSN) AS NumberOfDups
FROM [All Non Null and Non Zero SSNs with Addresses Unique]
GROUP BY [All Non Null and Non Zero SSNs with Addresses Unique].SSN
HAVING (((Count([All Non Null and Non Zero SSNs with Addresses
Unique].SSN))>1));

(Please keep in mind that that the long query name will of course
become shorter as the DB structure progresses)


My theory is that I should be able to put a subquery into the first
query to eliminate the remainder of the duplicate SSN's, but I cannot
seem to get it right. More times then not, when I include a subquery,
the query "goes on forever" never returning a result and sometimes
locking up the app.

Much thanks for all help that can be contributed!!!

 

[mcflyy]

Just an addition:

DISTINCT (Unique Values = Yes) is technically doing it's job. As you
can see in the first Query, the tables are connected by a Participant
Number. And with Distinct, is it correctly providing the address info
for one SSN per Participant Number.

The duplicate SSN's that still come through, and returned in the second
query, however, appear to have 2 distinct Participant Numbers. This is
a different problem in and of its self that will have to be remedied by
users.

These duplicates need to be eliminated as the first query will be
changed into an update query that will update a table where SSN is the
only Key, and no duplicates are allowed.

So in short, the first query is working properly, but now how do I
further eliminate the records where the SSN is the same but the
Participant Number is different?

I feel that there is a way to do it in one query, the first one, with a
few tweaks, but I just can't seem to get it right. I would really not
want to have to create a third, unmatched, query between the first
query and the second to produce what seems to be a simple result.