[AccessD] merging records

John,

    User defined function is found more convenient for such a situation. Sample query Q_Results, as given below, making use of function Fn_SurveyResults() given below, should get the desired results.

    PersonID, Qn and Ans are the fields (all text type) in table T_Survey.

Best wishes,
A.D.Tejpal
------------

Q_Results
==================================
SELECT T_Survey.PersonID, Fn_SurveyResults([PersonID]) AS SurveyResult 
FROM T_Survey 
GROUP BY T_Survey.PersonID;
==================================

Fn_SurveyResults()
'==================================
Function Fn_SurveyResults(Pid _
                        As String) As String
    Dim Qst As String, Txt As String
    Dim rst As DAO.Recordset
    
    Qst = "SELECT Qn, Ans " & _
            "FROM T_Survey " & _
            "WHERE PersonID = '" & _
            Pid & "' ORDER BY Qn;"
    Set rst = DBEngine(0)(0).OpenRecordset(Qst)
    ' Note - For repeated calls from a query,
    '            DBEngine(0)(0) is preferred over
    '            CurrentDb
    
    Txt = ""
    Do Until rst.EOF
        Txt = Txt & ", [" & rst.Fields("Qn") & _
                " - " & rst.Fields("Ans") & "]"
        rst.MoveNext
    Loop
    
    ' Get rid of leading comma if any
    If Len(Txt) > 0 Then
        Txt = Mid(Txt, 3)
    End If
    
    Fn_SurveyResults = Txt
    
    rst.Close
    Set rst = Nothing
End Function
'==================================

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' ; dba-sqlserver at databaseadvisors.com 
  Sent: Tuesday, November 13, 2007 18:23
  Subject: [AccessD] merging records


  I have tables of information about people.  The tables represent polls that
  people have taken, so if a person takes a poll about brands of cigarettes
  smoked, the table would have a record about that person for that poll.  If
  that same person took a poll about software used, the table would have a NEW
  record with information about the same person, but about the software that
  person used.  And so forth and so on.  Now I need to "roll up" all of the
  information about a person into a single record so that one record contains
  all of the information about that person contained in all of the records
  about that person in the table.

  Is this possible directly in SQL?  Do I need to write code to iterate
  through the table finding each person and all the records for that person,
  and then consolidating the information from the second and subsequent
  records into the first record found?

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com