Update Qry always updates to Zero

[sevenair]

Hey,
im trrying to build a stock control system for a small store, and im having a nightmare trying to gert the updatestock query to work. It is an update query, and it does update this, the only thing is, no matter how many i sell on a day, it always sets the InStock to Zero.

Basically, I have a Sale table, which records the ProductID, and Quantity. I also have a Stock table, with an InStock value. I want to take a SumOfSale and then a this to he InStock value in the Stock table.

I couldnt make a query to automatically update stock as I make a sale, so i thought I could just take a tally of how many i sell a day (sumofsalequantity), and then deduct this from the InStock at the end of the day, but this isnt working Sad

Does anyone know why it would reset the InStock to zero, regarless of how many is in stock?? r can you avise me on where to get help, ive tried everything, been reading all the manuals, and im at my witts end now

Any advice would e much much appreciated Smile

 

[FoFa]

Assumption:
Sales Table:
Date
Qty Sold
Stock Number

Stock Table:
Stock Number
Qty on hand

UPDATE STOCK TABLE
SET Qty on hand = Qty on hand - Qty sold
inner join Sales Table on Sales Table.Stock Number = Stock Table.Stock number Where Date = xx/xx/xxxx

That is not sytatically correct, it is to give you an idea of what you should be looking for.

You could sum the qty sold by stock number, but then you can't use it in a update query. So this is a better way for small amounts of data which it sounds like you have.