No duplicates on a one to many table

[chinkygogo]

Hi

I have a table full or resources that can be categorised as many different types of categories. i.e. tblResources has a one to many relationship with tblCategory. if i could categorise a resourse as either a 'Listening' resource or a 'Reading' resource this relationship would capture it. however, i don't want the user to categorise a resource as listening twice! please see below for example:

ALLOWED

tblResources
resource_id | resource_name
1 'first resource'

tblCategories
id | resource_id | Category
1 1 Listening
2 1 Reading



NOT ALLOWED
tblResources
resource_id | resource_name
1 'first resource'

tblCategories
id | resource_id | Category
1 1 Listening
2 1 Reading
3 1 Reading


in the 'NOT ALLOWED' example you can see that 'first resource' is categorised as a Reading resource twice.

thanks

pete

 

[chinkygogo]

hi

i think that i have almost answered my own question. I have set both resource_id and category as a primary key. this disallows the duplication i am thinking of. however, the only way of exiting from the error is to re-categorise as a non-duplicate. does anyone know of a way of exiting but the error without exiting the entire database, whilst getting rid of the erroneous data.

thanks

pete

 

[boblarson]

hi

i think that i have almost answered my own question. I have set both resource_id and category as a primary key. this disallows the duplication i am thinking of. however, the only way of exiting from the error is to re-categorise as a non-duplicate. does anyone know of a way of exiting but the error without exiting the entire database, whilst getting rid of the erroneous data.

thanks

pete

1. I wouldn't use a composite key as it is difficult to match as foreign keys.

2. Just set up an autonumber as the primary key but set a MULTI-FIELD INDEX which will keep out the duplicate data. See here for how:
http://www.btabdevelopment.com/main...createamultifieldindex/tabid/140/Default.aspx

3. Use forms to validate your data (for input) and then you can use the BeforeUpdate event of the form to cancel if it will violate your no dups instead of getting to the table validation.

 

[chinkygogo]

thank you very much. i will give this a go in the next few hours and let you know how i get one.

pete

 

[chinkygogo]

Thank you! yes i indexed my fields and made them unique and then wrote validation in Form_BeforeUpdate using DLookUp and then set Cancel = True and called the Undo function so that erroneous duplicate could not be made.

thanks again

pete

 

[boblarson]

Glad to hear you got it going for you.