How to track specific item as part of a kit Here is my situation. I have over 50 boxes each with a unique ID number. That number is tracked for inventory purposes. My problem is that inside each box there is one specific part that is tracked independently of the box. What I am trying to accomplish is to be able to track the serial number from the part and link it up to which box it goes in such that the following rules are followed:


Each box cannot contain more than one primary part
Not every box has a primary part (some were broken and never replaced)
If a primary part needs to be moved from one box to another the original box it was taken from shows up as having no primary part.


I thought this would be fairly easy to accomplish with MS Access since it is already deployed across the network, but I am having a hard time preventing multiple boxes from being assigned the same primary part. If I just put the part SN as a field in the same table as the box then I have to remember to change 2 things any time one of the parts moves.
Put the Box ID against the primary part, instead of the primary part serial number against the box. To see what's in the box you need to search the Primary Part table for the Box ID. If you move which box a part is in, you just update it once.

You could make a unique index on BoxID as well to ensure that there are never 2 primary parts in the same box.