I have an Access 2010 db for monitoring data about equipment on engineering projects. There is a basic table containing the complete equipment number, name, and a couple of other items, and then multiple related tables with specifics about the equipment. The equipment number is comprised of 3 separate fields, Area, Type, and Sequence. Currently Sequence is the single unique identifier, and is used to relate the basic table to the specifics. After working with this db for a while we have decided we would like to use the combined Area and Sequence as the unique identifier. Area is a text field and Sequence is a number with 2 decimal places. The related tables don't currently have Area in them, only Sequence, but since Sequence is currently unique it won't be difficult to add Area to those other tables.
Is there a reasonable way to change to the compound unique identifier and if so how do I do it? Would it be better to simply create a compound index? How would I do that?
Any help will be greatly appreciated!
Alan