Create a calculated field from sum of radio buttons

[antimoneylaundering]

I need to create a calculated field from radio buttons. I have been searching and have not found anything that addresses my particular situation. I need a calculated field that works like this:



207 - (value of all 'no's)/ 207 * 100.

The value of all 'no's will be a sum of all of the 'no's selected. Some 'no's are 6 points deductions, while others are 3 point deductions, etc.

I created radio buttons for each question (48 total) with the option values of 'no' are set as the points (e.g. 6, 3, etc), which I've read to do. I have googled calculations from radio buttons and nothing looks quite like this. How should I proceed?

Thank you!

[ranman256]

The radio buttons should have been bound to a field. (*yes, 0 no) = true in tTable.Q1
This way you need only run a query to sum the values.

[June7]

As ranman says, save value to record.

What is table structure?

[antimoneylaundering]

I have attached a small mock up of what I currently have. I may have totally botched this.

Thanks for the responses.

Example.accdb

[June7]

So the mockup table has 8 Completed fields. Do you want to count the False responses for each record?

IIf([Task A Completed]=6,1,0) + IIf([Task B Completed]=3,1,0) + IIf([Task C Completed]=2,1,0) + IIf([Task D Completed]=6,1,0) + IIf([Task E Completed]=6,1,0) + IIf([Task F Completed]=6,1,0) + IIf([Task G Completed]=6,1,0) + IIf([Task J Completed]=6,1,0)

Adding the values would be similar conditional expression. Replace the 1 with appropriate value.

Now imagine doing that for 48 fields. Not only will that be tedious, the expression will likely exceed length limit.

If you saved 0 for Yes and Null for N/A then addition calc could be:

Nz([Task A Completed],0) + Nz([Task B Completed],0) + Nz([Task C Completed],0) + Nz([Task D Completed],0) + Nz([Task E Completed],0) + Nz([Task F Completed],0) + Nz([Task G Completed],0) + Nz([Task J Completed],0)

However, same issues - tedious and too long.

[antimoneylaundering]

I do want to count responses for each record. I've come to terms with this being tedious, but if this is going to be too long how would you recommend I go about this? Is there any way to salvage what is built?

[June7]

Are you trying to build a questionnaire/survey database? This has been discussed in other threads. Search forum.

Would require VBA code to do the counts and addition.

Probably should scrap what you have and start over.

http://www.rogersaccesslibrary.com/