One Table Split to Multiple Tables (1 Viewer)

[Strike_Eagle]

I have an old database that I had to redesign. Unfortunately, it had multiple, unlinked tables with duplicate fields in each of those old tables. They basically made it a database with diffeent reports and forms to enter and manipulate their data with.

I have created another database, splitting all of those tables and relating them correctly. Because of the size of some of those tables, I created One to One as well as One to Many tables and have them normalized through 5th.

Now comes the hard part....

I need help with porting the data, one table at a time, from the hold database into the multiple tables of the new database. How can I do this? Everything I have read about Insert and Append queries is for multiple tables into one table... how can I do the reverse of that?

I don't care if I use VBA, a macro or SQL, I just need to use something. Both of these databases are Access 2007 on an XP machine. Afterwards I will be splitting backend from frontend and inserting new data from an Excel file.

Thanks everyone!

 

[Privateer]

Strike Eagle:
The easy answer is to do an update/append query from the source table into the target table with just those fields for that table, then change the target table and update/append the other fields to that table. Can't figure out why you want to do this. Anyway, if you have a real need, the answer involves some code and three recordset and a for loop which allows you to divy up the data. It goes something like this:

Dim rstSource, rstTarget1, rstTarget2 As DAO.Recordset

Set dbs = CurrentDb
Set rstSource = dbs.OpenRecordset("tblSource", dbOpenSnapShot, dbOpenTable)
Set rstTarget1 = dbs.OpenRecordset("tblTable1", dbOpenDynamic, dbOpenTable)
Set rstTarget2 = dbs.OpenRecordset("tblTable2", dbOpenDynamic, dbOpenTable)

rstSource.MoveFirst
Do Until rstSource.EOF = True
rstTarget1.AddNew
rstTarget1!Field1 = rstSource!Field1
rstTarget1!Field2 = rstSource!Field2
rstTarget1.Update
rstTarget2.AddNew
rstTarget2!Field1 = rstSource!Field1
rstTarget2!Field2 = rstSource!Field2
rstTarget2.Update
rstSource.MoveNext
Loop
rstSource.Close
rstTarget1.Close
rstTarget2.Close

This will loop through all the records of the source table and place whatever value is in Field1 and Field2 into both tables. So you will have to add a row of code for each field in source to each field of target. One warning, the period is used between the name of a recordset and a command, the exclaimation point is used between the recordset and a field name. That is the basic idea, so good luck
Privateer

 

[Strike_Eagle]

Thank you! I will give it a shot, but not sure exactly when I will be able to get a crack at it. As soon as I am able to, I will update here with how it went. Thanks for the reply! I am looking forward to trying it out!

The reason, since you asked, that the first database had no relations and multiple duplicate fields and was locked, so I couldn't change anything. That employee has left, and while the data is still available, the ability to manipulate the tables is no longer available. I set up a much more workable database, and will hopefully be able to get it where the data can be used more efficiently and turn into actual information!

 

[Privateer]

Hey, thanks for the background. If you are moving this data from a locked database to a new, better normalized one, then I am guessing this is probably a one time activity. Creating a whole bunch of code will take more time than its worth. You might save some time by just using an append query. Just select the fields in the source table and do an append query to table one, then switch the target to table two and execute the query again. Should take seconds to do both as long as the field names are the same in both target tables.
Good Luck
Privateer