If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Avoiding duplicate fields in a table
Good morning
Please can someone help me with this: I have a table tblCommission which has the following fields: Commission_ID (primary key) Client_account (related to the field Client_account in tblClients) Product_Code (related to the field Product_Code in tblProducts) Commission Exchange I need a method to ensure that each client can only have one commission per product.... all clients should (and do) have the ability to have a commission for all the possible products, but I need to protect my database from the scenario I have shown below: Commission_ID Client_account Product_code Commission Exchange 1 000001 Coke 2 $ 2 000001 Coke 1 $ Please could someone suggest a method to protect the table from this scenario occuring? Thank you for the help, I really appreciate it. |
#2
|
|||
|
|||
Avoiding duplicate fields in a table
On Jan 13, 8:15*am, PVANS wrote:
Good morning Please can someone help me with this: I have a table tblCommission which has the following fields: Commission_ID (primary key) Client_account (related to the field Client_account in tblClients) Product_Code (related to the field Product_Code in tblProducts) Commission Exchange I need a method to ensure that each client can only have one commission per product.... all clients should (and do) have the ability to have a commission for all the possible products, but I need to protect my database from the scenario I have shown below: Commission_ID * Client_account *Product_code * Commission * Exchange 1 * * * * * * * * * * * * * * * * 000001 * * * * * * *Coke * * * * * * * * 2 * * * * * * * * *$ 2 * * * * * * * * * * * * * * * * 000001 * * * * * * *Coke * * * * * * * * 1 * * * * * * * * *$ Please could someone suggest a method to protect the table from this scenario occuring? Thank you for the help, I really appreciate it. Add a unique index to (Client_account, Product_code) and you will not be able to enter duplicates *ever*, so if this is a "sometimes" rule instead of an "always" rule, you'll have to rethink your strategy. |
Thread Tools | |
Display Modes | |
|
|