Copying data from one table into another

[GD]

Hello. I've checked around about this subject but can't seem to find
the information that helps my case of the problem, so here it is.

The overall goal is to move all the data from an old Q&A database to
the new Access database that I created. I exported the Q&A data into
a Database IV file, which I then imported into new Access database
file so it could be easily managed. Surprisingly enough to me, Access
neatly sorted the data into a new table with the correct fields
despite the fact that Q&A is more of a text sheet editor than a
database.

The goal now is to put all the old data (right now in table
[tblOldParts]) into the same table as my database [tblParts]. The
difficult part is how they vary with fields.

In my database tblParts has a child table, tblDimensions. However, in
the tblOldParts, since Q&A couldn't really have relationships, instead
of having child records, it has the fields #1 through #15, and #1Desc
- #15 Desc.

My question comes to this: How can I move (or copy) the data from
tblOldParts to tblParts, and somehow use the fields #1 - #15 to add to
the child table? Worst case scenario is I do it all manually.

I can post more information if it will help...hopefully this explains
enough about my situation to gather an understanding.

 

[Jeff Boyce]

It sounds like you recognize that you'll need a one-to-many relationship to
get those Dimension data points related to the proper parts.

One approach would be to use a two-step conversion. The first step is
adding the Parts (and keeping track of which part is associated with which
{?NEW} PartID number) -- you may need to add a temporary field that will
store the OldPartID for each addition.

The second step is to build a query (actually 15 queries!) that joins the
new parts table to the old one on that OldPartID field, and returns the 1st
Dimension column. When this SELECT query is returning what you want, add in
the new PartID field, change the query to an Append query, and append to
your tblDimensions, using the new PartID and the 1st Dimension column. If
you need to know which Dimension column it came from, you'll need to have a
field in the new Dimension table to hold "1".

Repeat for the 2nd Dimension (with "2" in the extra field). Rinse and
repeat <g>!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GD]

It sounds like you recognize that you'll need a one-to-many relationship to
get those Dimension data points related to the proper parts.

One approach would be to use a two-step conversion. The first step is
adding the Parts (and keeping track of which part is associated with which
{?NEW} PartID number) -- you may need to add a temporary field that will
store the OldPartID for each addition.

The second step is to build a query (actually 15 queries!) that joins the
new parts table to the old one on that OldPartID field, and returns the 1st
Dimension column. When this SELECT query is returning what you want, add in
the new PartID field, change the query to an Append query, and append to
your tblDimensions, using the new PartID and the 1st Dimension column. If
you need to know which Dimension column it came from, you'll need to have a
field in the new Dimension table to hold "1".

Repeat for the 2nd Dimension (with "2" in the extra field). Rinse and
repeat <g>!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello. I've checked around about this subject but can't seem to find
the information that helps my case of the problem, so here it is.

The overall goal is to move all the data from an old Q&A database to
the new Access database that I created. I exported the Q&A data into
a Database IV file, which I then imported into new Access database
file so it could be easily managed. Surprisingly enough to me, Access
neatly sorted the data into a new table with the correct fields
despite the fact that Q&A is more of a text sheet editor than a
database.

The goal now is to put all the old data (right now in table
[tblOldParts]) into the same table as my database [tblParts]. The
difficult part is how they vary with fields.

In my database tblParts has a child table, tblDimensions. However, in
the tblOldParts, since Q&A couldn't really have relationships, instead
of having child records, it has the fields #1 through #15, and #1Desc
- #15 Desc.

My question comes to this: How can I move (or copy) the data from
tblOldParts to tblParts, and somehow use the fields #1 - #15 to add to
the child table? Worst case scenario is I do it all manually.

I can post more information if it will help...hopefully this explains
enough about my situation to gather an understanding.

Thanks, Jeff. Worked great!