Help with this using an Update Query!

[netbytes]

Hi!

I just want to know how to do this using an Update Query.

I have two tables:

QM table
Tele Table

I have four fields:

price , vendorprice , listprice, productprice.

QM table:
- vendorprice , listprice , productprice

Tele table:
- price

So I need INNER JOIN Here right? to connect QM and Tele tables.

Anyway,

I want to know how to update the listprice and the productprice using the difference of the price and the vendorprice.
I mean update both listprice and productprice using the difference of price and the vendorprice.

Example:

Before updating ...
price vendorprice listprice productprice
20.00 15.00 18.00 17.00

After updating ...
price vendorprice listprice productprice
20.00 15.00 23.00 22.00

price - vendorprice = A
listprice + A = listprice
productprice + A = productprice

20.00 - 15.00 = 5.00
18.00 + 5.00 = 23.00
17.00 + 5.00 = 22.00

that's the example...

Hope someone can answer my question.
Thank you and have a great day!

 

[stopher]

Hi
Yes you need a join query (that is also an update query). But you will need a field that is the same in both tables e.g. ProductID. Then you create the join query where these are equal.

Your update query will look like this:

UPDATE QM INNER JOIN Tele ON QM.ProductID = Tele.ProductID 
SET ListPrice = [ListPrice]+[Price]-[VendorPrice], ProductPrice = [ProductPrice]+[Price]-[VendorPrice]

Note that this update needs to be used with caution. If you run it once it will increment your values but if you run it again it will increment them again.

hth
Chris

 

[netbytes]

Thank you!

Hi
Yes you need a join query (that is also an update query). But you will need a field that is the same in both tables e.g. ProductID. Then you create the join query where these are equal.

Your update query will look like this:

UPDATE QM INNER JOIN Tele ON QM.ProductID = Tele.ProductID 
SET ListPrice =[ListPrice]+[Price]-[VendorPrice], ProductPrice = [ProductPrice]+[Price]-[VendorPrice]

Note that this update needs to be used with caution. If you run it once it will increment your values but if you run it again it will increment them again.

hth
Chris

hi,

thx for the codes.
btw any codes that will not increment the values? if i clicked more than once?

thank you again..
have a great day!
Added Reputation for you!