Create Unique Product within a table based on mutliple inputs from other tables (1 Viewer)

[chrisjames25]

Hi.

I am trying to create a table that allows me to create a product description that is unique.

To elaborate i have three table - Tbl_Category, Tbl_Potsize and Tbl_CaneType. Within category i have things like fruit, clematis, fuchsia (types of plants basically). Within potsize i have the various pot sizes a plant can be grown (9cm, 2L , 3L) and within canetype i have bamboo 90cm, bamboo 60cm etc. The idea is that a product type would be made - for example I could have a 2L-Fruit_90cm_bamboo_cane or a 2LClematis_90cm_bamboo_cane.

What i want to ascertain is how do i create a table that will not allow me to create the same product type of three elements combined. SO could put in 2L fruit 90cm and 2l fruit 60cm cane but couldnt double put in 2L fruit 90cm twice.

Hope that makes sense.

 

[theDBguy]

Hi. Try to look up "multi-field unique index" or "multi-column unique index."

 

[Gasman]

I would have a table that holds the PK of each and make a unique index of all three keys.

 

[jdraw]

Here's a link to info on composite unique index.
Good luck.

 

[arnelgp]

you may also need to add validation before the record is saved.
if you are inputting direct to the table, use BeforeChange DataMacro.

if on form check if all fields are filled and then use DCount() to validate
if the new record has same "description" on your table.