Update Query define unique record for updated table

[patriciashoe]

I have a table that has as its primary key an autonumber field. Can I create an update query that defines what would constitute a unique record? For example, the buiildingID field combined with the room field would constitute a unique record. When there are changes in these records I want to be able to update them and this combo uniquely identifies the record. Thanks,

Patti

[Stewart Ross]

I have a table that has as its primary key an autonumber field. Can I create an update query that defines what would constitute a unique record? For example, the buiildingID field combined with the room field would constitute a unique record. When there are changes in these records I want to be able to update them and this combo uniquely identifies the record. Thanks,

Patti

Hi Patti. You already have a unique key - the autonumber field - which is certain to have been associated with other tables in one-to-many or many-to-one relationships. Redefining the relationships to use a new primary key is possible but is very involved, as it requires changes to all dependent queries, forms, subforms and reports. I would not recommend you make such a change on a working database.

Long and the short of this one is that it was at the design stage that the compound key could have been identified and applied. I would leave your keys and relationships as they are, but recognise for the future that the true candidate key for a table is often not an autonumber, quick and easy though this choice may be at the time!

Having said all that, you don't need to do any updates at all to use the compound key of building ID and room number as an alternate key for your room table for testing purposes. If there are indexes defined on these two fields it is already possible to join other tables to the two fields in subsequent table relationships, but as it is certain that the autonumber field will have been used for this purpose already I would leave it be at present.

-Stewart

[patriciashoe]

Thanks much. i will get to work and see what I can do.

Patti