Split 1 table into 2 linked tables

[yessir]

I have a table with the followig fields where I do all the work on imported data. I then need the data to be sent to their permanent tables. Some of the below data is repeated in that there are many of the same sample no and client id to the different results.

The permanent tables solve this repetition by using a parent table and child table.

Thus I need to extract the distinct first part of the table and the related results into the parent and child table respectively.

Here are the temp table fields: (tblWater_Sample_Temp)
ClientID
ProjectNo
LabID
Matrix
DuplicateOf
SampleDate
Analyte
Method
Result
Units
LOQ
Outlet
Elevation Unit
DataSource
Note
DL
FlowValue
FlowUnit
Notes
Duplicate
ERR_MATRIX
ERR_ANALYTE
ERR_METHOD
ERR_OUTLET
ERR_UNITS

These need to be extracted into the 2 tables tblWater_Sample_Info, and the child tblWater_Sample_Results.

the ones for tblWater_Sample_Info are:
ClientID
ProjectNo
LabID
Matrix_ID
SampleDate
SampleTime
Outlet_ID
Duplicate
DuplicateOf
Elevation
Unit_ID
DataSource
Note

the ones for tblWater_Sample_Results are:
Analyte_ID
Method_ID
Result
Unit_ID
DL
LOQ
FlowValue
FlowUnit_ID
Notes

Any help on how to do this would be greatly appreciated!

~

 

[ByteMyzer]

Two Append Queries, in the following sequence, should do the trick:

INSERT INTO tblWater_Sample_Info (ClientID, ProjectNo, LabID, Matrix_ID, SampleDate, SampleTime, Outlet_ID, Duplicate, DuplicateOf, Elevation, Unit_ID, DataSource, Note)
SELECT DISTINCT ClientID, ProjectNo, LabID, Matrix, SampleDate, Format(Now(),'Short Time'), Outlet, Duplicate, DuplicateOf, [Elevation Unit], Units, DataSource, Note
FROM tblWater_Sample_Temp;

INSERT INTO tblWater_Sample_Results (ID, Analyte_ID, Method_ID, Result, Unit_ID, DL, LOQ, FlowValue, FlowUnit_ID, Notes)
SELECT ClientID, Analyte, Method, Result, Units, DL, LOQ, FlowValue, FlowUnit, Notes
FROM tblWater_Sample_Temp;

 

[Pat Hartman]

I don't see a foreign key in the child table that will link it back to the appropriate parent.

 

[yessir]

THey have common id's