Split multiple fields into individual records

[skwilliams]

I have a table with data populated daily from another system. The table displays data this way.

FName, LName, Address, City, State, Postal, Cat, Sample1, Sample2, Sample3, etc.
Each record can contain up to 10 samples.

I would like to store the data in two tables like this so each sample would have its own record in the child table.

tblSampleMain
pkID, FName, LName, Address, City, State, Postal, Cat
and
tblSamples
pkID, fkID, Samples

with a relationship between the tblSampleMain.pkID and tblSamples.fkID.

Is there an easy way to append this data from the current table to the new tables as listed.

 

[GinaWhipp]

In the table that holds all the data create a PK using AutoNumber. Create your new tables with an extra field to hlkd the AutoNumber from the main table. Append the data to the first table. Rgen on the second table append the dara using the link from the main table to the first table you created... Make sense?