If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
eliminate dup records based on partial field contents
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; |
#2
|
|||
|
|||
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 R2D2Groupie wrote: 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; |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Default Value of field based on another field of same record | Naresh Kumar Saini | New Users | 5 | May 24th, 2005 03:11 AM |
combine fields from many records based on a key field | cwitczak | Database Design | 5 | May 4th, 2005 05:15 PM |
Field that populates based on other field | NewIdiot | General Discussion | 1 | April 7th, 2005 10:15 PM |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
NUMBERING the pages | Bob | New Users | 7 | June 14th, 2004 12:20 AM |