Suggestion for Append

B

Bonnie

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?

Thanks.
 
D

Dirk Goldgar

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.
 
B

Bonnie

Yes, that is what I'm trying to do. Your a good mind reader.

I'm going to work with your suggestion and will let you know.

Thanks!
 
D

Dirk Goldgar

Bonnie said:
Yes, that is what I'm trying to do. Your a good mind reader.

We get lots of practice. :)
I'm going to work with your suggestion and will let you know.

Please let me know if that approach doesn't work. There are other ways.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top