eliminate dup records based on partial field contents

[R2D2Groupie]

I was instructed to import 2 text files into 2 Access files - no problem.
Then compare records between them. Non dups records in second file need to be
appended to the first file. Duplicates are defined as the 5 leftmost chr of
[PostalCode] concatenated to 5 leftmost chr of [LastName] concatenated to the
3 leftmost chr of [Address]. Also, there may be 3 or more text files that
need to be appended to first file (Will handle later - I hope).
I started with scrubbing out dups with the following code. Have same record
in each file except one has an 11th chr in the address. This code let both
records in because they weren't dups. I need it to only read the first 3 chr
of the [Address], declare they are dups based on those 3, then prevent the
second record from coming in. Would appreciate any and all assistance.
SELECT s2.*,
Left(s2.[PostalCode],5)+Left(s2.[LastName],5)+Left(s2.[Address],3) AS DupKey
FROM [ProspectList2] as S2;
UNION SELECT s3.*,
Left(s3.[PostalCode],5)+Left(s3.[LastName],5)+Left(s3.[Address],3) AS DupKey
FROM [ProspectList3] as S3;

 

[John Spencer (MVP)]

You need to use an outer join, not a union. The following query should give you
all the records in ProspectList3 that have no match in ProspectList2. Then you
can turn this into an append query.

SELECT s3.*
FROM [ProspectList2] as S2 RIGHT JOIN [ProspectList3] As S3
ON Left(s2.[PostalCode],5)+Left(s2.[LastName],5)+Left(s2.[Address],3) =
Left(s3.[PostalCode],5)+Left(s3.[LastName],5)+Left(s3.[Address],3)
WHERE Left(s2.[PostalCode],5)+Left(s2.[LastName],5)+Left(s2.[Address],3) Is Null