Validation Rules to prevent non primary key fields from duplicat

[gdev]

Hi Everyone,

I have these 3 fields in a table

planinvestid - pk
planid
investid

Records can have the same planids
Records can have the same investids

Records cannot the same planid and investid combination
A record can have an unique planid and investid

How can I make sure that a new record will not have a duplicate planid and investid?

Is there a way to do this at the table validation level or will I have to programmatically prevent duplication?

Thanks
Gwen

 

[PHV]

Create an unique index on planid and investid

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

...and this is how...

Open the table in design view. Then, from the menu, select "View" -> "Indexes"

The Index window has three columns - Index Name, Field Name and Sort Order.

You want something like this...
[tt]
Index Name Field Name Sort Order
PlanInvestKey PlanID Ascending
InvestID
[/tt]

The PlanInvestKey is name of the index. High-light it, and make sure in the Index properties, Unique is set to Yes, and Ignore Nulls is set to No.

Select the field names required for the index as shown.

The first time you do this type of thing, defining a multiple-key index, it may seem kind of weird, but basically you are assiging one index for the fields selected under the FeildName column.

Hint: The order you assin the fields is important. If you list the index as PlanID + InvetID, then queries using this order will have better performance.

Richard

 

[gdev]

Thank you, Thank you, Thank you.



Gwen