Breaking up a table and creating relationships

[dhstein]

I have a situation where I have table "A" and table "X". Table A has a field
with a one-to-many relationship with a field in table "X" and referential
integrity is enforced. Because now I've realized that I need a better
design, I decided that I need to break table "A" into 2 tables "B" and "C"
"B" and "C" will be related by a new field and I want to relate "C" to "X" on
the same field as before. So far my initial attempts to do this have not
been successful. Can this be done without recreating the tables? What is
the best way to do this? Thanks for any help you can provide.

 

[John W. Vinson]

I have a situation where I have table "A" and table "X". Table A has a field
with a one-to-many relationship with a field in table "X" and referential
integrity is enforced. Because now I've realized that I need a better
design, I decided that I need to break table "A" into 2 tables "B" and "C"
"B" and "C" will be related by a new field and I want to relate "C" to "X" on
the same field as before. So far my initial attempts to do this have not
been successful. Can this be done without recreating the tables? What is
the best way to do this? Thanks for any help you can provide.

What problems are you having? Could you perhaps post a more realistic example?
Just HOW are you breaking them up?

 

[Paul Shapiro]

This is a bit of a guess since you didn't give us any details, but try
something along these lines.
0. Make a few good backups of your existing db.
1. Delete the existing relationship from A to X.
2. Create your new tables B and C.
3. Write two append queries to move your data from A to B and from A to C.
4. Create and enforce the relationship from C to X.
5. Once you're sure everything is good, delete the old table A.

 

[Paul Shapiro]

I forgot a couple of steps in the first post.

This is a bit of a guess since you didn't give us any details, but try
something along these lines.
0. Make a few good backups of your existing db.

0b. Since you already found one data structure modification, spend some time
reviewing the data model to see if you can find others. It's easier to do
them all at once.

1. Delete the existing relationship from A to X.
2. Create your new tables B and C.
3. Write two append queries to move your data from A to B and from A to C.
4. Create and enforce the relationship from C to X.

4b. Presumably there is a relationship between B and C, so create and
enforce that one too.

 

[hor vannara]

I have a situation where I have table "A" and table "X". Table A has a
field
with a one-to-many relationship with a field in table "X" and referential
integrity is enforced. Because now I've realized that I need a better
design, I decided that I need to break table "A" into 2 tables "B" and "C"
"B" and "C" will be related by a new field and I want to relate "C" to "X"
on
the same field as before. So far my initial attempts to do this have not
been successful. Can this be done without recreating the tables? What is
the best way to do this? Thanks for any help you can provide.