Data Import Advice

[automationamateur]

I have two tables (this is a simplified version for simplicity sake, see below). I am fed data from a referral agency that has client information and appointment information. If client is a new client (this is a field on data feed) I want to append data to tblClient, get a new client # and then go back and add the appointment data (with newly assigned client #) to tblCheck_In. If client is now new I only want to append appointment data to tblCheck_In. What would be the best way to go about handling these two scenarios? Your help is greatly appreciated. See below for what I've tried

tblClient
Field 1 = ClientNumber (primary key, autonumber, unique values)
Field 2 = FirstName
Field 3 = LastName
Field etc, etc, etc

tblCheck_In
Field 1 = CheckInNumber (primary key, autonumber, unique values)
Field 2 = ClientNumber (foreign key of Field 1 in tblClient)
Field 3 = Appt Type
Field etc, etc, etc


Existing client: import data gets put into holding table, query pulls holding table and tblClient together and gets existing client # by matching first and last name fields between tables. then query appends data to tblCheck_In. but this fails due to key violations

New client (haven't tried this yet but if i can't get the above approach to work I don't think i'll be successful with this one): query pulls all new clients from holding table, appends to tblClient. Another query basically repeats process of existing client steps (above) to get data into tblCheck_In

 

[automationamateur]

Any takers?

 

[AngelJ]

Are you doing this in VBA or with queries?

 

[automationamateur]

My first attempt (the one that failed due to key violations) was with queries but I'm open to any suggestions. I can do some simple VBA stuff so I'm not ruling that out. What I want to achieve seems so simple but I just can't get my head around it.

 

[AngelJ]

I think doing it with queries should be good. But, rather than thinking of it as a single operation that does one thing in the new client case, and another thing in the existing client case, I would think of it like this:

Add any new clients to the clients table:

INSERT INTO tblClient
SELECT tblHolding.FirstName, tblHolding.LastName
FROM tblHolding LEFT JOIN tblClient
ON tblHolding.FirstName = tblClient.FirstName AND tblHolding.LastName = tblClient.LastName
WHERE ClientNumber IS NULL

Now, all clients should be in the database, so add the appointments:

INSERT INTO tblCheck_In
SELECT ClientNumber, tblHolding.[Appt Type]
FROM tblHolding INNER JOIN tblClient
ON tblHolding.FirstName = tblClient.FirstName AND tblHolding.LastName = tblClient.LastName

Edit: Btw, I usually wouldn't rely on First and Last name to distinguish unique clients, but your situation my be different.

 

[automationamateur]

Fantastic. I've implemented this and it works just like I wanted, I was failing on the left join in first query, i was doing an inner join for some reason. Thanks so much. And yes, I actually rely on the combination of first, last, and birth date. These 3 work pretty well, unfortunately we don't store any better fields like a social security number and all other fields submitted to us are formatted too random to use as a key (like address). THANKS AGAIN

 

[AngelJ]

Glad that worked for you! First name, last name, and birth date sounds good, but yeah, anything other than a social security number is going to have the possibility of getting it wrong.