We help IT Professionals succeed at work.

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

czaz
czaz asked
on
353 Views
Last Modified: 2013-11-27
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
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)
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);
Sorry...Peter57r just gave that some answer....
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Use CCur, it rounds to four decimals:

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

/gustav
czazDirector of Systems Administration

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If the table is attached (linked) from another database file, the source table gets updated.

/gustav
czazDirector of Systems Administration

Author

Commented:
Is there a way to see what it is linked to?
czazDirector of Systems Administration

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
czazDirector of Systems Administration

Author

Commented:
Rounded as I wanted it to.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.