Increase number in table cell by percentage & round to nearest decimal.

I have an access table that has a column of job rate.   Ex: 4.2516.      They need all the numbers (in the cells) in this column increased by  2.06% .   So the example would now be 4.3392   (they want it still to be rounded to the 4th decimal place.)

I know mathwise, it is (X * 1.0206) but am unsure how to apply this formula to all the cells in the column
czazDirector of Systems AdministrationAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
UPDATE [SomeTable]
SET [job rate] = Round([job rate] * 1.0206, 4)

or, unrounded:

UPDATE [SomeTable]
SET [job rate] = [job rate] * 1.0206
0
 
peter57rCommented:
Well you really need to agree rounding rules with your users, otherwise you will have arguments about the 4 th DP values.

The datatype of the JobRate field is important.
If it is currency you can do..
Update tablename set [job rate] = 1.0206*[Job Rate]

However if it is a double, then you will have to round:
Update tablename set [job rate] = round(1.0206*[Job Rate],4)
0
 
momo4kidsCommented:
You can also update the cells using an update query.  It should look something like this....
UPDATE [Tablename] SET [Tablename].JobRate = Round([JobRate]*1.0206,4);
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
momo4kidsCommented:
Sorry...Peter57r just gave that some answer....
0
 
Gustav BrockCIOCommented:
Use CCur, it rounds to four decimals:

UPDATE
  [YourTable]
SET
  [JobRate] = CCur([JobRate] * 1.0206)

/gustav
0
 
czazDirector of Systems AdministrationAuthor Commented:
Is is possible for updates in one database to effect other files??
I ran a script & it seems to have updated a field accross multiple files, even ones in archive?

How is this possible
0
 
Gustav BrockCIOCommented:
If the table is attached (linked) from another database file, the source table gets updated.

/gustav
0
 
czazDirector of Systems AdministrationAuthor Commented:
Is there a way to see what it is linked to?
0
 
czazDirector of Systems AdministrationAuthor Commented:
I found where the file is linked to and was able to restore data.

They are altering their request.  They want updates based on a JobCode of 'R',  So the current request is if JobCode=R, update the JobRate datafield.

BTW: I am using Access 2003
0
 
Gustav BrockConnect With a Mentor CIOCommented:
How about:

UPDATE
  [YourTable]
SET
  [JobRate] = CCur([JobRate] * 1.0206)
WHERE
  JobCode = 'R'

/gustav
0
 
czazDirector of Systems AdministrationAuthor Commented:
Rounded as I wanted it to.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.