OK, with the risk of this being too big a question:
I am working on porting a pretty massive scoring system of grant applications. Each application has let’s say 100 questions, each ranging from 2-15 possible answers (some multiple, some single select) and each answer is worth a certain number of points (integer).
I’m thinking one transparent way to do it without massive scripting (which may be what we end up doing), is to have each answer in a linked table, and in that answer table, have a column that is the point value. Then use roll-up to calculate a score (except where the score has to choose the highest scoring component, which i can do with a formula).
HOWEVER - I don’t want to have a different linked table for each question. SO - I can use the ‘Limit record selection to a view’ option to narrow the options for each question. HOWEVER, this would result in hundreds of views to cover every question in each of four program areas. Maybe I could break up the linked answer tables by program and make it manageable, but is there any other way to narrow select options based on some condition or logic?
Or another way to assign score to drop down answers without using linked records? I guess if could have a SWITCH column for each answer column, but that doubles the table length and makes rollup a manual formula, etc.
Any thoughts appreciated. Thanks!!