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

 

[KenHigg]

I think you'll find that the consensus at this forum is to not store the value at all, but rather do totals every time you need to know the value. So an explaination as to a fix for the way you're trying do it would get heckled and scorned

Ken

 

[Jon K]

Take a look at this thread. It illustrates how you can use a function to find the current stock (and the stock at a particular date) of a ProductID.
http://www.access-programmers.co.uk/forums/showthread.php?t=82677


New link to Allen Browne's page:
http://allenbrowne.com/AppInventory.html
.

 

[sevenair]

hey, thanks very much il give it a try