[AccessD] merging records

Robert robert at servicexp.com
Wed Nov 14 14:43:52 CST 2007


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A.D.,
 Nice, that is very cooooool....

Robert

A.D.TEJPAL wrote:
> John,
> 
>     Apparently, each column carries only one significant value (over a group of records) for each combination of person & address. Your objective is to display only one compacted row per combination of person & address, showing only the significant values for survey results in various columns across the record.
> 
>     As a programmatic solution, the following course of action is suggested:
> 
>     1 - Let the source table be named T_Data. Its first four fields are ID (PK), FirstName, LastName and Address, followed by large number of other fields (like Smokes etc) meant to hold survey response.
> 
>     2 - Create an empty table named T_Result. Its structure should be identical to that of T_Data.
> 
>     3 - Create a dummy table T_Dummy having one field. Populate it with one record.
> 
>     Having taken the above steps, if you run sample subroutine P_PopulateResultTable as given below, table T_Result will get populated with the compacted survey results in desired format. You might like to try it out and confirm whether it is in line with what you have been aiming at.
> 
>     Note - It has been tested on Access 2003 desktop (Access 2000 file format). Reference required - DAO 3.6
> 
> Best wishes,
> A.D.Tejpal
> ------------
> 
> Sample subroutine - for merging survey results
> T_data is source table. Results are appended to T_Result.
> '=====================================
> Sub P_PopulateResultTable()
>     ' This subroutine merges the survey
>     ' results for each person in source table
>     ' T_Data and appends the outcome into
>     ' destination table T_Result. Structure of T_Result
>     ' is identical to that of T_Data
>     ' T_Dummy is a single field single record table.
>     
>     Dim Qst As String, Txt As String
>     Dim Fnm As String, Qst2 As String
>     Dim Fv As Variant
>     
>     Dim rst1 As DAO.Recordset
>     Dim rst2 As DAO.Recordset
>     Dim fd As Field
>     Dim tdf As TableDef
>     Dim db As DAO.Database
>     
>     Const SourceTable As String = "T_Data"
>     Const DestnTable As String = "T_Result"
>     Const DummyTable As String = "T_Dummy"
>     
>     ' Comma separated string of all field names
>     ' that do not directly carry survey response
>     Const ExemptFields As String = _
>             "ID,FirstName,LastName,Address"
>     
>     Set db = DBEngine(0)(0)
>     
>     ' Clear destination table
>     db.Execute "DELETE * FROM " & _
>                     DestnTable & ";", dbFailOnError
>     
>     Qst = "SELECT FirstName, LastName, " & _
>             "Address FROM " & SourceTable & _
>             " GROUP BY FirstName, " & _
>             "LastName, Address;"
>     Set rst1 = db.OpenRecordset(Qst)
>     
>     Set tdf = db.TableDefs(SourceTable)
>     
>     Do Until rst1.EOF
>         Qst = "INSERT INTO " & DestnTable & _
>                 " SELECT '" & _
>                 rst1.Fields("FirstName") & "' AS " & _
>                 "FirstName, '" & rst1.Fields("LastName") & _
>                 "' AS LastName, '" & rst1.Fields("Address") & _
>                 "' AS Address,"
>         For Each fd In tdf.Fields
>             Fnm = fd.Name
>             If InStr(ExemptFields, Fnm) > 0 Then
>             Else
>                 Qst2 = "SELECT " & Fnm & _
>                         " FROM " & SourceTable & _
>                         " WHERE FirstName = '" & _
>                         rst1.Fields("FirstName") & _
>                         "' AND LastName = '" & _
>                         rst1.Fields("LastName") & _
>                         "' AND Address = '" & _
>                         rst1.Fields("Address") & _
>                         "' AND Len(" & Fnm & ") > 0;"
>                 Set rst2 = db.OpenRecordset(Qst2)
>                 
>                 If rst2.RecordCount > 0 Then
>                     Qst = Qst & " '" & rst2.Fields(0) & _
>                             "' AS " & Fnm & ","
>                 Else
>                     Qst = Qst & " Null AS " & Fnm & ","
>                 End If
>                 
>             End If
>         Next
>         
>         ' Remove trailing comma
>         Qst = Left(Qst, Len(Qst) - 1)
>         Qst = Qst & " FROM " & DummyTable & ";"
>         
>         ' Append to destination table
>         db.Execute Qst, dbFailOnError
>         
>         rst1.MoveNext
>     Loop
>     
>     rst1.Close
>     rst2.Close
>     Set rst1 = Nothing
>     Set rst2 = Nothing
>     Set fd = Nothing
>     Set tdf = Nothing
>     Set db = Nothing
> End Sub
> '=====================================
> 
>   ----- Original Message ----- 
>   From: jwcolby 
>   To: 'Access Developers discussion and problem solving' 
>   Sent: Wednesday, November 14, 2007 19:09
>   Subject: Re: [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.
> 
>   << SNIPPED to prevent overall size crossing limits >>
> 
>   John W. Colby
>   Colby Consulting
>   www.ColbyConsulting.com 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHO14H72dSYCwH8FQRAgB4AKCib+mOSRFqmWCrhBSxqrS0lIh5DwCgpOkC
BvIeTD2jF0e2Skvfkla+kLY=
=YHVF
-----END PGP SIGNATURE-----




More information about the AccessD mailing list