Dynamic total ItemInStock

[oscarmolivera]

Hello, I trying to make an inventory Db. and I'm stuck in something that I belived is quite easy but I can't solve it.

How can I make a dynamic total?

Here's the example.

When someone sales an item, it has to shown the quantity of that item has been reduce. The query shoud show something like this:

IDItem	Description	SalesDate	IDSalesman	SaleQty	ItemInStock
256	DVD plug	12/09/2010	3	1	45
256	DVD plug	15/09/2010	3	1	44
256	DVD plug	21/09/2010	3	1	43
256	DVD plug	01/10/2010	3	3	40
256	DVD plug	05/10/2010	2	1	39
256	DVD plug	11/10/2010	2	1	38
256	DVD plug	19/10/2010	2	2	36

Other thing is that I really want to learn, so if any can point some good book for learning it will most apreciated (I've already finnish the video course from Lydia.com)

Thanks for the help.

[TheShabz]

the Qty on hand should be displayed in the master inventory table (the one where you have only on record per ItemID). everytime a sale is made, there should be a a bit of code that will subtract the qtySold from the qtyOnHand.

[oscarmolivera]

Thanks for your answer.
I really apriciated.
I think I've already did this, but it keep subtracting from the main total like this:

IDItem	Description	SalesDate	IDSalesman	SaleQty	ItemInStock
256	DVD plug	12/09/2010	3	1	45
256	DVD plug	15/09/2010	3	1	45
256	DVD plug	21/09/2010	3	1	45
256	DVD plug	01/10/2010	3	3	43
256	DVD plug	05/10/2010	2	1	45
256	DVD plug	11/10/2010	2	1	45
256	DVD plug	19/10/2010	2	2	44

please tell me what am I doing wrong, or how I have to re-think this

[TheShabz]

you shouldnt have each sale in the same table as your main item table. Read up on Normalization. It's really way too much for me to try to explain here. you need at least 3 tables to properly do what you want.

[oscarmolivera]

Maybe I didn't explain myself. Acctually all tables are "normalized", I've allready check that. what I'm showing here is the result of a query that has 3 related tables. But you right, maybe I shoud read a little more... I just thought that maybe it was so simple that it could be explained in a forum. I'm a novice on the access matter.
Again, thanks you time.

[oscarmolivera]

Here's the answer if someone is looking for it to

http://allenbrowne.com/AppInventory.html