automationamateur
Board Regular
- Joined
- Jan 3, 2011
- Messages
- 166
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
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