Update Query

[kicken18]

i have an order form in which a turkey is picked from the order form by the weight (i.e a customer phones up and says i want a turkey of this weight and a turkey of that weight or somehting closer is picked) thats bit is done. I have ym turkey table with Id and weight in an order form and subform for an order. But once that order has been enterd in to to table then i dont want that turkey to be avalible anymore so 2 people cant have the same turkey.

I belive i need to use an update query to move that turkey data to another table or field so its still inteh DB for the order details but it cannot be picked again. My problem is i have no idea how to and googling it hasnt got me anywhere. I have seen the SQl to be

UPDATE [TableName]
SET [FieldName] = newValue
WHERE [CriteriaField] = criteriaValue;

but i have no idear what to put in even in the query GUI i dont know what to add where. If someone can point me i nthe correct direction of what to do, that be great

Thanks
Chris

 

[manojab]

Have status field in that table call Sold Status & set it as a bit field
everytime an ordered is placed & a turkey is sold... run the update query which would say somethin like this

UPDATE tblNAME SET soldStatus = 1 where Id = "get the ID from the Order Form"


And when you retrieve data from the table to show all the available trukey..add a clause "where sold status = 0"

hope this helps

 

[kicken18]

ok yes that does make sense, and i did think of a simular sort of idea ealier but this seams easier. The problem i have is setting the ID to the order ID, since this could be anything from 1 to..99999 or something, it obvislly needs to be a varbiel, so do i just set it as

where ID = TBL_Order.OrderID

I use to program a lot in PHP and MySQL and going from one to another trips me up, i find hard coding easier to get my head around strangly lol

 

[kicken18]

yess it worked but now i have a small problem where i press the button (to urn the query) it brings up a box saying ID and then a prompt. If i type in the order ID then it does add a 1 in the correct turkey but i dont want to have to key in the order ID, i just want it to do it. Here is my code

UPDATE TBL_Turkeys INNER JOIN TBL_Order ON TBL_Turkeys.TurkeyNumber=TBL_Order.TurkeyWeight SET SoldStatus = 1
WHERE ID=TBL_Order.OrderID;

Thanks for letting me annoy you

Chris