Importing a file and saving to two linked tables ?

[Guest]

I'm importing a file ech month.
Currently the entire contents are saved in a table populated with an append
query.

However, lots of the data (over 100 of 200 columns) is used very seldom.
I want to store this data in a separate table linked in a one-to-one
relationship.
Here is the problem:
I need the second table's data to be linked to the autonumber key of the
original table. EG:
Table1:
Key = autonum
Table2
Refid = link to Table 1 Key

What is the best (quickest) way to achieve this?
Obviously I dont know what the autonumber keys will be until I have loaded
the first table.

 

[Guest]

Create Table1 and Table2 with the appropriate columns in each. Create a new
temp table with the same structure as the one you currently append these
records to. Seed the AutoNumber column so that it will start with the next
higher number than the original table's AutoNumber.

Append these records to the temp table instead, so that the AutoNumber will
be assigned to each record. Next, create two append queries, one for Table1
and the other for Table2. Append the records from the temp table to these
two tables using the appropriate columns. Delete the records in the temp
table when finished, compact the database (it's best if this is a table in a
different database that is linked to, so that the current database doesn't
bloat), then reseed the AutoNumber column in the temp table the _next_ time
the records need to be appended from the file.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.