Data Transfer

[randusoleis]

I have a table that contains data on multiple families and family members. They are all associated by a ID number. Everything is in the same table and I need to split the info into 2 tables. How can I do this. Randusoleis.....

 

[PaulaJ]

Create a 'Make Table' Query including all the fields in the table you want to duplicate. When you indicate that this is a 'Make Table' Query, you are asked what to name the new table. Indicate in criteria how to determine which row goes into the table and Run the Query. Then repeat with the opposite criteria for the second table.

 

[randusoleis]

I can already create new tables with an "SELECT....INTO" statement(ie: SELECT family_id, member_id, membertype, ssn, lname, mname, fname, dob, gender, hphone, wphone, mphone, race_1
INTO FAMILY
FROM f_family1)
What I really need to do is take this original table and place the data into 2 existing tables that already have an AUTONUMBER field. Randusoleis.....

 

[PaulaJ]

Is this a one time deal or are you setting up an on-going conversion situation? If you can already create the tables, what exactly is the issue?

 

[randusoleis]

The issue lies when I need to add more entries to the tables. Once I insert all that data, how can I create a new AUTONUMBER field so I can relate new data. Randusoleis.....

 

[PaulaJ]

Personally, I do everything I can to avoid the Autonumber because it creates all these issues. However, if you delete the autonumber field from the table, make sure the records are sorted in the order desired, or remove the sort indexes all together and add the autonumber back into the table. It will number ALL the records in the order the existing indexes indicate. I know it's a pain, but using Autonumber pretty much demands it. There is another thread that shows how to skip the Autonumber. (thread181-212005 Feb 14, 2002) Hope that helps.

 

[randusoleis]

I think that what I need to do is send some info to one of you and that way you can see what I mean.
If I split my table into 2 then how can I associate "new" records with each other? Randusoleis.....

 

[PaulaJ]

I would set up a field in the original table (not Autonumber) that will be the primary key. When creating the separated tables, be sure to include this field in both.
Is the table separation a one time thing and then the separated tables will be maintained or will you be copying from the original table each time you add records to the separated tables? It will make a big difference in how you proceed. If it is a one time conversion, then your on-going procedures may need to import the records to one of the tables, do an append of these 1st table records to the 2nd table making sure that the key field is updated in the 2nd table. This would associate your records. How many records are we talking 50 - 50,000?

 

[randusoleis]

Here is the kicker-in-the-asser.
I just realized that the table that I was given is set up just like a JUNCTION TABLE. I think that is the problem.
This table has no column for a distinct value.
What do you think? Randusoleis.....

 

[PaulaJ]

Can't help with that. "JUNCTION TABLE" doesn't mean anything to me. However, the only way to associate records in two tables is to have a matching field (or fields) in both tables that has matching data in it to function as a key link. ACCESS is real picky about what it will allow for a link.

 

[randusoleis]

A junction table is only a table to contain data for a "MANY-2-MANY" relationship.
Randusoleis.....