[AccessD] merging records

A.D.TEJPAL adtp at airtelbroadband.in
Wed Nov 14 22:51:59 CST 2007


    Thanks Robert!  So nice of you.

A.D.Tejpal
------------

  ----- Original Message ----- 
  From: Robert 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, November 15, 2007 02:13
  Subject: Re: [AccessD] merging records


  -----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
  > '=====================================


More information about the AccessD mailing list