minusing stock!

shutzy

Registered User.
Local time
Today, 09:21
Joined
Sep 14, 2011
Messages
775
i want to be able to have a column that tells me what i have in stock. when an item is sold i want to deduct 1 from that ItemID column. im using an update query and dont worry i plan on having a reverse query if the item is deleted from the order.

for example. the item id is 123 and it has been added to an order

i thought that the criteria would be 123
the expression would be SUM([StockQTY]-1) but it doesnt like that.

the only fields i need are the ItemID and the StockQTY

thanks for any help
 
Use an UPDATE query to subtract one from the relevant Quantity field.

i.e.

UPDATE MyStockTable SET Quantity = (Quantity - 1) WHERE ItemID = itemID;
 
ive tried this. but it want to know the value of Quantity

can you help any further?

UPDATE tblItems SET tblItems.StockQTY = (Quantity-1)
WHERE (((tblItems.ItemsID)=123));
 
Last edited:
Based on the field names in your tblItems, I think you need to use the real field name.
I think Mile-0 was just giving a general approach, and you have taken his "quantity" too literally.

UPDATE tblItems SET tblItems.StockQTY = tblItems.StockQTY -1
WHERE (((tblItems.ItemsID)=123));
 
ah thanks. i thought that Quantity was part of an expression like SUM

thanks for your help
 
If you want a good stock control have a look at the attached.

It may be a bit complex but it is accurate.
 

Attachments

Users who are viewing this thread

Back
Top Bottom