Update Query with percentage value

[Lochwood]

I have a rates table with different fields for standard, overtime, weekend etc..

We have an increase of say 2% on all rates across the board. how can i create an update query that can increase all Rates by 2%.

Any help is appreciated.

 

[Guus2005]

update tablename set rate = rate * 1.02

or is that too simple?

HTH

 

[arnelgp]

you can use Update query against the table:

Update table set [starndard] = [standard] * 1.02, [overtime] = [overtime] * 1.02, etc...

or, add New table for the increase rate, with effective date:

tblRateIncrease (table)
Date (Date/Time)
Rate (single)

Using query to show the increase (but not save to table):

select [overtime] + ([overtime] * (1 + dmax("Rate", "tblRateIncrease", "[Date]=" & dMax("[Date]", "tblRateIncrease")))) As OT, etc...

 

[Lochwood]

ideally i will have a text box on a form.. input percentage increase value, hit the button to run the query and update the data.

 

[arnelgp]

on the Click Event of the button:

Private Sub button_Click()
If Len(Trim(Me.txtRateIncrease & ""))>0 then
    With Currentdb.CreateQuerydef("", "Update table Set [standard]=[standard] * p0, " & _
                   "[overtime]=[overtime] * p1, [weekend] = [weekend] * p2;")
        .Parameters("p0") = 1 + CSng(Me.txtRateIncrease)
        .Parameters("p1") = 1 + CSng(Me.txtRateIncrease)
        .Parameters("p2") = 1 + CSng(Me.txtRateIncrease)
        .Execute
    End With
End Sub

 

[theDBguy]

ideally i will have a text box on a form.. input percentage increase value, hit the button to run the query and update the data.

Hmm, how often do you increase your rate? Just curious...

 

[Lochwood]

on the Click Event of the button:

Private Sub button_Click()
If Len(Trim(Me.txtRateIncrease & ""))>0 then
    With Currentdb.CreateQuerydef("", "Update table Set [standard]=[standard] * p0, " & _
                   "[overtime]=[overtime] * p1, [weekend] = [weekend] * p2;")
        .Parameters("p0") = 1 + CSng(Me.txtRateIncrease)
        .Parameters("p1") = 1 + CSng(Me.txtRateIncrease)
        .Parameters("p2") = 1 + CSng(Me.txtRateIncrease)
        .Execute
    End With
End Sub

where in this code does it reference the table to be updated..

 

[Lochwood]

Hmm, how often do you increase your rate? Just curious...

quite a lot.. lots of rates lots of jobs lots of stuff going on.

 

[theDBguy]

quite a lot.. lots of rates lots of jobs lots of stuff going on.

Interesting. Thanks!

 

[Pat Hartman]

It's not clear what your schema is. Typically rates are kept by date. They have an effective date and an expiration date. The active date would have null as the expiration date. When a new date goes into effect, you know about it ahead of time. You update the active date with an expiration date, then you insert a new date with an effective date of the expiration date +1.

Overwriting rates makes it impossible to do any historical reporting or analysis.

 

[Lochwood]

It's not clear what your schema is. Typically rates are kept by date. They have an effective date and an expiration date. The active date would have null as the expiration date. When a new date goes into effect, you know about it ahead of time. You update the active date with an expiration date, then you insert a new date with an effective date of the expiration date +1.

Overwriting rates makes it impossible to do any historical reporting or analysis.

Yes Yes Yes... I have a template table and an append query that populates a rates table with start and end dates.. all i need to do is add a percentage increase to the templates table.

 

[Pat Hartman]

What was wrong with the solution proposed by Guus2005? I think the update query needs criteria because you should not be updating historical records but aside from the Guus2005's solution is correct.

 

[Lochwood]

update tablename set rate = rate * 1.02

or is that too simple?

HTH

Not quite that simple... i have a text field on the form labelled " increase rates by %" how do i reference the query to this.. tried * 1.0 & Forms!aaaaa obviously the wrong syntax.

 

[Pat Hartman]

The reference should be:

Forms!yourformname!yourcontrolname

Obviously, the Form must be open and "yourcontrolname" must be a numeric value.