Update Query with percentage value (1 Viewer)

Lochwood

Registered User.
Local time
Today, 04:48
Joined
Jun 7, 2017
Messages
130
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

AWF VIP
Local time
Today, 12:48
Joined
Jun 26, 2007
Messages
2,645
Code:
update tablename set rate = rate * 1.02
or is that too simple?

HTH:D
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:48
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 04:48
Joined
Jun 7, 2017
Messages
130
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:48
Joined
May 7, 2009
Messages
19,169
on the Click Event of the button:
Code:
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

I’m here to help
Staff member
Local time
Today, 04:48
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 04:48
Joined
Jun 7, 2017
Messages
130
on the Click Event of the button:
Code:
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..
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:48
Joined
Feb 19, 2002
Messages
42,970
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

Registered User.
Local time
Today, 04:48
Joined
Jun 7, 2017
Messages
130
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

Super Moderator
Staff member
Local time
Today, 07:48
Joined
Feb 19, 2002
Messages
42,970
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

Registered User.
Local time
Today, 04:48
Joined
Jun 7, 2017
Messages
130
Code:
update tablename set rate = rate * 1.02
or is that too simple?

HTH:D

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

Super Moderator
Staff member
Local time
Today, 07:48
Joined
Feb 19, 2002
Messages
42,970
The reference should be:

Forms!yourformname!yourcontrolname

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

Users who are viewing this thread

Top Bottom