automatically creating a record based on a relationship as well as a checkbox field
I have three tables, one is an "Inventory" table, one is a "Removed Items" table, and the other is a "Item Use" table. Each has its own layout. Each item in the inventory has its on unique ID number, which is an auto enter serial number. The "Item Use" table, is used to document when items are taken from the inventory and being used, so we have an accurate depiction of the actual items in our system.
The following relationships have already been set up:
inventory::uniqueID = removeditems::uniqueID
This keeps track of an accurate number of units physically owned by the company
inventory::uniqueID = itemuse::uniqueID
This keeps track of items that were used, but not actually removed from the company
In the "item use" table, the user inputs the date, item unique id, and number of items used. There is then another field where they enter whether the item was physically removed from the system. It is a simple checkbox set with the values "yes" and "no". What I want, is that if the unique ID number matches one that is in the inventory table (there are some cases that it may not be), AND the checkbox indicates "Yes" they were removed from the system, a new record is automatically created in the "removed items" table, that shows this information.
Is this even possible?