How do I combine multiple 1D arrays and create a table (rows, columns) like a 2D array using a loop? (1 Viewer)

[devtr]

How do I combine multiple 1D arrays and create a table (rows, columns) like a 2D array using a loop?

Public Function GetCombineArray() As Variant

Dim ColA() As Variant, Row1() As Variant, Row2() As Variant, Row3() As Variant
Dim NewTable() As Variant, RowInputs() As Variant
Dim iC As Integer, jC As Integer

ReDim ColA(2)
ColA(0) = "Year"
ColA(1) = 2020
ColA(2) = 2021

ReDim Row1(2)
Row1(0) = "Purchase"
Row1(1) = 1000
Row1(2) = 1500

ReDim Row2(2)
Row2(0) = "Sales"
Row2(1) = 3000
Row2(2) = 3500

ReDim Row3(2)
Row3(0) = "Expences"
Row3(1) = 500
Row3(2) = 1000



ReDim NewTable(0 To 3, 0 To 2)

'NewTable(0, 0) = ColA(0)
'NewTable(0, 1) = ColA(1)
'NewTable(0, 2) = ColA(2)
'NewTable(0, 3) = ColA(3)'.....

RowInputs = Array(ColA, Row1, Row2, Row3)

For iC = 0 To 3

    For jC = 0 To 2
    
        NewTable = RowInputs(iC, jC)
        
    Next jC
    
Next iC

GetCombineArray = NewTable

Of course, this loop will not work, as I am missing so many corrections and logic.
I do not want to save this to the tables and use crosstab queries.

I am trying to create a table like 2D array:

Year 2020 2021

Purchase 1000 1500
Sales 3000 3500
Expences 500 1000


And, I don't want to explicitly assign the values to each element. In my real project, that won't be a practical thing to do.

Please help. I really appreciate your knowledge and time.

 

[June7]

Cross-post https://www.utteraccess.com/topics/2063177/posts/2798090

 

[The_Doc_Man]

You don't want to use crosstab queries AND you don't want to have to explicitly assign the values to each element. Is there a separate data source such as a text file or another table or an existing spreadsheet? Because your first two "don't want to..." statements eliminated the two most common methods that would be used to do what you seem to be describing. Without some external data source to drive this, you are going to HAVE to accept one or the other of the things you claim to not want. Last I looked, Access was not telepathic.

Not to mention that the apparent structure you are trying to make will cause you headaches, as Access REALLY doesn't like having an all-numeric name for a field, and that is what you are apparently trying to do for the numbered "year" fields. Forget the field-name brackets just once and you will hate yourself forever for making that mistake. AND it will be a bear to root out.

My first-impression answer is that EITHER you have a complex school assignment OR this is work and your totally unreasonable boss gave you the impossible to do so s/he can fire you for non-compliance. But most bosses don't know enough to make that kind of requirement. So... is this for some class?

EDIT: We frown on unannounced cross-posting. It is a waste of people's time. Shopping around for a better answer might seem easy for you - but it is not at all easy for those of us who volunteer our time. Your other thread included suggestions for a cross-tab or a particular web site that holds some interesting utilities. What was wrong with those answers?

 

[devtr]

I appreciate June7 and The_Doc_Man. Thank you. I am not in the right direction.

 

[MarkK]

You could create a disconnected ADODB.Recordset as outlined in this post https://www.access-programmers.co.u...ynamic-table-slow-to-run.322738/#post-1821496

 

[arnelgp]

Public Function ConsolidateArray(ParamArray p() As Variant) As Variant
    'arnelgp
    'passed parameter should array(s) with same single dimension
    Dim vArr() As Variant, v As Variant
    Dim allOK As Boolean
    Dim max As Integer, j As Integer, k As Integer
    ConsolidateArray = Null
    allOK = True
    For Each v In p
        If VarType(v) <> vbArray And VarType(v) <> 8204 Then
            allOK = False
            Exit For
        End If
        'determine the maximum rows on each array
        If UBound(v, 1) > max Then
            max = UBound(v)
        End If
    Next v
    If Not allOK Then
        Exit Function
    End If
    ReDim vArr(0 To max, 0 To UBound(p))
    For j = 0 To UBound(vArr, 1)
        For k = 0 To UBound(vArr, 2)
            vArr(j, k) = p(k)(j)
            'Debug.Print "vArr(" & j & ", " & k & ")", vArr(j, k)
                    
        Next
    Next
    ConsolidateArray = vArr
End Function


Private Sub test()
Dim ColA() As Variant, Row1() As Variant, Row2() As Variant, Row3() As Variant
Dim NewTable As Variant
Dim iC As Integer, jC As Integer

ReDim ColA(2)
ColA(0) = "Year"
ColA(1) = 2020
ColA(2) = 2021

ReDim Row1(2)
Row1(0) = "Purchase"
Row1(1) = 1000
Row1(2) = 1500

ReDim Row2(2)
Row2(0) = "Sales"
Row2(1) = 3000
Row2(2) = 3500

ReDim Row3(2)
Row3(0) = "Expences"
Row3(1) = 500
Row3(2) = 1000



NewTable = ConsolidateArray(ColA, Row1, Row2, Row3)
For iC = 0 To UBound(NewTable, 1)
    For jC = 0 To UBound(NewTable, 2)
        Debug.Print "NewTable(" & iC & ", " & jC & ")", NewTable(iC, jC)
    Next
    Debug.Print
Next
End Sub

 

[MajP]

I got no idea of what and why you are doing. However, if you are trying to expand your array skills here is a great module for doing lots of things with arrays in vba. Recommend copying and storing it in a template database.

VBA Arrays