Append Queries and Autonumbers

[lismeta]

I am appending to two different tables.
tblOne has an autonumber(ID) and this appends fine without using an ID
field in the query.
tblTwo has a field(number) which links to the ID field in tblOne.

I am currently using two different append queries. The one for the first
works great.
In the second query I really need to get the ID field assigned in tblOne
before I can append to the second query.

Any suggestions?

Pax,
Mary

Teach me to fish.

 

[Guest]

Hi

It would be useful for you to tell us a bit more about the data you are
appending and where it is coming from and in what format etc

However, i'll assume you're importing a file where some data from each row
belongs in a "header" type table and some belongs in a "detail" table.

Append the relevant header data first as you are doing in your first query.

Then, we need to append the detail data into a separate table but we also
have to give each row of data the correct autonumber id from query 1 so the
correct detail links to the correct header.

To do this, add the header table to the append query and join it to the
source data.

Eg

tblSourceData
InvoiceDate
InvoiceNumber
ItemNumber
ItemValue

Append1
INSERT INTO tblInvoiceHeader
SELECT InvoiceDate, InvoiceNumber
FROM tblSourceData

Append2
INSERT INTO tblInvoiceDetail
SELECT tblInvoiceHeader.ID, tblSourceData.ItemNumber, tblSourceData.ItemValue
FROM tblInvoiceHeader INNER JOIN tblSourceData
ON tblInvoiceHeader.InvoiceNumber = tblSourceData.InvoiceNumber

In this example, InvoicveNumber correctly links all rows of the source data.
It may be for your data that 2 or more fields will need to be used. If you
have a different scenario altogether then you will need to describe how you
can link the "header" and "detail" from the source data.

hth

Andy Hull

 

[Fifth Amendment]

SQL Server has this functionality; it is called OUTPUT I believe and it's a
new feature in SQL Server 2005 Insert functionality

you can get a whole bunch of numbers at the same time

ADO and ADP have this functionality, one record at a time using @@identity

Access MDB is a crap database and it doesn't suit your needs