counting all chosen values

  • Thread starter Thread starter Milton Snider
  • Start date Start date
M

Milton Snider

I have a web page which contains a list box. The list box has 55 rows of
choices. The user can choose at most 10 topics of interest by holding down
the control key. The web page stores the values in an access DB in a single
field separated by commas. The table might look like:
mytable
id choices
1 1,2,5,33,44,45,46,49,50,53
2 40,43,45,46,47,48,49

I need to count all the instances of 1
I need to count all the instances of 2
etc so that I know the most popular topics. I don't think I know how to
accomplish this. The only solution I have is to export to excel and maybe
try that unique function. Any ideas of how to do this in access 2002?
thanks
Milton
 
Hi,


Since your data is not normalized (it does not have just one value per
field), you can proceed through a old VBA way:

================air code
...

Debug.Assert True ' need VBA6

Dim x(1 to 55) As Long 'the result
Dim y() As String ' split of the choices
Dim i as long ' loop on the choices
Dim k As long ' numerical (was string) value of a choice
Dim up As long ' number of choices ( minus 1 )
Dim rst as DAO.Recordset ' recordset that will hold the data

Set rst=CurrentDb.OpenRecordset("SELECT choices FROM ... ", ... , ... )

Do until rst.EOF
y=split(choices, ",")
up=UBound(y) 'optimization: avoid computing it
For i=LBound(y) to up ' ...each time, in the loop
k=eval(y(i)) ' again, optimization
x(k)=x(k)+1 ' ... avoid to compute it twice
Next i
rst.MoveNext
Loop


' the answers are now in array x( )
' we will print them in the immediate window

For i=1 to 55
' print a line each 10 output
If 1=i mod 10 then Debug.Print
Debug.Print x(i); ' continue the print on the same line
Next i
Debug.Print ' print the last batch


================


You can add error handling, since many things can go wrong (like having a
choice of -7 or of 88, out limit, etc). You need VBA6 (Access 2000 or
later).



Hoping it may help,
Vanderghast, Access MVP
 
Milton

Consider a different table structure. By embedding multiple values in a
single field, you've guaranteed yourself that you'll have to build a parsing
function to extract information from that field.

On the other hand, if your table looks more like:

trelUserChoice
UserID
Choice

you'd have one row per user X choice. And finding the count of choice = 1
is a VERY simple query.
 
Jeff,
I have not deployed the top 10 topics web yet. How would you suggest I
collect the data so that i can have the data separate? Should I use check
boxes for each topic? I suppose then I could just count the yes answers in
the access database?
thanks
Milton
 
Milton

Your first post mentioned a listbox will all possible choices. I don't see
why you'd need to change that. You must have some routine that iterates
through the listbox, identifying the chosen items. Could you modify that
routine to write rows instead of a long string of comma separated values?
 
Back
Top