Multiple primary keys 1

[only412c]

I am trying to set up multiple primary keys to uniquely indentify record with three fields in the paymentlink table.

Key fields for the paymentlink table are
Contract
member
receipt date

The enduser will interface with this table using a form to enter values in these fields. The Contract field will automatically populate in the form once user selects it through a combobox. The overall objective is to associate contract and receipt date with member and to allow the user to edit and delete records from the form. Once and edit or delete is performed the other tables will automatically be updated.


All field can have duplicate values but once all put together there would be only one unique record. These fields are also replicated in two other tables-Paymentamount and Settlement, respectively . However, I want to make the paymentlink table the controlling table and establish one-to-many relationship between this table and the paymentamount and settlement table using these three field. Is this possible to do? Any help would be greatly appreciated.

 

[danvlas]

You can create a multifield primary key easily...just go to Indexes , create an index on all three fields, then set it as Primary Key.

However, if you want it to be the 'parent table' with a One-To-Many relationship with another table based on the primary key, you will have to include the same fields in the 'child' table as foreign key, then establish the relationship on all three fields...Waste of space and speed...

If I were you, I would create an extra index (unique, Do Not Ignore Nulls) on just one field, having no meaning to the user, which can then be used for a one to many relationship. The thing is that you will have to manage that field transparently so that user is not confused...

This field could be an AutoNumber to assign unique values automatically (acceptable for single-user applications) or something you calculate through code (better for multi-user environment).

HTH
Daniel Vlas
Systems Consultant

 

[only412c]

Thank you for taking time to respond to my request. I think establishing an Autonumber for the primary key is the best course of action.

Sincerely,

Juan Lopez