Transferring data from one "table structure" to another "table structure"
must be done in specific orders so that the data relationship can be
maintained. The "trick" is to append basic data to the table initially,
which must include a field whose values in the records are the same in the
new table as in the old table (or you can add a new field to the original
table, such as an autonumber, let it "populate", and then use that as the
"same" field to the new table). Bottom line, there must be a way to uniquely
identify an old record to be the same record in the new table.
Then you can build append queries that use that field as the join between
the old and new tables so that data are related as they were before.
Note that an append query will insert your numbers into an autonumber field,
overriding the "normal" incrementing behavior of the autonumber field when
one record at a time is added otherwise.
One should never move data from one structure to another without
(1) making a backup copy (no, make that two backup copies) of the
original data in its original structure;
(2) planning on paper how to do the data transfer ("I need an append
query to put data in the main table, so that I then can use an append query
to copy the primary key field's values into the child tables' foreign key
fields, and I need this table first and that table second, etc.") to ensure
that data values will be where you want them to be when you need them; and
(3) testing your plan on a copy of the database, testing as you go that
the data are properly stored and related.
Then, you can do the data transfer for real.
