Bonnie said:
I am trying to append a table to two different tables. The import data has
no ContactID but Table1 and Table2 have a ContactID that is based on an
Autonum field. The Autonum field is on Table1 and is added to Table2 when
normally keyed in a linked subform. I am having a little trouble with the
concept of pulling the Max(ContactID) from Table1 and updating an interim
import table.
I have the Max(ContactID) in a seperate table but how do I add it plus one
to each record? I am getting the same number in each record?
What exactly is it that you're trying to do? Import a record and split it
into a parent record (in Table1) and a related record (in Table2)? And the
trick is to get the autonumber ContactID from the parent record into the
related record?
*IF* that's what you're after, then ...
It should be possible to do this in one go, by appending to a query that
joins the two target tables on the ContactID field. For example, suppose
you have two tables,
Contacts
--------------------
ContactID (autonum, PK)
FirstName (text)
LastName (text)
and
ContactAddresses
---------------------------
ContactAddressID (autonum, PK)
ContactID (long, FK: Contacts)
Street (text)
City (text)
State (text)
Zip (text)
And you want to append from a table like this:
ContactsImport
--------------------------
FirstName (text)
LastName (text)
Street (text)
City (text)
State (text)
Zip (text)
Then you could create a stored query like this:
qryContactsAddresses
----------------------------------
SELECT
Contacts.ContactID, Contacts.FirstName,
Contacts.LastName,
ContactAddresses.Street,
ContactAddresses.City,
ContactAddresses.State,
ContactAddresses.Zip,
ContactAddresses.ContactAddressID,
ContactAddresses.ContactID
FROM
Contacts LEFT JOIN ContactAddresses
ON Contacts.ContactID = ContactAddresses.ContactID;
Then you could target that query with an append query to do the import, like
this:
qappImportContacts
------------------------------
INSERT INTO qryContactsAddresses
(
FirstName,
LastName,
Street,
City,
State,
Zip
)
SELECT
FirstName,
LastName,
Street,
City,
State,
Zip
FROM ContactsImport;
This works for me, in a simple test. You may have to create a relationship
between the tables Contacts and ContactsAddresses, if one doesn't already
exist.
