I am trying to format a report that will show beginning inventory and determine ending inventory based upon transactions. This seems to be a simple matter. However, I keep coming up with the wrong answer.
There are four tables (keys denoted by *)
Product: *ProductCode / Description
Warehouse: *WarehouseCode / Description
ProductWarehouse: *ProductCode / *WarehouseCode / Beginning Inventory
Transactions: *ProductCode / *WarehouseCode / *TransactionID / TransactionDate/ TransactionAmount
Report is formatted in the following manner
WarehouseCode WarehouseDescription Beginning Inventory: 2,500 Ending Inventory 1900 (BeginInv - Sum(TransactionAmt))
11/15/2006 100 (This works)
The problem occurs here. If I have more than one warehouse within a specific product code, I cannot sum the Beginning Inventory and subtract the sum of the transaction amounts (=sum(BeginInventory) - sum(TransactionAmount). In the case listed above there are three detail records. Access adds 2500 for each detail record. Thus my equation becomes 7,500 - 600, not 2,500 - 600. It is adding 2,500 for each record. I hope I have made this clear enough for someone to assist.