Inventory on Hand Query

[Guest]

I am tracking lab reagents made and sent. Since different types of reagents
require diff types of information about them to be stored, each type of
reagent will have its own "production table". In the production tables there
will be a "quantity made field". Every entry and all its field data will be
kept on record indefinately, i.e. no replenishment of stock as in a typical
business setting.

I then have a table of "Items Sent" which will record what reagents and how
many were sent to what labs. These entries and all their data will be kept
on record.

In an inventory table I will have a storage location field and a
lossed/tossed field. This table will have records, whose inventory
calculated to zero, regularly deleted.

All tables/records will be linked through a unique batch/lot number assigned
with every new batch of reagent made.

My idea is to have a query that will return inventory on hand:
Items made(production tables) - Items used(Sent table) - Items
lossed/tossed(Inventory table) = Items on hand.

I need confirmation that this will be an updatable query? Also, I know how
to do this if all of the operands/fields in the calculations were from the
same table, but how do I do this when I am drawing on data from different
tables, more than one production table, one "items sent" table and an
inventory table?

Thanks you all for your help,
Access Greenhorn

 

[Guest]

To add to the above...

I'm thinking a solution may be to have a two inventory tables. One that
stores the location of the items (PK batchnumber, entries often deleted) and
the other would store how many items were made, sent out, and lossed/tossed
(PK batchnumber, linked to production tables and other inventory table). This
table would be kept for record and reporting purposes and would be the basis
of a simple "inventory on hand" query.

The only problem with this solution that I can see is that, unlike with my
previous proposal, this format has data a bit further from a spreadsheet
format; which normally is often preferable, but I work with Access neophytes
and I won't always be around to maintain the dB and help them retrieve data
in useful manners. I can easily envision them printing off tables and/or
copy and pasting data to connect and collect data manually.

Any advice?

Thanks,
Access Greenhorn