Copy non-duplicates from new file to existing file

[Ron]

Hi All,

A2k under winxp with all updates/upgrades, etc. I have main file
called tblClients with many controls, some of which are LastName,
FirstName, DateOfBirth, ClientNumber, ClientID (pk), StreetAddress,
etc.

I have a new requirement to import data from another system 2-3 times
a week from a file called ImportFile (actually this isn't a mdb file--
it's from a mini computer and they're putting it into a .dbf file--I
know how to link to this file, and then work with it. I'm checking to
see if it can be a standard access table to begin with, instead of
this dbf file, but that can happen later). Fields are Lname, Fname,
dob, NumberID, StAddress, etc (no primary key but the NumberID can be
equal to ClientNumber on main file.

First, I want to make sure I don't transfer any record from ImportFile
that has a NumberID equal to existing ClientNumber in tblClients.

In addition, I want to make sure I don't transfer any record from
ImportFile that has a client who's got the same first and last name
plus date of birth as an existing record in tblClients.

Transfer all records into tblClients that don't match so... avoiding
possible duplicates. Then I'll print out the ones not transferred so
the user can decide to manually add ones that turned out to not be
duplicates (from the last name, first name and date of birth
limitation).

I've tried every which way to figure this out on my own and I'm more
mystified today than when I started trying things yesterday. 1 day,
blown!

Anyone give me a heads up about where to start?

TIA
ron

 

[Douglas J. Steele]

Link to the dbf file (through File | Get External Data | Link Tables, and
write queries against the linked table. The following should return all new
customers:

SELECT ClientID, ClientNumber, LastName, FirstName, DateOfBirth
FROM LinkedTable LEFT JOIN tblClients
ON LinkedTable.ClientID = tblClients.ClientID
WHERE tblClients.ClientID IS NULL
UNION
SELECT ClientID, ClientNumber, LastName, FirstName, DateOfBirth
FROM LinkedTable LEFT JOIN tblClients
ON LinkedTable.LastName = tblClients.LastName
AND LinkedTable.FirstName = tblClients.FirstName
AND LinkedTable.DateOfBirth = tblClients.DateOfBirth
WHERE tblClients.ClientID IS NULL