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.
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.
Microsoft AccessSQL
Last Comment
Ryan Chong
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
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?
I think this can be done.
will you want to go with database option?
ASKER
Yes a second table is fine with me. I just need help creating the SQL to generate the results
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
you can try this if you wish to use 2 tables do do mapping and calculation
28975123.accdb
28975123.accdb