Calculation in a form Access 2003

[1943]

Hi,
Can anyone make the attached db work? I need it to clear the Units Received and UnitsSold after each transaction ready for new entry while
retaining Units On Hand for each record.


I would appreciate any HELP.

DWH

[alansidman]

Cross Post

http://forums.aspfree.com/microsoft-...tml#post862075

[jzwp11]

Actually since a product can be assoicated with many transactions, that describes a one to many relationship which would be handled with a separate table.


tblProducts
-pkProductID primary key, autonumber
-txtProductNo
-txtProductName

tblTransactionDetails
-pkTransDetailID primary key autonumber
-fkTransID foreign key to tblTransaction (the "header" info to the transaction)
-TransType (Sold or Received)
-fkProductID
-QTY

tblTransaction
-pkTransID primary key, autonumber
-fkCompanyID foreign key to the company table; company associated with the transaction
-dteTransaction (date of the transaction)


To find your current quantity, you would use a query to sum up all of the transactions for each product that are of the Received type and a similar query for the Sold type. You would then bring those together in a third query to find the quantity on hand.

There are some more intricate ways of handling inventory control. Allen Browne has another table structure and a function on his site that you may want to look at.