ACC1x: Changing the Relationship Between Two Existing Tables

• Microsoft Access versions 1.0, 1.1

SUMMARY

You can change an ID field from a Long Integer or other data type to a Counter data type in the table on the one-side of a one-to-many relationship and change the ID values in the corresponding records in the many-side table while still maintaining the links between the two tables.

MORE INFORMATION

The following example uses the sample database NWIND.MDB to show you how to change the ID values in the Customers table (the one-side table) and update the values in the Orders table (the many-side table) so that the records in the two tables are still linked:

1. Add the following fields to the Customers and Orders tables:

         Table: Customers
         ---------------------
         Field name: New ID
            Data Type: Counter
   
         Table: Orders
         ---------------------------
         Field name: New ID
            Data Type: Number
            Field Size: Long Integer
   
   

2. Create a new query. Add the Customers and Orders tables to the query. Make sure the tables are joined on Customer ID.

3. From the Query menu, choose Update.

4. Add the following field and properties to the query grid:

         Update Query: Query1
         ----------------------------------
         Field name: Orders.New ID
            Update To: [Customers].[New ID]
   
      When you type Orders.New ID into the Field name cell, the Orders
      table name disappears. From the View menu, choose Table Names to
      see all the table names displayed on the grid.
   
   

5. To run the query, click the Run button on the toolbar.

6. Close the query. You do not need to save it.

To remove the original relationship between tables:

1. Press F11 to give the Database window the focus. From the Edit menu, choose Relationships.

2. Select Customers for the Primary Table and Orders for the Related Table. Customer ID automatically appears in Select Matching Fields.

3. Choose the Delete button to remove the relationship. Then close the dialog box.

1. Open the Customers table in Design view.

2. Delete the Customer ID field.

3. Change the name of New ID to Customer ID and make it the primary key.

4. Close the table, and save the changes.

1. Open the Orders table in Design view.

2. Delete the Customer ID field.

3. Change the name of New ID to Customer ID.

4. Close the table, and save the changes.