I am building a simple database to keep track of reagents made (many made-in
house), the kits they are assembled into and to whom the kits are sent.
Reagents will be given a batch code and there may be as many as one or as
many as 100 to a batch. So no reordering of stock; each new batch is given a
unique batch code, i.e. a new entry in a production table.

In addition to the tables 'Kits Sent' and 'Kit Contents' which are in a
one-to-many relationship linked by a Kit Barcode, each reagent or group of
reagents will need their own table to store information unique to that type
of reagent and with specific information about the batch.

The locations and inventory of the reagents should then be stored in a
separate table (called Inventory) or stored in the aforementioned tables? I
ask this with the following in mind:
When the technician who assembles the kits is filling out a kits form (based
on tables Kits Sent and Kit Contents), and adds a reagent to the kit I would
like Access to automatically subtract this quantity from the quantity on hand.

Ideally, the solution to my problem would leave me with a field in each of
the "reagent production tables" or the "single inventory table" showing the
quantity of a batch left on hand. This way the technician could filter
records from the table(s) with "Not 0" to find out what reagents are made and
how many there are at any given time.

What is the best way to do this?

Thanks,
help is GREATLY appreciated.