Cascade Updates With Primary Keys

[veaux]

Help! I understand how primary keys work, but may need some creative
thinking to help with this. Can't be first time it was asked.

I have a streets table with a primary key of street ID. Table is like
below:

Street ID Street Address
ID1 6 Blank Way
ID2 9 Waters Way
ID3 6 Blan Way

The Primary key is linked to about 9 other tables in the relationships
window with cascade updates. Looking at the above, I've determined
that ID1 and ID3 are the same. So I want to reassign everyone that has
ID3 to ID1. Then I want to remove ID3 from the table since it's no
good.

I can't just change ID3 to ID1 since it's a master key. If I add an
autonumber field to this table and make the 2 of these fields a key,
that might allow me to change ID3 to ID1, but I wouldn't think it would
let me delete ID3 (now ID1) since that's linked to other tables.

I dont want to have to write 9 queries to tell all my tables to change
ID3 to ID1. I know I could do that, but the point of the cascade
updates is so I wont' have to do that. I don't really know VB.

Any help that can be provided is much appreciated!

[Tom van Stiphout]

On 16 Nov 2006 10:36:09 -0800, "ve***@aol.com" <ve***@aol.comwrote:

>Help! I understand how primary keys work, but may need some creative
thinking to help with this. Can't be first time it was asked.

I have a streets table with a primary key of street ID. Table is like
below:

Street ID Street Address
ID1 6 Blank Way
ID2 9 Waters Way
ID3 6 Blan Way

The Primary key is linked to about 9 other tables in the relationships
window with cascade updates. Looking at the above, I've determined
that ID1 and ID3 are the same. So I want to reassign everyone that has
ID3 to ID1. Then I want to remove ID3 from the table since it's no
good.

I can't just change ID3 to ID1 since it's a master key. If I add an
autonumber field to this table and make the 2 of these fields a key,
that might allow me to change ID3 to ID1, but I wouldn't think it would
let me delete ID3 (now ID1) since that's linked to other tables.

I dont want to have to write 9 queries to tell all my tables to change
ID3 to ID1. I know I could do that, but the point of the cascade
updates is so I wont' have to do that. I don't really know VB.

Any help that can be provided is much appreciated!

[Tom van Stiphout]

On Thu, 16 Nov 2006 12:52:07 -0700, Tom van Stiphout
<no*************@cox.netwrote:

(Sorry for previous post - I hit the wrong key)

The nine queries is your best idea. Of course you would make them
parameter queries so you can re-use them for any other pair of ID
values. Depending on your skills, you could even produce a "Database
Cleanup" form where the user can select the two ID values, click the
"Fix" button, and your queries do the work, looking back at this form
for their parameters.

If you want to automate this, learning VBA is the only way to go.

-Tom.

>On 16 Nov 2006 10:36:09 -0800, "ve***@aol.com" <ve***@aol.comwrote:

>>Help! I understand how primary keys work, but may need some creative
thinking to help with this. Can't be first time it was asked.

I have a streets table with a primary key of street ID. Table is like
below:

Street ID Street Address
ID1 6 Blank Way
ID2 9 Waters Way
ID3 6 Blan Way

The Primary key is linked to about 9 other tables in the relationships
window with cascade updates. Looking at the above, I've determined
that ID1 and ID3 are the same. So I want to reassign everyone that has
ID3 to ID1. Then I want to remove ID3 from the table since it's no
good.

I can't just change ID3 to ID1 since it's a master key. If I add an
autonumber field to this table and make the 2 of these fields a key,
that might allow me to change ID3 to ID1, but I wouldn't think it would
let me delete ID3 (now ID1) since that's linked to other tables.

I dont want to have to write 9 queries to tell all my tables to change
ID3 to ID1. I know I could do that, but the point of the cascade
updates is so I wont' have to do that. I don't really know VB.

Any help that can be provided is much appreciated!

[veaux]

I might have dumbed down my skills a little too much. I was hoping not
to have to do the 9 queries. Yes, I did do similar in past and have a
table with old to new ID. Can certainly fix them all with a macro.
It's just each time this comes up it has to be run, etc. If that's
definitely only way and no one can think of anything else, so be it I
guess. Thanks!
Tom van Stiphout wrote:

On Thu, 16 Nov 2006 12:52:07 -0700, Tom van Stiphout
<no*************@cox.netwrote:

(Sorry for previous post - I hit the wrong key)

The nine queries is your best idea. Of course you would make them
parameter queries so you can re-use them for any other pair of ID
values. Depending on your skills, you could even produce a "Database
Cleanup" form where the user can select the two ID values, click the
"Fix" button, and your queries do the work, looking back at this form
for their parameters.

If you want to automate this, learning VBA is the only way to go.

-Tom.

On 16 Nov 2006 10:36:09 -0800, "ve***@aol.com" <ve***@aol.comwrote:

>Help! I understand how primary keys work, but may need some creative
thinking to help with this. Can't be first time it was asked.

I have a streets table with a primary key of street ID. Table is like
below:

Street ID Street Address
ID1 6 Blank Way
ID2 9 Waters Way
ID3 6 Blan Way

The Primary key is linked to about 9 other tables in the relationships
window with cascade updates. Looking at the above, I've determined
that ID1 and ID3 are the same. So I want to reassign everyone that has
ID3 to ID1. Then I want to remove ID3 from the table since it's no
good.

I can't just change ID3 to ID1 since it's a master key. If I add an
autonumber field to this table and make the 2 of these fields a key,
that might allow me to change ID3 to ID1, but I wouldn't think it would
let me delete ID3 (now ID1) since that's linked to other tables.

I dont want to have to write 9 queries to tell all my tables to change
ID3 to ID1. I know I could do that, but the point of the cascade
updates is so I wont' have to do that. I don't really know VB.

Any help that can be provided is much appreciated!

[maxhugen]

I agree with Tom, you don't have any other options than run the 9
queries. My only suggestion would be to put these into a function, and
wrap the queries within a workspace Transaction, so that they all
update, or rollback on any failure.