Moving Records to new DB

[Vaimpir]

I was giving a DB with 300+ records that are all in one table. I have created a more normalized DB and I want to move all these records to their respective tables in the new DB while keeping the relationships together for the information.

Can this be done, and if so can someone point me in the right direction of have to do so?

Thanks

Steven

 

[KenHigg]

Sure it can be done, and with only 300 records it should be easy by simply attaching to the old db table and use append queries to populate the new tables...

To offer any concrete advice, you would need to post the table structures...

kh

 

[Vaimpir]

The current DB has a table with such fields as:

Client First
Client Last
Client Mailing Address
Child First
Child Last
Child DOB.....

I want to move these records into a new DB with seperate Table such as:

Client Table
Client First
Client Last
Client Mailing Address

Child Table
Child First
Child Last
Child DOB....


These are just a few of the fields but it should present a good example that can get me going.

Thanks

Steven

 

[sfreeman@co.mer]

Like this?

If this were your main table:

tblMainTable
Name Color
Jones Blue
Smith Red
Williams Blue
Johnson Pink
Floyd Orange
Brownstone Blue
Pearce Red
Larson Black
Peterson Yellow


Add a new field to your main table, called something like ColorID

tblMainTable
Name Color ColorID
Jones Blue
Smith Red
Williams Blue
Johnson Pink
Floyd Orange
Brownstone Blue
Pearce Red
Larson Black
Peterson Yellow

This would be your new normalized table:

tblColor
ColorID Color
1 Blue
2 Red
3 Pink
4 Orange
5 Black
6 Yellow

Then create an update query:

UPDATE
tblMainTable

INNER JOIN
tblColor

ON
tblMainTable.Color = tblColor.Color

SET
tblMainTable.ColorID = [tblColor]![ColorID];

tblMainTable
Name Color ColorID
Jones Blue 1
Smith Red 2
Williams Blue 1
Johnson Pink 3
Floyd Orange 4
Brownstone Blue 1
Pearce Red 2
Larson Black 5
Peterson Yellow 6

Then after you have verified all of the relationships are intact, you can then delete the ‘Color’ field in the Main table.

Simple.

HTH

 

[KenHigg]

Steven, Two things right off the top. One, I would suggest your read up a little on naming conventions. There really is no need to include 'client' and 'child' as pseudo prefixes in your fld names. And you should omit the spaces as well ('client name' shouuld be 'clientName' if you do elect to use the prefixes).

Second, I see no evidence of primary keys and foriegn keys in either of your tables. I will yield on the explaination of these topics and suggest you search the net for info...

Sorry, I don't mean to be short, but covering this kind of stuff in depth in a forum such as this would be frustrating...

However, if you have specific questions - this is an excellent forum.

kh

 

[Vaimpir]

Sorry I forget to add the keys in my example. I have keys as such:

Client Table
ClientID (pk)

Child Table
ChildID (pk)
ClientID (fk)

I have the field names in one I just seperated them for explaination purposes.

Steven

 

[KenHigg]

Oh, my bad...

Well - Seems like series of append queries would work. Could you make this small enough to post?

kh

 

[Vaimpir]

Should I post the old DB or the new one?

 

[KenHigg]

Both?

kh

 

[Vaimpir]

OK i'll give it a shot.

 

[Vaimpir]

These are the two DB's. Please tell me what i can do.

Steven

 

[KenHigg]

Ok, First, I only think this will work so do it on back up copies...

1. In your new borrowerInfo table, create an 'oldPrimaryKey' fld.
2. Attach to the old table
3. Append old records to the borrowerInfo, matching up the relevant flds, putting the old primary key in the old table in the oldPrimaryKey you just created.
4. Do a new append query and bring in the old table and the new borrowerInfo table and link them on the old primary key fld
5. For each of the other tables append the associated flds in the old table with the flds in the new table, putting the newprimarykey fld in the borrowerID fld in the new table(s).

Make sense?

Oh - then I would set up the proper relationships for the new tables in the relationship window...

kh

 

[Vaimpir]

Yeah I think I get it. I will give it a shot and se what happens. Thanks for your help.

Steven

 

[Vaimpir]

One more Question for you. How can i seperate the mailing address field into the address, city, state, and zip fields?

Steven

 

[KenHigg]

Hum...

You might be able to use string functions like right(), left() and mid() to grab the zip and state but after that, I really don't know...

Sorry...

kh

 

[Vaimpir]

Thanks anyway. I will see if I can find it in the forum.

Steven