How to search in an array and merge the index in the array as one in Access?

[xboxown]

Before I ask the question let me post the data here (in case people ask for it):

Density	Part	Unit	Price	Board Feet	Description	Rank
ABC	ABC	ea	9.27	8.9618	ABC - 123
ABC	ABC	ea	40.23	16.5	DEF - 456
ABC	ABC	ea	40.23	8.1736	GHI - 789
ABC	ABC	ea	1.17	0.7031	JKL - 999
ABC	ABC	ea	5.17	2.8141	MNO - UUU

This is the code below

Private Sub cmbParents_AfterUpdate()
On Error GoTo err_
    Dim strSQL As String
    Dim RS As Recordset
    Dim lngNext As Long
    lngCounter = 0
    ' First delete all the contents in the preview table
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL "DELETE FROM tblComponentsFromPSIToAccessPreview"
    
    strSQL = "SELECT Bill, MaterialDescription, BillDescription, REPLACE(Item,'BLOCK',''), BoardFeet, [Item], [So Price] FROM tblComponentsFromPSIToAccess WHERE [create_child_wo]=0 And Parent='" & cmbParents.Column(0, cmbParents.ListIndex) & "' AND Parent IS NOT NULL AND Parent<>'' AND SFC_Disc=''"
    Set RS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
     
    If RS.RecordCount > 0 Then
       RS.MoveFirst
       While RS.EOF = False
'            DoCmd.RunSQL "INSERT INTO tblComponentsFromPSIToAccessPreview(StockID, PartNumber, Description, Unit, Price, BoardFeet) VALUES ('" & cmbParents.Column(0, cmbParents.ListIndex) & "','" & cmbParents.Column(0, cmbParents.ListIndex) & "','" & RS.Fields(3).Value & "/" & RS.Fields(1).Value & " - " & RS.Fields(2).Value & "', 'ea'," & RS.Fields(6).Value & "," & RS.Fields(4).Value & ")"
          ' Store the information in the array first
          ReDim Preserve arrayDensity(lngCounter)
          ReDim Preserve arrayPart(lngCounter)
          ReDim Preserve arrayUnit(lngCounter)
          ReDim Preserve arrayPrice(lngCounter)
          ReDim Preserve arrayBoardFeet(lngCounter)
          ReDim Preserve arrayDescription(lngCounter)
          arrayDensity(lngCounter) = cmbParents.Column(0, cmbParents.ListIndex)
          arrayPart(lngCounter) = cmbParents.Column(0, cmbParents.ListIndex)
          arrayUnit(lngCounter) = "ea"
          arrayPrice(lngCounter) = RS.Fields(6).Value
          arrayBoardFeet(lngCounter) = RS.Fields(4).Value
          arrayDescription(lngCounter) = "" & RS.Fields(3).Value & " / " & RS.Fields(1).Value & " - " & RS.Fields(2).Value & ""
          lngCounter = lngCounter + 1
          RS.MoveNext
       Wend
    End If
    
    For lngNext = 0 To lngCounter
        
    Next lngNext
    
    Refresh
    Exit Sub
err_:
  Call MsgBox(Err.Description, vbCritical)
End Sub

These arrays below store all the selected data from the database as shown in the grid above:

ReDim Preserve arrayDensity(lngCounter)
ReDim Preserve arrayPart(lngCounter)
ReDim Preserve arrayUnit(lngCounter)
ReDim Preserve arrayPrice(lngCounter)
ReDim Preserve arrayBoardFeet(lngCounter)
ReDim Preserve arrayDescription(lngCounter)

What I want to do is using this loop code down below to look for the price if they are the same. If they are the same then we merge the two lines in the array as one line and we add the boardfeet together into one total boardfeet in one line (regardless if there are 10 duplicate prices in the array) and then delete all the other duplicates in the array and keep the one array that have merged two or more one lines into one array:

For lngNext = 0 To lngCounter

Next lngNext

An example of what I am talking about if you noticed above in the grid there is 40.23 twice right? In the following arrays (it could be there are 10 items that are duplicate..not just 2):
arrayDensity(0) = ABC
arrayPart(0) = ABC
arrayUnit(0) = ea
arrayPrice(0) = 40.23
arrayBoardFeet(0) = 16.5
arrayDescription(0) = DEF - 456

arrayDensity(1) = ABC
arrayPart(1) = ABC
arrayUnit(1) = ea
arrayPrice(1) = 40.23
arrayBoardFeet(1) = 8.1736
arrayDescription(1) = GHI - 789

It would combine index 0 and index = 1 as one and place the new result into index 0. So it would look like this:

arrayDensity(0) = ABC
arrayPart(0) = ABC
arrayUnit(0) = ea
arrayPrice(0) = 40.23
arrayBoardFeet(0) = 24.6736
arrayDescription(0) = DEF & GHI - 456 & 789

arrayDensity(1) = ABC
arrayPart(1) = ABC
arrayUnit(1) = ea
arrayPrice(1) = 40.23
arrayBoardFeet(1) = 8.1736
arrayDescription(1) = GHI - 789

Now that I have merged these two together I want to destroy index 1 of the array and resize the array after I have deleted index 1. I will be left with this:

arrayDensity(0) = ABC
arrayPart(0) = ABC
arrayUnit(0) = ea
arrayPrice(0) = 40.23
arrayBoardFeet(0) = 24.6736
arrayDescription(0) = DEF & GHI - 456 & 789

How do I go about doing that? Remember there could be 50 duplicate prices not necessarily just 2 and this is going to be done through loop.

[Micron]

What's the first table? Source data or desired result? Seems like the result:

These arrays below store all the selected data from the database as shown in the grid above

Are you sure you want/need to do something so complex rather than just a cross tab query? It's kinda mind boggling. I'm having difficulty believing it has to be so complex at the moment.

[orange]

How about telling readers in simple English --what you want to achieve?

What's with the arrays and loops and How you will do something.

Why not simply describe the issue and what you want as a result?

Before people can tell you HOW to do something, they will want to ensure they understand WHAT it is you are trying to do. It isn't easy to work backwards and guess correctly, consistently.
It sounds like you're trying to reduce the number of records in inventory. Those records where prices are same, combine the quantities (board feet) into a single record, then delete those original records.....

Perhaps you have a clearer description and purpose for this exercise that you could share with readers. I'm sure someone will ask why do this.

[xboxown]

You are right Orange. It is 5:27 PM now..and I have to go home. As soon as I arrive home I will describe to you in detail the reason behind this madness. But if this is something that can be done in a query...I am more happy to go that approach. I just don't know the approach is all...