Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 :

    SELECT NewPrices.Code, NewPrices.grade, NewPrices.oldprice, NewPrices.newprice,
    ([NewPrice]-[OldPrice])/[OldPrice] AS Increase
    FROM NewPrices
    WHERE (((([NewPrice]-[OldPrice])/[OldPrice]) Between 0.065 And 0.075))
    ORDER BY NewPrices.grade;

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #3
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Updating selected records (Access 2000)

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

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating selected records (Access 2000)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #7
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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


    What will be your advice?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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
  •