database help

[Guest]

I am building a database for tracking meat inventories. When I make certain
product their will be residual meat left over to use next time that product
is produced. It will be stored until that time. I have build a table to
track what I have in inventory. What do I need to create in order to
subtract out of inventory when I use the product in inventory? Would this be
tracked on same table or would I create a new table and link? Please help!!

 

[Bill Edwards]

I would probably do something like the following
tblInventory
InventoryKey Autonumber Primary key
MeatDescription Text


tblChanges
ChangeKey Autonumber Primary key
ChangeDate Date
InventoryKey Links back to tblInventory
AmountAdded Amount of meat added to inventory
AmountRemoved Amount of meat removed from inventory

or AmountAdded/AmountRemoved could just be a single field as below:

Amount A positive or negative number indicating the amount of meat
removed from inventory (negative number) or the amount of meat added to
inventory (positive)

Total amount of meat in inventory at any point in time would be
SELECT tblINventory.MeatDescription, sum(AmountAdded - AmountRemoved) AS
AmountInInventory
FROM tblInventory JOIN tblChange ON tblInventory.InventoryKey =
tblChanges.InventoryKey
GROUP BY tblInventory.InventoryKey

Or

SELECT tblInventory.MeatDescription, sum(Amount)
FROM tblInventory JOIN tblChange ON tblInventory.InventoryKey =
tblChanges.InventoryKey
GROUP BY tblInventory.InventoryKey

 

[Guest]

my database is a little more complicated here is my main table
tblrework
id autonumber
date date/time todays date
item text Poduct Code
lot text Date Poduct was made
ref# text
cases number Number of case boxes being
Reworked
weight number Pounds being Reworked
code text Reason Code for Rework
location text Freezer Location

also have a
Customer tbl
product that can mix tbl
rework codes tbl

I have built relationships with a table listing rework codes to rework table
through a query.....it would be better if I could send you by database..would
that be possible???