Updating related tables.

[MattCollins]

Ok due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.

The table structure we have at the moment is with the related field in []:

Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.

There is also another Temperaturetbl linked to Systemtbl by [sysID]

The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)

Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.

I've tried using append queries but this produces the same affect as above.

Many thanks

Matt Collins

 

[neileg]

If your autonumber is set to increment (ie not set to random) there is a relatively simple way to fix this.

Decide which database is going to be unchanged (recipient) and which is going to be added (donor). Find the largest ID used in the recipient. Go into the donor and create a query for each table. Include all the fields except the ID field(s). Create a calculated field that takes the ID value in the donor table and adds the largest value you found in the recipient. Then you can append the data in the query to the recipient tables.

You may have to do this table by table, but it will still be quicker than doing it manually!