If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Importing a file and saving to two linked tables ?
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. |
#2
|
|||
|
|||
Importing a file and saving to two linked tables ?
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. "mscertified" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Links picking up values from an older version of linked file | Cate | Links and Linking | 4 | October 20th, 2005 01:53 PM |
Saving publisher 2003 down to 2000 file size? | Chad | Publisher | 3 | September 12th, 2005 10:06 PM |
share a database | jwb | General Discussion | 11 | April 4th, 2005 09:14 PM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |