Option Button

[shaggi03]

How could I count the amount of time an option was selected from an option group? The information is been saved in a table.

Could it be done with SQL sentence in VBA? If so could you add a sample code?
:banghead: :help

[geekgirlau]

This will count the number of times a particular option was selected:


Dim strSQL as String

strSQL = "UPDATE tbl_MyOption_Count " & _
"SET lng_Opt_Count = nz([lng_Opt_Count],0)+1"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


The SQL above assumes that there is a table "tbl_MyOption_Count" containing the field "lng_Opt_Count" (data type Long). You would need to use the code as part of the On Click procedure for a button, with an IF statement to check which option was selected.

[shaggi03]

Let's say I have a option group with 5 selections

Apple
Banana
Cherry
Chocolate
Celery

To each time apple is selected 1 is saved to a field in a table, if banana is selected 2 is saved to the field, and so on.

What I need is the following:

I need to count how many time apples was selected, how many times bananas was selected and so on.

So lets say 10 user selected apples, 8 selected chocolate, 2 selected Cherry, etc.

Then
Apple = 10
Chocolate = 8, etc.

Can this be done? And if so could you show a code sample?

[xCav8r]

:hi:

Not sure where you're going with this, but...

Option Compare Database
Option Explicit
Sub CountFruitSelections()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("qryCountOfFruitSelections")

' SELECT tblFruit.strFruit, Count(tblFruitSelections.lngFruitSelectionID) AS NumberSelected
' FROM tblFruit INNER JOIN tblFruitSelections ON tblFruit.lngFruitID = tblFruitSelections.lngFruitID
' GROUP BY tblFruit.strFruit;

With rst
Do Until .EOF = True
Debug.Print .Fields!strFruit & ": " & .Fields!NumberSelected
.MoveNext
Loop
End With
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub

HTH!

[shaggi03]

I don't know if this explanation will make thing clearer, for starters I used the wrong title it should read "Option Group" not Option Button, the information is pickup from a form that has an option group.

The user selects one of the options the option group. When the option group was created it was establish that the information was numeric and it's been saved in a field of the table

At the end of a week or month, they need a report that establishes the amount of time an option was selected.

Example:
Let's say that the question is "Select one of the following fruit" and to each time a user enters the specific screen he or she selects and option, example Apple and the numerical value assigned to apple is saved in a field called fruit.

When the option group was created apple was assigned the number 1, banana 2, cherry 3, pear 4 and grapes 5.

What I need to know is how many times one of the options was selected. Example how many people selected apple, how many selected cherry and so on.

[xCav8r]

shaggi03, :hi:

Did you, by chance take a look at the attachment in my previous post? It already does want I think you want to do (even after reading the last explanation). Perhaps if I explain it, things will make more sense.

First, there are two tables. The first table called "tblFruit" contains the five types of fruit you listed. It has two fields: a numeric key (which can correspond to the numeric value in your option group) and a text field to capture the name of the fruit. The second table called "tblFruitSelections" corresponds more or less to the table called "Fruit" in your attachment. As the name implies, it captures users' selections.

Second, there is a query called "qryCountOfFruitSelections". I pasted its SQL as a comment in the VBA in my previous post. This query counts all the instances where each fruit was selected.

Third, I provided in Module1 a code sample as you requested. The code can be seen in my previous post. It does nothing more than step through the query above, printing the totals to the immediate window.

I hope that helps.