# Thread: Updating selected records (Access 2000)

1. ## Updating selected records (Access 2000)

I must increase our price list first with 4 percent for the bulk of the products, and for some products with 7 percent.
At first i have increased all the products with 4 percent:

CurrentDb.Execute "UPDATE Products SET GrossPrice = GrossPrice * 1.04"
Then with the help of Hans, the Moderator of the present Forum i have built the query for the products with a price increase of 7 pct :

([NewPrice]-[OldPrice])/[OldPrice] AS Increase
FROM NewPrices
WHERE (((([NewPrice]-[OldPrice])/[OldPrice]) Between 0.065 And 0.075))

I have called this query qryPreise7pct.

My question is, can i use this query to update my table products? To be more exact, to increase the grossprice of these products with further 3 percent?
The "products" table has a productid as an autonumber, and contains the field code.

2. ## Re: Updating selected records (Access 2000)

Adding 4% to the GrossPrice and then adding 3% to the new GrossPrice is not the same as adding 7% to the original GrossPrice, for 1.04 * 1.03 = 1.0712. In other words, the combined increase is 7.12%, not 7%. If you want to end up with a 7% increase, you must multiply by 1.07/1.04.

You want to increase GrossPrice in the Products table. I don't understand what this has to do with NewPrices.

3. ## Re: Updating selected records (Access 2000)

Thank you very much indeed.I was aware for the difference but i didnt think of this easy solution you have offered to me.It is marvelous.I will
apply it no in my calculations.Thanks once again.
About the NewPrices.these are the prices at which we get the goods in our stores.The new prices and the grossprice are actually independent.
But of course if the NewPrices are increased, we must also increase the grosprices.In other words, i want to increase only those prices, for which
the NewPrices have an increase with 7 percent.I have increased already all the grossprices with 4 percent, and now i have to increase with appr 3 percent
those products for which the NewPrices have an increase with 7 percent.I have a query built with your help for these products, could i use this query
to update the products contained in it? I am afraid updating with a query isnt possible

4. ## Re: Updating selected records (Access 2000)

Is there a field that links the Products table with the NewPrices table?

5. ## Re: Updating selected records (Access 2000)

Yes, the field is called code and it is number but not Autonumber and has no key.

6. ## Re: Updating selected records (Access 2000)

Try a query with this SQL:

UPDATE Products INNER JOIN NewPrices ON Products.Code = NewPrices.Code SET Products.GrossPrice = [Products].[GrossPrice]*1.07/1.04
WHERE ([NewPrice]-[OldPrice])/[OldPrice] Between 0.065 And 0.075;

7. ## Re: Updating selected records (Access 2000)

Dear Hans,

To my regret i cannot handle this case.The query is not showing the results and this is because
i couldnt explain my tables in full.
Since the code numbers are only about 20, i would like to try to build a simple update query.
The query i have in mind is :

CurrentDb.Execute "UPDATE Products SET GrossPrice = GrossPrice * 1.07/1.04 WHERE products.code = 16748 "
whereby i must insert the other code numbers with Or.

i wonder how can i insert the remaining codes more easily.Inserting them one after another
with Or is rather tedious.They are in fact as follows,i copied the numbers from the query:
Codes
16748
18813
15478
15479
15470
15474
14379
14370
13626
13616
13540
13537
10189
10142
10200
10329
10320
10039
10034
10031

8. ## Re: Updating selected records (Access 2000)

You can use

CurrentDb.Execute "UPDATE ... WHERE products.code IN (16748, 18813, 15478, ..., 10031)"

Of course, you must write out the list of numbers.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•