Consolidate data from multiple rows into 1 row??

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • stateemk
    New Member
    • Aug 2009
    • 62

    Consolidate data from multiple rows into 1 row??

    I have attached an example of what I am wanting to do. Please let me know if you have any additional questions or need more information from me. Thanks.
    Attached Files
  • ADezii
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #2
    Originally posted by stateemk
    I have attached an example of what I am wanting to do. Please let me know if you have any additional questions or need more information from me. Thanks.
    What is the Maximum Number of Duplicates that can exist in the Original Table for a given UPN?

    Comment

    • stateemk
      New Member
      • Aug 2009
      • 62

      #3
      There is no max.

      Comment

      • ADezii
        Recognized Expert Expert
        • Apr 2006
        • 8834

        #4
        Originally posted by stateemk
        There is no max.
        That significantly complicates matters but I'll see what I can do.

        Comment

        • ADezii
          Recognized Expert Expert
          • Apr 2006
          • 8834

          #5
          I've produced your results exactly as requested, but unfortunately the code is rather cumbersome and complex, and I honestly do not have the time to refine it. It will also only work for a MAXIMUM of 3 Duplicates on the [UPN]. I will post what I have, and make the Test Database available to you as an Attachment, so that you may further develop it should you so desire.
          Code:
          Dim MyDB As DAO.Database
          Dim rstUniqueUPNs As DAO.Recordset          'Contains the Unique UPNs
          Dim rstOrig As DAO.Recordset                'Records in tblOriginal with Unique [UPN]s
          Dim rstFinal As DAO.Recordset               'Will write the Final Results
          Dim intNumOfUPNs As Integer
          Dim intCounter As Integer
          Dim varRes09 As Variant
          Dim varRes08 As Variant
          Dim varYrBlt As Variant
          
          CurrentDb.Execute "DELETE * FROM tblConsolidated", dbFailOnError
          
          Set MyDB = CurrentDb
          Set rstUniqueUPNs = MyDB.OpenRecordset("SELECT DISTINCT [UPN] from tblOriginal", dbOpenForwardOnly)
          Set rstFinal = MyDB.OpenRecordset("tblConsolidated", dbOpenDynaset)
          
          With rstUniqueUPNs
            Do While Not .EOF
              intNumOfUPNs = DCount("*", "tblOriginal", "[UPN] = " & ![UPN])
              Set rstOrig = MyDB.OpenRecordset("SELECT * FROM tblOriginal WHERE [UPN] = " & ![UPN], dbOpenSnapshot)
                rstOrig.MoveLast: rstOrig.MoveFirst       'Need 'exact' Recordcount
                  Select Case rstOrig.RecordCount
                    Case 1
                      rstFinal.AddNew
                        rstFinal![UPN] = rstOrig![UPN]
                        rstFinal![Res09_1] = rstOrig![Res09]
                        rstFinal![Res08_1] = rstOrig![Res08]
                        rstFinal![YearBuilt_1] = rstOrig![YearBuilt]
                      rstFinal.Update
                    Case 2
                      For intCounter = 1 To intNumOfUPNs
                        varRes09 = varRes09 & rstOrig![Res09] & ";"
                        varRes08 = varRes08 & rstOrig![Res08] & ";"
                        varYrBlt = varYrBlt & rstOrig![YearBuilt] & ";"
                          rstOrig.MoveNext
                      Next
                      rstFinal.AddNew
                        rstFinal![UPN] = ![UPN]
                        rstFinal![Res09_1] = Split(varRes09, ";")(0)
                        rstFinal![Res09_2] = Split(varRes09, ";")(1)
                        rstFinal![Res08_1] = Split(varRes08, ";")(0)
                        rstFinal![Res08_2] = Split(varRes08, ";")(1)
                        rstFinal![YearBuilt_1] = Split(varYrBlt, ";")(0)
                        rstFinal![YearBuilt_2] = Split(varYrBlt, ";")(1)
                      rstFinal.Update
                    Case 3
                      For intCounter = 1 To intNumOfUPNs
                        varRes09 = varRes09 & rstOrig![Res09] & ";"
                        varRes08 = varRes08 & rstOrig![Res08] & ";"
                        varYrBlt = varYrBlt & rstOrig![YearBuilt] & ";"
                          rstOrig.MoveNext
                      Next
                      rstFinal.AddNew
                        rstFinal![UPN] = ![UPN]
                        rstFinal![Res09_1] = Split(varRes09, ";")(0)
                        rstFinal![Res09_2] = Split(varRes09, ";")(1)
                        rstFinal![Res09_3] = Split(varRes09, ";")(2)
                        rstFinal![Res08_1] = Split(varRes08, ";")(0)
                        rstFinal![Res08_2] = Split(varRes08, ";")(1)
                        rstFinal![Res08_3] = Split(varRes08, ";")(2)
                        rstFinal![YearBuilt_1] = Split(varYrBlt, ";")(0)
                        rstFinal![YearBuilt_2] = Split(varYrBlt, ";")(1)
                        rstFinal![YearBuilt_3] = Split(varYrBlt, ";")(2)
                      rstFinal.Update
                    Case Else
                  End Select
                    varRes09 = Null
                    varRes08 = Null
                    varYrBlt = Null
                .MoveNext
            Loop
          End With
          
          rstFinal.Close
          rstOrig.Close
          rstUniqueUPNs.Close
          Set rstFinal = Nothing
          Set rstOrig = Nothing
          Set rstUniqueUPNs = Nothing
          
          DoCmd.OpenTable "tblConsolidated", acViewNormal, acReadOnly
          DoCmd.Maximize
          P.S. - Any questions, feel free to ask.
          Attached Files

          Comment

          • stateemk
            New Member
            • Aug 2009
            • 62

            #6
            Thanks so much for all your help.

            Comment

            • dsatino
              Contributor
              • May 2010
              • 393

              #7
              What's the necessity of making the UPN field a primary key? The original table has a much easier structure to work with than your intended result table.

              Comment

              Working...