Hi,

The database I've inherited has a primary key autonumber set up which is randomly generating numbers. It also has another field which has yet to be used, of a clients reference number.

I thought it would make sense to use the reference number field as the primary key, as it seems redundant otherwise and it would give us another field to perform searches on (if we record a clients reference number on their paper file, then finding them on the database would be much quicker).

Because of the relationships in the database, I didn't want to remove the existing primary key as I'm afraid it would mess up the whole thing. I was wondering if there is a way of having the autonumber which is generated in the primary key to be automatically copied to the clients reference number field? This would leave the primary key intact but give us this extra level of information to search on.

Thanks.

Why copy the data? Just use the PK and ignore the reference number field.