Assets Inventory Table vs. Assets Used Table; One or Two Tables?
Thanks ahead of time for any guidance I may receive to this question.
BACKGROUND: I have an inventory DB with a table of consumable commodities on-hand. It contains nearly 1,000 records, some solitary and unique, others multiples of the same item.
DESIRED OUTCOME: I want to start tracking events when the commodities are taken from storage and used up. In my mind I would create a separate "consumables used" table and completely move a record from the "on-hand" table to the "used" table when that particular item is used, thereby reducing the number of items remaining in the "on-hand" table and increasing the number of items in the used table.
PREVIOUS RESEARCH ON THE MATTER: I am aware that I could use a value-listed drop-down field to discriminate between "on-hand" and "used" and leaving all records in the same single table, but is that the best way to handle the problem?
I have read several posts here and other places and see that an Import Records command is potentially useful in this regard. I also see that a script using multiple variables (1 per field to be moved from table 1 to table 2) is also possible but less elegant, perhaps.
1. Should I employ 2 related tables to do the job? (Or, would a Join Table be necessary, as well, making it three?)
2. If two tables (or three), what's the best way to physically move an entire record, basically, from one table to another?