You might use two tables instead of three. The Bought and Used tables could be a single table with separate fields for Bought and Used Quantities. This would allow you to set up a kind of "bookkeeping ledger" where "Bought" quantities are your debits and "Used" are your Credits. Calculation and Summary fields could then compute running balances to not only show quantities on hand, but how levels have changed over time (a useful way to see if re-order levels need adjusting).
Working from your existing setup, you can relate your tables by an ItemID field defined in all three tables but generated as a serial number in Status. In Status, define one record for each item in your inventory. In the other two fields, create a new record each time you record an update entering the amount sold or used for a given item each in different records in these tables.
Your relationships would look like this:
Bought::ItemID = Status::ItemID
Used::ItemID = Status::ItemID
In status, you can compute amount on hand with this expression in a calculation field: Sum (Bought::Qty) - Sum (Used::Qty)
An expression such as: If ( Sum (Bought::Qty) - Sum (Used::Qty) < ReorderPoint ; "Re-order this item" )
can be used to identify all products in Status that have a current amount on hand less than their specified re-order levels.
Hi there Phil,
Sorry for the late replay. I started working on the database again today and I still cant figure it out.
There is a record made for each new project with diffrent materials used, and I still cant get a sum for 1 field in all records.
Lets say my database looks a bit like this
Projectname, ID, Tape used, Filters used
X, 1, 5, 5
X, 2, 10, 10
And then another 2 fields Tape bought and Filters bought, lets say both 20
I need a sum field where it will do bought tape (20) - Sum of all tape used in all records.
Because if I use the calc Sum it will only sum up the tape used in that record.
Thanks in advance
You need more tables for this. In Manufacturing systems a Bill of Materials table (BOM) is used to list the materials ID's and Quantities required to build one item. Then a Production table records how many items were produced during each production run. A script can use the information in the BOM multiplied by the production numbers recorded in the production table to compute the amount of each material used and can then update an invenory log to document the decrease in materials and also the increase in product.
Projects::ID = BOM::ID
Projects::ID = Production::ID
Projects::ID = InventoryLog::ID
This is just the bare skeleton of the basic tables.