Access - Calculate value of a field based on value of another in the same table
Posted on 2016-10-07
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.