We help IT Professionals succeed at work.
Get Started

Access - Calculate value of a field based on value of another in the same table

taskhill
taskhill asked
on
443 Views
Last Modified: 2016-10-10
Greetings Experts,

I have a table ('user') with the following structure:

ID (pk), Name, Age, EnctrCnt

I am trying to figure out a way (vba, query, view, new table, etc.) to calculate a weighted score based on the value of EnctrCnt.  

For the weights, if the user's encounter count is between 1-3 they get 3 points for each encounter, between 4-10 they get 5 points for each encounter, and for >10 they get 7 points for each encounter.  

I have tried doing this in a report and form using if statements.  The report didn't work and the form only worked if I clicked through each record using the On Load event.  I tried the On Open but nothing happened.

I have basic SQL and VBA knowledge so I am struggling with what is and is not allowed in Access.  

I am using Access 2013

Please let me know if you have any questions.
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
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
Ask ANY Question

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

  • Troubleshooting
  • Research
  • Professional Opinions
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