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.

On Jan 13, 8:15*am, PVANS <PV...@discussions.microsoft.com> 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.