I work for a manufacturing company which sells machines to customers which have a control unit installed in them. We need to keep track of the locations of all the Machines after they are sold to the Customers. We need to keep track of all the Control Units whether they are assigned to the Machines, they are sent to Customers as spares, they have being returned back to the Supplier for repair or in stock. I am trying to setup tables to record of the history of the Locations of Machines which can be assigned to a Customer but then the same machine can be moved/reassigned to another Customer. The Machine has a Serial Number. Also need to record the history of the Locations of a Control Unit which can be assigned to a Machine which can then be moved/reassigned to another Machine. A Control Unit can also be sent to a Customer as a backup unit in case of failure or it can be sent to the manufacturer for repair. After the Control Unit comes back from the Manufacturers it is put back in stock and at some stage it can be reassigned to another Machine. The Control Unit also has a Serial Number.
I have attached an ER Diagram of what I think might be a suitable solution. The ERD diagram shows the link to MachineID, CustomerID and ManufacturerID in the TBL_ControlUnitLocations table. The problem I see is that when you select which link you want, the other two will need to be made NULL. When you view the history on a particular Control Unit you will end up with two fields always being blank.
One option is not to show the MachineID, CustomerID and ManufacturerID fields on the main form and only show the Location field. Is it possible to have another form which pops up and gives you the option to choice which one of the MachineID, CustomerID and ManufacturerID fields you wish to link to and then assign the link to the Location field and then close the popup form. So all you see then is the Location field.