move data from a non-normalized table to normalized tables

[SandyR]

I have an Access database that I inherited. I have designed a normalized
data base using that data, and created the tables and relationships. I
don't know how to actually migrate the data from the old table to the new
ones. There are some fields in the old one that need to become repeated
records in the new one (eg. old fld1,fld2,fld3,fld4 need to become new
record1 fld1, fld2, record2 fld1, fld2). There are some single fields in
the new tables that need to be created based on the value of several fields
in the old table (eg. new flag1 is true if the value of old fld1 is null and
the value of old fld2 is non-null, with old.fld1 not existing in the new
database), and many of the field names are changed.

What would be the best way to achieve this? Can I use queries? (if so,
could someone show me an example of what such a query might look like? Or
should I run reports on the old table that create ascii files that I import
into the new ones? Is there some other way that I haven't figured out yet?
(Obviously I am fairly new to Access).

 

[Smartin]

I have an Access database that I inherited. I have designed a normalized
data base using that data, and created the tables and relationships. I
don't know how to actually migrate the data from the old table to the new
ones. There are some fields in the old one that need to become repeated
records in the new one (eg. old fld1,fld2,fld3,fld4 need to become new
record1 fld1, fld2, record2 fld1, fld2). There are some single fields in
the new tables that need to be created based on the value of several fields
in the old table (eg. new flag1 is true if the value of old fld1 is null and
the value of old fld2 is non-null, with old.fld1 not existing in the new
database), and many of the field names are changed.

What would be the best way to achieve this? Can I use queries? (if so,
could someone show me an example of what such a query might look like? Or
should I run reports on the old table that create ascii files that I import
into the new ones? Is there some other way that I haven't figured out yet?
(Obviously I am fairly new to Access).

Hi SandyR,

Bravo for your effort!

There certainly is no "one size fits all" solution to your question.
Since we can't see your data, we would be hard pressed to give a
pertinent example.

Generally speaking, /if/ you have thought out the normalized design very
well, and /IF/ (a bigger if) the source data can be decomposed using
consistent logic, then yes, you should be able to use queries.

I would start with a (normalized) table at the top of a hierarchy. I.e.,
one that does not require related records in another table. Write a
SELECT query that obtains the data required for this table and check the
results. When you have this working, morph this to an INSERT query to
populate your new table. Rinse and repeat, tracking from the top down,
until all your new tables are filled.

The devil is in the details though, and you are the only one among us
who knows what the results should look like.

Best of luck, and let us know if you hit specific road blocks.