Change the value stored for linked field

[S N]

In a master detail relationship, my detail table stores the actual,value of
the linked master field.
I want to replace this value with the autonumber index field value of the
master table so that the database size reduces and data integrity is also
maintained.
Kindly advise.

 

[Jeanette Cunningham]

Hi S N
to start-->
make a copy of both tables as safe keeping.
Add a new field to your detail table - make it a number type of long
integer.
Give it the appropriate name.

Create a query with both master and detail tables.
Drag the linked master field from the detail table to the master table so
they are linked on that field.
Drag the new field from the detail table onto the grid.
Change the query to an update query.
In the Update To row type
[NameOfMasterTable].[NameOfPKField] for that one field that is the new field
as explained above.

Note: replace the obvious with the real names of your table and field.
Run the query.
Close the query and open the detail table to check that the new field is
populated with the master table's pk values.
When you have checked it and it is OK, then you close the tables and go to
the relationships window.
Delete the existing relationship between those two tables and create a new
relationship using the master table's PK field and the new field in the
details table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia