I am creating a database for inventory purposes. The units that I'm inventory belong to a hierarchy system, but the user may not always know it's level. Some units are installed in a higher level unit, others have lower units installed in them, while others are stand alone. For this reason, I only have one table and assume that all units are at the same hierarchy level. To distinguish whether a unit installed in another or has units installed in it, I use check boxes and store the ID of the higher or lower unit.
When I check the box that indicates a unit is installed in a higher unit, I would like to automatically have the higher unit indicate that it has a lower unit installed.
Lets say I have two records, Unit1 and Unit2, in the same table. I indicate Unit1 is installed into Unit2 by storing Unit2's ID as an entry in Unit1 and checking a checkbox.
I would like for Unit2 to automatically take Unit1's ID, store it, and check a checkbox.
I am able to do this with an UPDATE query but am unsure of whether this is the best way to do it. Below is the sql code that I am currently using.
DoCmd.RunSQL ("UPDATE tbl_newInventory" & _
" SET SAID1= " & A & ",chkSA1 = True" & _
" WHERE InventoryID= " & B & " AND SAID1 is Null")
Where A is the ID of Unit1 and B is the ID of Unit2
Is there a better way to do this? Is there also a better structure for the layout of the entire database? Thanks in advance for your help.