Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Kent, England
    Thanked 0 Times in 0 Posts

    Update Queries (2000)

    Having just been on an 'advanced course' & not learnt much, a colleague pointed me in this direction.
    i wish to do multiple updates in one quick way rather than doing lots of updates queries.
    e.g. i have a table with 2 fields - YEAR & AMOUNT. For a particular year i wish to change the amount by 20%, for another year i wish to change the amount by 15% and so on. I just want to avoid creating repetitive queries.
    Is VBA / SQL the answer?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Sint Niklaas, Belgium
    Thanked 0 Times in 0 Posts

    Re: Update Queries (2000)

    You could write a function like this one :
    <pre>Function NewAmount(intYear As Integer, OldAmount As Single) As Single
    Select Case intYear
    Case 1998
    NewAmount = OldAmount * 1.2
    Case 1999
    NewAmount = OldAmount * 1.3
    Case 2000
    NewAmount = OldAmount * 1.4
    Case Else
    NewAmount = OldAmount
    End Select
    End Function

    Then do an update query where you set the new value to NewAmount([Year],[Amount])

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Update Queries (2000)

    Do you want to reuse the same query or do it all at once? If you just want to reuse the same query, you can use a parameter to specify the year to update and another parameter to specify the amount to apply.

    Your expression in the UpdateTo line for the amount field would be something like: [Amount]*[Enter percent as decimal]. You could use a function or IIf to test the input value to see if it was >1 to handle numbers entered as 15 rather than 0.15, but that would depend on whether you were the only one using the query.

    If you want to do it all at once, you probably would be better off using code to loop through a set of parameters and make the changes sequentially. Otherwise, you're going to need some pretty complicated IIf expressions to match up years and percentages.

Posting Permissions

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