[AccessD] merging records

A.D.,

Thanks for the response.  Unfortunately it is not that simple, i.e. there
are about 700 fields, of which about 600 are responses to query questions.
Each of those 600 fields will need to be merged with the alternate record.
for example:

FName LName	Addr	Smokes Softdrink Car
John  Colby	1723	N	 ''	     ''
John  Colby 1723  ''     Pepsi     ''
John  Colby 1723  ''     ''        Ford Escort

In at least one table there are 600 fields.  The fields are divided into
"sets" of fields.  One set is about boats - State registered, length, type,
engine etc.  Another set is about medications taken - Zoloft, Aspirin, Etc.
Another set is about electronics purchased - stereo, cb, computers, cell
phones etc.

Thus there are entire sets of fields where there are answers, and
potentially (other than name / address) those may be the ONLY fields with
data, or potentially someone in the past has already merged the car fields
and the medical fields but not the electronics fields.

I am attempting to allow a form of data mining I guess, but not really.  The
user wants to be able to ask questions like "return name and address for all
people who smoke and drive a Chevy and purchased CBs".  Let's not get bogged
down in "why would he ask that", I am not saying that he will, that is just
an example.  Let's simply assume that he can ask for where clauses on ANY
combination of fields.  Obviously if the answers to fields are scattered out
in multiple records, then it becomes difficult to provide a name set who fit
all the criteria because they don't fit all the criteria in any one record.

Thus I need to merge the data for a single person at a single address into a
single record.  I can, and often but not always do, have several records for
a given person at that same address.

My gut tells me that this is a "software" kind of solution.  There are just
too many fields to manually type in all the field names that need merging.
I need to be able to say something like... merge fields 78-740.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Wednesday, November 14, 2007 8:10 AM
To: Access Developers discussion and problem solving
Cc: A.D.TEJPAL
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com