Calculating Inventory Quantities

[Peter Hobbs]

I am developing an Access 2000 application to track inventory for multiple
warehouses and their products. I need a bit of help with code to calculate
the current count of products by product per warehouse.

I have a transactions table with the following fields:

Trans ID (autonumber) - Record ID
TransDate (date/time) - Transaction Date
TransType (number) - Transaction Type ("supply
issued", "transfer")
ProductID (number) - Product ID
Quantity (number) - Amount of product
LocationID (number) - warehouse quantity when
TransType = "supply issued" (product is being ordered to a location)
SourceWarhouseID (number) - warehouse the quantity was taken
from if TransType = "transfer" (product is being moved from here)
DestinationWarehouseID (number) - warehouse the quantity was moved
to if TransType = "transfer" (product is being received here)

I need to add products that are received at warehouses from the supplier
(under "supply issued" TransType) and subtract product counts from warhouses
transfering to others under the "transfer" TransType) using the counts
quanties field.

I can enter initial transactions by product to establish the beginning
balance of products at first I guess. But using this design, how can I
track the movement of products (adding and subtracting) based on this tables
design? Should I have 3 quantity columns? - (i.e. Balance, Credit, Debit)


Any help is appreciated.