Consolidate data from multiple rows into 1 row??

[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.

[ADezii]

@stateemk
What is the Maximum Number of Duplicates that can exist in the Original Table for a given UPN?

[stateemk]

There is no max.

[ADezii]

@stateemk
That significantly complicates matters but I'll see what I can do.

[ADezii]

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.

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.

[stateemk]

Thanks so much for all your help.

[dsatino]

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.