I have two tables, "Item" and "Set". The Set table has a unique "SetID" field and the Item table has a unique field "ItemID". I have also created a join table, "SetItem" which has "LinkSetID" and "LinkItemID" fields.
The join table is connected as follows:
Set::SetID >----- = -----< SetItem::LinkSetID as well as
Item::ItemID >----- = -----< SetItem::LinkItemID.
In the past, I have created join tables whereby the Set table can have multiple records with any combination of Items; I have controlled the Items that can be joined in the Set table via a "cItemID" calculated field whereby the calculation is: List (SetItem::LinkItemID) & "¶-.1 ". Then I also have a relationship to a copy of the Item table called "UnaffiliatedItem" ; with this relationship: UnaffiliatedItem::ItemID >------ ≠ --------| Set::cItemID. This allows me to put in a Set record any number of Items without repetition of the same Item to a Set and works fine.
Now I want to do something a bit different and cannot figure out how to establish the proper relationship (or is it in the "cItemID" field calculation?). I want to be able to link Items to Sets in a similar manner but want to limit the ability to link an Item only once to a Set and then no other Sets. Once an Item is linked, it can no longer be considered to be linked to any other Set. I am thinking a join table is still necessary, but how can I limit the Item selection so that an Item is linked to a Set and never given the opportunity to link to another Set?