ACC1x: Changing the Relationship Between Two Existing Tables

ID: Q95058

The information in this article applies to:

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:

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

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

From the Query menu, choose Update.

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.

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

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

If there is a relationship already defined for the two tables, you need to remove it. For example, the NWIND database has a defined relationship, so you need to remove it.

To remove the original relationship between tables:

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

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

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

Next, remove the original ID field and rename the new one in the Customers table:

Open the Customers table in Design view.

Delete the Customer ID field.

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

Close the table, and save the changes.

Then remove the original ID field and rename the new one in the Orders table:

Open the Orders table in Design view.

Delete the Customer ID field.

Change the name of New ID to Customer ID.

Close the table, and save the changes.

Now you can redefine the relationship between the tables. From the Edit menu, choose Relationships.

Keywords : kbusage RltOthr
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo