If I have four tables:
Products
Purchases
Sales
Destruction
The three latter tables refer to products from the first table (often the same product many times), using the same field name "product", and each have a column which tells how much of the product is treated. These columns have a different name for each table, respectively: "delivered", "ordered", "destroyed"
What I want to do is to generate the stock levels from these tables. The idea is to take the number of purchased items, grouped by product, and then subtract the sold and destroyed items, each grouped by their own product fields. (I would also be interested in how to write a general function of the three values for each product.)
The end result is a table where each row uniquely represents a product and has a field which shows the current stock level. I believe this is very possible and a favorable solution to auto-editing a static table representing the inventory. Please correct me if I am wrong, because I am rather new to Access.
Thank you.