Mass Count Help

Results 1 to 2 of 2

Thread: Mass Count Help

  1. #1
    Join Date
    Dec 1969

    Default Mass Count Help

    I have 1 database table, named: hiring_responses<BR>The table has 3 fields:<BR>ID AUTONUMBER<BR>number VARCHAR(5)<BR>answer VARCHAR(3)<BR><BR>I currently am taking user answers to a group of questions and placing them into this database table. Under answer, the table will only have a value of Yes or No. Under number the value can be: 1, 1a, 1b, 1c, 1d, 1e, 1f, 1fa, 2, 2a, 2b, 3, 4, 4a, etc.<BR><BR>As you can see the system goes in no order at all.<BR><BR>No I want to create a graph.<BR><BR>Answer #1<BR>Yes: 14 answers<BR>No: 6 answers<BR><BR>Answer #1a<BR>Yes: 11 answers<BR>No: 9 answers<BR><BR>The amount of answers under 1 also does not need to equal the amount under 1a, as a question can be skipped.<BR><BR>I could do this ...<BR>sql = "SELECT count(*) FROM hiring_responses WHERE number = &#039 1&#039 AND answer = &#039Yes&#039"<BR>set count_1_A = cnn.Execute(sql)<BR>sql = "SELECT count(*) FROM hiring_responses WHERE number = &#039 1&#039 AND answer = &#039No&#039"<BR>set count_1_B = cnn.Execute(sql)<BR><BR>Response.Write "Question 1&lt;br&gt;" & vbcrlf<BR>Response.Write "Yes: "& count_1_A.fields(0) & "&lt;br&gt;" & vbcrlf<BR>Response.Write "No: "& count_1_B.fields(0) & "&lt;br&gt;" & vbcrlf<BR><BR>But as there is a total of 102 answers and every question would need to be hard coded that could really hurt a database table.<BR>Any suggestions on how to pull a count on each number and how many answers (Yes or No) there are for each?

  2. #2
    sm549 Guest

    Default RE: Mass Count Help

    You could let the database give you a data summary with a single query, then walk the recordset drawing your graph. Try this query:<BR><BR>&nbsp&nbspĀ*select number, answer, count(*) TotalCount<BR>&nbsp&nbspĀ*from hiring_responses<BR>&nbsp&nbspĀ*group by number, answer<BR>&nbsp&nbspĀ*order by number, answer<BR><BR>Note that you won&#039t see any zeros in the resultset, but you could probably detect them as you walk the recordset, and the extra processing would not be as noticeable as with multiple queries.<BR><BR>Hope this helps you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts