[AccessD] merging records

Gustav,

    In its present version, the suggested procedure does not open any recordset for target table. After all the values meant to be inserted in various fields are lined up, the pertinent record gets inserted in destination table in one go via append query.

    As pointed out by you, pure recordset approach can also be adopted in lieu of append query method. John states that the number of fields involved is very large. That would require corresponding large number of assignment calls from one recordset to the other.

    It might be interesting if an actual trial could be conducted over a large data set, using append query visa-vis recordset approach. Perhaps John could do it and let us know.

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

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, November 15, 2007 13:43
  Subject: Re: [AccessD] merging records


  Hi A.D.

  But - as you have the DAO recordset present - why not use AddNew/Update to replace the slow "Insert Into .." SQL execution?

  /gustav

  >>> adtp at airtelbroadband.in 14-11-2007 20:49 >>>
  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
  '=====================================