merging tables to remove duplicate functions

[steven]

Please excuse my poor explanation in advance, I am a self taught Access user
with no programming background.

I am re-designing tables within my company's mailing list database.
Currently we have separate tables for tblDomestic_Contacts (with a one to
many relationship to Domestic_Notes), and tblInternational_Contacts (with a
one to many relationship to International_Notes). The Key field in each
table is via an autonumber field. There is no difference between
tblInternational_Contacts and tblDomestic_Contacts, except an additional
country field in the international table. Since this redundancy is poor
table design, I decided to combine the contact tables, and the notes tables.

But how can I do this? If I run an Append Query, there will be redundant
Primary Keys in the notes table (it looks like I got lucky with the Contact
databases, the primary key numbers are not redundant). Can someone please
advise?

 

[Steve Schapel]

Steven,

Preserving the value of the autonumber primary key fields in the Notes
tables will only be important if this field is the basis of any
relationships with other tables. Is this the case? If so, you will
need to update the foreign key field in the other tables to reflect the
new primary key values in the combined Notes table.

 

[steven]

Steve:

Thank you in advance. Yes, I do need to update the foreign key field values
in other tables that relate to the combined notes table. Please advise how
to do this.

 

[Steve Schapel]

Steven,

Step 1, as always, is to make sure you have a backup of your database!

Here's the principle... When you combine the two Notes tables into one,
append the existing Autonumber primary key values into a Number data
type field in the new combined table, in addition to having an
Autonumber field in this new table which will eventually be the primary
key. Also, add a new number field to the related tables. Then, make a
query which includes the new combined Notes table, and one of the tables
that was related to the original Notes table, joined on the number field
(FormerKey) in CombinedNotes to the foreign key field in the "other"
table. Make it an Update Query, and update the value of the new number
field in the "other" table to the value of the new autonumber field in
the combined notes table. Then, in the "other" table, you can delete
the old foreign key field, and rename the new number field to a suitable
name as this will now be the foreign key field. Hope that makes
sense... it's a lot easier to do than to explain!

 

[steven]

You are correct, it was hard to explain.. but rather east to do once I
traced down the foreign keys. Thanks for your help, 4 hours later I have two
tables instead of 6!!! No more redunancy, nice and normaled.

 

[Steve Schapel]

Lovely, Steven. Pleased to hear it's sorted now!