Link to home
Create AccountLog in
Avatar of taskhill
taskhillFlag for United States of America

asked on

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

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.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

it would be more easier and dynamic for calculation if you have an encounter point table to store the points to be allocated for your range of encountered values
Avatar of taskhill

ASKER

I am fine with creating another table but I need assistance in how to calculate the values using SQL, VBA, or any other method.  Since the values are limitless I was leaning towards a loop but don't know how they are constructed in VBA or SQL. It would be easy for me to do in PHP but unfortunately  that is not an option because this is a system at work.
once you have 2 tables you can construct the relevant SQL to generate the output you needed.

I think this can be done.

will you want to go with database option?
Yes a second table is fine with me.  I just need help creating the SQL to generate the results
ASKER CERTIFIED SOLUTION
Avatar of Antonio Salva Ripoll
Antonio Salva Ripoll
Flag of Spain image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
you can try this if you wish to use 2 tables do do mapping and calculation
28975123.accdb