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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

golfDoctorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.