migrating data process with data type mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I'm pretty green when it comes to migrating data and action queries and
such.....

I'm trying to get some data out of an old db into a new one. The old tables
weren't normalized, though, so where my new table stores the fk from another
table, the old db stores a text entry (like someone's name) and doesn't have
the second table.

I'm thinking to get the data migrated, I need to rework the old db table
structure. Once it's normalized (like my new db), then maybe I could use
append queries to add the old records. I'm sure I'd have more questions
during the process, but is that the way you have to do it? If not, how do
you deal with a situation like that where the old records in a table
represent what amounts to a query in the new db?

Thanks for any assistance,
CW
 
One approach might be to create queries against the old data that parses it
into the (normalized) pieces you need for the new data tables. A potential
wrinkle is if, as in your example, you have a old list of values that may
have duplication (there could be more than one person with the same name -
john smith?!). In that case, you'll have to add some kind of identifier
(for temporary use, during the transfer) to the old data so you can match
John Smith#1's other data to the correct John Smith in the new data.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for the reply, Jeff,

Sounds like I'm heading in the right direction. I am concerned that the
amount of work is prohibitive in this particular case. Guess I'll work on it
and see how it goes...

CW
 
"amount of work" ... ?one-time, or recurring?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Jeff,

It would be a one time thing. They had an access solution prior to the one
I developed, but it was VERY limited and it was just flat out poorly designed
on all levels. It was almost like a collection of tables, each with it's own
form. They took the 'relational' out of the process altogether.

That's the biggest problem in migrating the data.

If there's one good thing, I think the whole process gave me a great example
of the 'data integrity' function of lookup tables and related tables. I've
located some names that almost surely all refer to the same person, but are
entered in about 10 or more different variations.....

Thanks again,
CW
 
Back
Top