Calculations – how to proceed

[Spider]

Hi Everyone,
I am working on an existing Access database (still) and have beat on it until I get what I need for the data entry for a survey. It’s a complicated survey to put it nicely.
There are 15 questions, and two of those have sub questions a-f and a-d – the survey ends up with 23 responses. I have designed it so that there are actually 23 questions.
Many questions and responses are unique. For example:
Q1.a – Extremely limited, Moderately limited, Slightly limited etc
Q1.b “
Q1c. “
Q1d. “
Q2. Much worse, Slightly worse etc
Q3. Not satisfied, Somewhat satisfied etc.
Q4.
.
Q14.
Q15a
Q15b
The responses range from 5 to 7 choices (Option Groups)– and are not consistent.
Each choice has a numeric value.
I’ve tested all the calculations of the Scoring Instructions on paper without problems.

Examples of the Scoring Instructions for this survey are like this:
“If at least three of the questions 1a through 1f are not missing, then compute:
Score = 100* [(mean of Questions 1a-f actually answered)-1] / 4”
OR
“If at least two of Questions 3, 5, 7 and 9 are not missing, then compute:
S3 = [(Question 3)-1] / 4
S5 = [(Question 5)-1] / 6
S7 = [(Question 7)-1] / 6
S9 = [(Question 9)-1] / 4
Score = 100*(mean of S3, S5, S7, S9)

I need to have Access compute these scoring instructions.
** How do I know “if at least two of the questions are not missing”? **
In your opinion, does this need to be written in VB (which I am not good at)? If so, what direction should I proceed? -would it be a Case, (or) If, (or) Switch for each question?
Or could it possibly be done in a query?
The results will be needed in a report.
Thanks so much!
Vicky

[HansV]

How are the 23 responses stored? As 23 fields in a single record, or as 23 records in a subtable?

[Spider]

23 fields in a single record...

[HansV]

I think it would become too complicated for just a query.

One option is to write a custom VBA function with 23 arguments that computes the score, and to use this for a calculated column in a query.

But that might be slow. Alternatively, write VBA code that loops through the records in the responses table, calculates the score for each and stores it in a field.

[Spider]

Thank you!
I will proceed with the VBA code and see how that goes.
Vicky

[HansV]

Feel free to post back if you need help.

[Spider]

Thank you, I will.
I do want to learn it.