I'm creating a database that will help users track supplies that they've ordered. Right now, they track the supplies they've ordered via a Purchases Table, and when supplies are received, they have to manually add those supplies to their "Inventory" table. Then, when they are expending items from their inventory, they have to go to remove the item from their inventory and log the expenditure in an Expenditure Table. All changes made to the tables are recorded via forms.
Ultimately, how I would like to have it is when a user sets the "status" field of a record in their Purchases Table to "received", a copy of certain fields from that record are appended to the Inventory Table. And then when a user sets an item's "status" field in their Inventory Table to "expended", a copy of the record would be moved to the Expenditure Table.
I'm not really well versed in relationships, but my assumption is that what I would like to do requires knowledge of them.
I'm just looking for a starting point here but any help is greatly appreciated.