Need help on select with group by statement

I am trying to figure out how to group some results from a survey.

I have 4 tables: SurveyResults, SurveyAnswers, SurveyQuestions, and Users

SurveyResults
---------------
results_id
UserID
question_id
answer_id
date_inserted

SurveyAnswers
---------------
answer_id
question_id
answer
anwser_type
order_in_question

SurveyQuestions
---------------
question_id
survey_id
question
question_type
default_answer
order_in_survey

Users
---------------
UserID
Company
Phone

I have this select statement that works but does not group properly:

sub db_select_grid_SurveyResults
      grid_SurveyResults_sql = "SELECT " & _
      "Users.Company," & _
      "Users.Phone," & _
      "SurveyResults.results_id, " & _
      "SurveyResults.UserID, " & _
      "SurveyResults.answer_value, " & _
      "SurveyResults.dt_inserted, " & _
      "SurveyAnswers.question_id, " & _
      "SurveyAnswers.answer_id, " & _       
      "SurveyAnswers.answer, " & _
      "SurveyAnswers.order_in_question, " & _
      "SurveyQuestions.question, " & _
      "SurveyQuestions.survey_id FROM (((SurveyResults LEFT JOIN SurveyAnswers ON SurveyAnswers.answer_id = SurveyResults.answer_id) " & _
      "LEFT JOIN SurveyQuestions ON SurveyQuestions.question_id = SurveyAnswers.question_id) " & _
      "LEFT JOIN Users On SurveyResults.UserID=Users.UserID) ORDER BY SurveyResults.UserID, SurveyAnswers.order_in_question" & _
      "" & _
      "" & _
      ""
end sub


if grid_SurveyResults_sql <> "" then
      cmd.CommandText = grid_SurveyResults_sql
      rs.Filter = ""
      if survey_id <> "" then rs.Filter = "survey_id = " & to_sql(survey_id,"number")
      rs.CursorLocation = 3
      rs.CacheSize = 5
      rs.Open cmd
      if not rs.EOF then
                  rs.MoveFirst
                  rs.PageSize            = 30
                  totalPages             = rs.PageCount
                  rs.AbsolutePage      = curPage
                  num_recs = rs.RecordCount
                  results = true
      else
            results = false
            rs.Close
      end if
else
      results = false
end if


The above SQL produces:

UserID      Company            Phone                  Date            Question                  Answer       
--------------------------------------------------------------------------------------------
1            Company1      111-222-3333      10/27/2008      How many dogs?            7
1            Company1      111-222-3333      10/27/2008      How many cats?            3
1            Company1      111-222-3333      10/27/2008      How many birds?            1
2            Company2      111-222-3333      11/02/2008      How many dogs?            5
2            Company2      111-222-3333      11/02/2008      How many cats?            3
2            Company2      111-222-3333      11/02/2008      How many birds?            1
3            Company3      111-222-3333      10/16/2008      How many dogs?            1
3            Company3      111-222-3333      10/16/2008      How many cats?            3
3            Company3      111-222-3333      10/16/2008      How many birds?            1



Need data displayed like:

UserID      Company            Phone                  Date            Question                  Answer       
--------------------------------------------------------------------------------------------
1            Company1      111-222-3333      10/27/2008      How many dogs?            7
                                                                        How many cats?            3
                                                                        How many birds?            1
2            Company2      111-222-3333      11/02/2008      How many dogs?            5
                                                                        How many cats?            3
                                                                        How many birds?            1
3            Company3      111-222-3333      10/16/2008      How many dogs?            1
                                                                        How many cats?            3
                                                                        How many birds?            1

Each user has multiple questions and answers. Can anyone help me modify my select statement (or code) to group the results properly?
ChristinaPupoAsked:
Who is Participating?
 
golfDoctorConnect With a Mentor Commented:
you would store the userid in a variable in each loop, and check each time if the userid is different that the previous record userid.  if it is different that display the full information for the line.  if userid is same as last record, then only display the question/answers information on the line.

example
<%

olderuserid = 0 'default userid

do until rs.eof
  newuserid = rs("userid") ' new user id
   
   if newuserid <> olduserid then  'compare old and new
        response.write "full information"
   else
        response.write "questions/answer info"
   end if
    olduserid = newuserid 'reset old userid with newuserid for next record
rs.movenext
loop
%>
0
 
ChristinaPupoAuthor Commented:
So simple I never even considered it, lol. I will give it a try and get back to you.
0
 
ChristinaPupoAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.