Calculating total selected options in an option group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am using Access to enter data collected from a particular
questionnaire. These then translate into answers which are coded from 0-8. I
have used option groups with checkboxes for each question data entry; this
most closely resembles the form itself so i'm hoping will help with accurate
data entry. There are 27 questions and i'd like a function to automatically
sum up the total number of particular selections in the option group (from
0-8). i'm guessing this would mean a real time update of how many 1's you've
selected and so on. Anyone know how i can do this? i'd really appreciate any
help but i'm not that clued up on functions so simple response please!!
 
Hi Emilina,

It sounds like you have 9 option groups on your form? I gather this because
an option group accepts will accept only one option. Based on this
assumption, here's my suggestion:

An option group has a property called value. The value stores the number
associated with each option in the group. Let's say you have an age
demographic option group with 3 options: 21-25, 26-29, & 30-34. Each one of
these options has a number associated with it. Let's assume the numbers are
1, 2, & 3, respectively. The option group keeps track of which option is
selected by the number. So if the user clicks the 26-29 button, the option
group value is now set to 2. If no selection is made for the option group,
the value is set to Null.

You can use the value property to calculate the total number of options
selected. When it comes to this calculation, you are just looking for values
that are not null. In my example, I declare a variable to store the total
and use a series of If statemnts that check to see if the option groups have
a value greater than
0.

Also, as an fyi, you don't necessarily need to write a function for this,
you can use a subroutine. I guess that depends on what you are doing with
the total once you get it. For instance, the code in this following example
is written in the click event of a command button:

Private Sub MyCommandButton_Click()
Dim optcount As Integer
If Frame0.Value > 0 Then
optcount = optcount + 1
End If
If Frame1.Value > 0 Then
optcount = optcount + 1
End If
If Frame2.Value > 0 Then
optcount = optcount + 1
End If
If Frame3.Value > 0 Then
optcount = optcount + 1
End If
If Frame4.Value > 0 Then
optcount = optcount + 1
End If
If Frame5.Value > 0 Then
optcount = optcount + 1
End If
If Frame6.Value > 0 Then
optcount = optcount + 1
End If
If Frame7.Value > 0 Then
optcount = optcount + 1
End If
If Frame8.Value > 0 Then
optcount = optcount + 1
End If
End Sub

The frame1 - frame8 refer to the option groups. You would obviously need to
change the names to whatever your option groups are named for it to work.

HTH!
AA
 
Thanks so much for your in depth reply! I don't know whether it's my
inexperience or whether I didn't explain myself properly but I don't think
your kind answer solves my problem!

There are 27 option groups in total (corresponding to 27 questions on the
questionnaire). For each option group, the user can select any one of 9
different options (corresponding to agree-to-disagree response as well as not
applicable etc).

I want to be able to input the particular option choice for each of the 27
questions and then use some function to automatically sum up the total number
of '1's selected out of 27, the total number of '2's selected and so on. The
total of this sum is interesting overall and I would want to store the answer
as Total 1, Total 2 etc.

Is that any clearer or did you know that already from my initial post? I
really appreciate your help!

Thanks, E
 
Well this would get it working but it's a lot of code. It's fairly easy to
write because you can copy and paste; however, there's probably a better way
to evaluate each option group, suggestions anyone? This will at least get it
working for now...
'Declare an integer value for each option group 1-9
Dim opt1 As Integer
Dim opt2 As Integer
Dim opt3 As Integer
'other variables here


Select Case Frame0.Value
Case 1: opt1 = opt1 + 1
Case 2: opt2 = opt2 + 1
Case 3: opt3 = opt3 + 1
'other cases here

End Select

Select Case Frame9.Value
Case 1: opt1 = opt1 + 1
Case 2: opt2 = opt2 + 1
Case 3: opt3 = opt3 + 1
'other cases here
End Select

Select Case OptionGroup3.Value
Case 1: opt1 = opt1 + 1
Case 2: opt2 = opt2 + 1
Case 3: opt3 = opt3 + 1
'other cases here
End Select

'All 24 other statements go here....

End Sub


Like I said...it's a lot of code, but it would work temporarily for you. If
I had more time, I'd write ya something better....
 
Hello!
Thanks again for taking the time to respond - i'm amazed at how helpful
people are! only problem is, i'm not sure exactly where i write this code or
how i get it to function? if i'm understanding the code correctly, this one
should add up the total number of 1's selected out of the 27 questions, the
total number of 2's selected etc. however, if it does it all in one go, how
will i see it separated out if that makes sense? i want a field somewhere
which gives total option 1's selected and another which shows total option
2's selected and so on.
is that what this code will do? if so, how / where do i actually put it!

apologies again but i'm relatively new to this stuff and trying to learn as
i go along but i'm slowly realising just how complex access can be

cheers, emma
 
Hi Emelina,

I guess it depends on what you are doing with the information. The way the
code works:
dim opt1
dim opt2
dim opt3
through dim opt 27

each of these are called variables. opt1 contains the total for all of the
one's selected. opt2 contains all the two's selected, etc.

So, what is it you need to do with the information? Do you need use the
totals in combination with other information or are you just interested in
knowing the scores by themselves? You can get started with this to get a
feel for it, by creating a command button on your form. (Click cancel when
the wizard starts going) Right click on it, go to properties, give a
meaningful name. Then scroll down in the properties to onclick event. Click
the dots.... on it. if it asks you want you want, expressionbuilder,
codebuilder...select codebuilder.

It should open the Visual Basic editor screen to the command button click
event which looks like this:

private sub mycommandbutton_click:


End sub

You need to put your code inbetween private sub & end sub.

Also add this line after each select case statement (after the end select
line) msgbox "There are a total of " & opt1 & " number of One's selected",
vbokonly

after the next case,
"There are a total of " & opt2 & " number of Two's selected"_vbokonly

etc.

The end result is that when you click the command button you'll get 27
message boxes showing you the totals for each group. Now I know that's not
what you probably want as an end result, but it will help you to get a feel
for vba code.
 
that's worked!! i had to play around with the coding a bit as i only wanted 9
message boxes (1 summarising each of the 9 potential options over the 27
questions) but that's perfect! it's exactly what i needed as it means i will
be able to manually input the totals for storage in another table.
thanks so much for your help,
emma
 
Back
Top