insertion into table

[yessir]

I have a temp table where I import information and do work on it.

I want to transfer the information to the permanent tables upon completion.
Here is the problem:

the temp table contains multiple instances of the same information in the first half of the fields and new info in the other half.

I am inserting this informaiton into 2 tables where the repeated info is in one and the other info is linked to it in another table.

EX:
hz250-en15 H2540 Water Antimony 2
hz250-en15 H2540 Water Arsenic 3.1

These are examples and there are a TON more fields.

I will include the db incase you can look at it.
But I am also looking for suggestions on how to insert the first half and ensure that the second half is linked to it.

Here is the kicker, in the permanent tables a lot of the information is just numbers, ID's to reference tables and that is what should be inserted not the actual value in the temp table. so it probably will have to be don record by record, field by field doing lookups on a lot of fields (not all).

The form that's doing the inserting is frmWater_Sample_Import and is inserting in to tblWater_Sample_Info and tblWater_Sample_Results from tblWater_Sample_Temp

the other forms are for viewing

Any help is GREATLY appreciated
~

 

[Len Boorman]

I think I have an idea of your problem.

I think that I would use queries to extract the data I want to insert into another table. You will probably need a couple of them following the idea below.

Query 1 "Select Query"
Extract basic data to go to a particular table

Query 2 "Select Query"
Use Query 1 linked to one , some or all of the other tables that you need in order to extract the referece ID's

Query X "Select Query"
Use Query 2 linked to one , some or all of the other tables that you need in order to extract the referece ID's Carry on until you have all the necessary references.

Query X+1 "Append Query"
Extract from Query X the actual data items you want to append to the permanent table.

Then do it all again for other permanent tables

HTH

Len