Setting Primary Key on a Calculated Field

[Tophan]

Is there anyway to set a calculated field as the primary key? Or can anyone suggest how to duplicate the value of a calculated field into a new field and set the new field as the primary key?

 

[pr2-eugin]

Calculated fields by itself is not a good option.. Why is this required?

 

[Tophan]

I have a table called Drawing Details_tbl. The primary key on this table is the drawing number. The drawings are issued by the architect and the drawing numbers are created by them and must be registered accordingly. Unfortunately many architects use similar drawing number sequences or in some cases repeat the drawing numbers when working on a new job. The only constant would be the Contract number. By creating a string argument and inserting a prefix I can create a unique field within the Drawing Detail_tbl which would look like this - [Contract No]&"-"&[Drawing No]. With this I can use one database to record information for several jobs; otherwise I would have to copy the database and clear the information from the tables each time a new contract is started.

 

[Pat Hartman]

Use an autonumber as the PK. Then create a unique index that includes the ContractNo and DrawingNo to ensure uniuqeness. Although it is possible to create a compound primary key, you will find it easier to have the PK be only a single field. The unique index will enforce the business rule while allowing you to keep a single field PK.

 

[Tophan]

I would really love to try the compound primary key. A ContractNo can never be duplicated and I think that using this field as a prefix for the drawing numbers (which are often duplicated) I can create a unique index.

The autonumber will work but as my database is right now in the very early stages I can make mistakes and start over if necessary.

Could you tell me how to create a compound primary key - I would really like to try.

Thanks for your help

 

[Pat Hartman]

You really don't want a compound PK. It will be impossible to use in a combo or listbox should you find you need that functionality. A compound unique index will solve the problem and still allow you to use an autonumber PK which works perfectly with combos and listboxes.

A PK or index may contain up to 10 columns (Jet/ACE limitation. SQL Server, et al allow more). Bring up the index box by pressing the key on the menu. The first "line" defines the index and gives it its name. Subsequent "lines" add additional columns.

 

[Tophan]

Tried the above method and it's not working how I want it to and causes me to re-enter information that the calculated field would have created.

I only want the calculated field to add a prefix using a string argument. The autonumber method is not working with this particular database and right now the solution left is to create a new database everytime I have a new contract rather than keep all the contracts in one database.

Thanks anyway

 

[Galaxiom]

Tried the above method and it's not working how I want it to and causes me to re-enter information that the calculated field would have created.

You are misunderstanding then.

The fields that comprise the compound primary key are the fields that are already in your table. There will be no need to re-enter their data.

Both fields also need to be in the related tables to form a compound foreign key.

Forget about the calculated field.