Split and count from survey results

[meldrape]

Greetings,

I have survey results where 4 of the questions had multiple checkboxes: Basic, Advanced, Specialty. All of the checkboxes are named the same so some of the answers look like this: Basic, Advanced, Specialty

<form>CPT Coding for Staff: 
<input type=checkbox name=cpt value=Basic>&nbsp;Basic
<input type=checkbox name=cpt value=Advanced>&nbsp;Advanced
<input type=checkbox name=cpt value=Specialty>&nbsp;Specialty

Some of the survey takers checked all of them, some only 1 or 2. I need to split out and count the number of basic, advanced, specialty and I'm stuck. I kind of figured out how to split them out but not how to count them. Any help would be appreciated. thanks!

 

[Sheco]

If you are saving the survey results in a database, perhaps the best approach is to use the Count keyword in an SQL Select query.

or do you need to count them before they go into the database?

 

[meldrape]

Many thanks for your reply. I can count them later, when I display the results. So you mean like:

set rs=objconn.execute("select count(cpt1) from surveydetails")

something like that? But that will count all of them and I need to split them up. How many basic, or advanced, or specialty boxes... Like 15 basics, 30 advanced, etc. because some people only checked one box... I know, it's an inept design. I'm trying to compensate for that, ha!

 

[meldrape]

Sheco, I found one of your old threads: thread333-1040057

You made a suggestion that sounds like what I need I just don't know how to do it. any thoughts? Many thanks again.

 

[Sheco]

ah yeah, you could put multiple fields into your table... so that a record has a true/false bit flag type field for each level.

For simplicity sake, suppose your table looks like this:
UserID -> character text field
CPT_Basic -> bit flag true/false
CPT_Advance -> bit flag true/false
CPT_Special -> bit flag true/false


In the page that processes the form, you have somethign like this:
Dim bBasic, bAdvance, bSpecial
bBasic = CBool(Instr(Request("cpt"), "Basic"))
bAdvance = CBool(Instr(Request("cpt"), "Advanced"))
bSpecial = CBool(Instr(Request("cpt"), "Specialty"))


Then your SQL might be something like:
"INSERT INTO surveydetails (UserID, CPT_Basic, bAdvance, bSpecial) VALUES ('" & Request("UserName") & "', " & bBasic & ", " & bAdvance & ", " & bSpecial & ") "


If you do all that then your count becomes really easy:
"select count(CPT_Basic) from surveydetails

 

[Sheco]

Also you could use an integer to store all 3 on top of each other as a sort of bit flag.

1 = Basic
2 = Advanced
4 = Specialist

Then you could just sum up the numbers and you are guaranteed to get a unique number for any combination of the 3:
1 = Basic Only
2 = Advanced Only
3 = Basic + Advanced
4 = Specialist only
5 = Specialist + Basic
6 = Specialist + Advanced
7 = Specialist + Advanced + Basic


This kind of thing is very kind on the database storage, but not so much fun to count later if you can't do a bitwise operator.

 

[meldrape]

Sounds great, but I cannot change the structure of the table. I'm only supposed to tally the results. So there is only one field with a comma delimeted value. I don't like the thought of doing it by hand but I'm out of choices. when i response.write the field values in a do loop they look like this:
basic, advanced, specialty
basic
advanced
basic, specialty

etc. So there reallly is no way I can do a split and count how many basics there are, etc.

 

[meldrape]

Ok, I like your second idea. Question is, how do I put that in place? Maybe I can figure it out... Thank you so much for your ideas!!

 

[Sheco]

Ohhhh

Well what if you use a WHERE clause with a LIKE and wildcards ?

select count(cpt1) from surveydetails where cpt1 like '%basic%'