sql query updating problem
 

I'm creating a sales invoice order form and whenever a purchase is made, the quantity of a certain product ordered is deducted from the product database. how do i do that? I am using this sql query to get the product details input from vb to the database.

SELECT Invoice_Detail.[InvoiceNo], Invoice_Detail.Product_Code, Items.Description, Invoice_Detail.Qty, Invoice_Detail.Price, Invoice_Detail.Discount_Per, Items.[Sales_Price], ([Qty]*([Price]-[Price]*[DISCOUNT_PER]/100)) AS inv_amount
FROM Items INNER JOIN Invoice_detail1 AS invoice_detail ON Items.[Product_Code] = Invoice_detail.Product_Code;

is there a way to add an extra syntax in there to update the products table to reflect the new quantity? thanks!

Re: sql query updating problem
 

Well what I would suggest, to make things easier, SELECT the values from the DB and have them stored in a variable on the VB side, then do your calculations, and post the results back to the DB.

Or even better, create a Stored Procedure which you pass the variables into, and have the DB do the calculations and run the UPDATE sql statement to update the data.

Hope this helps..