Unique question about crosstab queries...

[ChaseC]

Hi All,

I'm in a bit of a predicament. I have a very specific need to change this table:



To this table:





Basically, a crosstab table with:

• the rows being in the following pattern: 0, three repeats of the first percentage, three repeats of the running sum percentage, etc. etc.,
• The data being two repeats of the column header's multiple followed by a zero for whichever percent chunk it counts in

So far, I've been able to use a crosstab query (code below) to create this:

TRANSFORM Sum(qryGraphTest.investmentMultiple) AS SumOfinvestmentMultiple
SELECT qryGraphTest.percentOfInvestmentEquity
FROM qryGraphTest
GROUP BY qryGraphTest.percentOfInvestmentEquity
PIVOT qryGraphTest.investmentName;

A few problems however:

• Not sure how to get repeating rows
• Not sure how to have the rows be a running sum rather than actual percentage
• Repeating the actual data points and inserting a blank row between them

I'm at a loss as to the best way to go about this.. I'm sure I can cook something up in VBA to create a temp table that mimics the one above but it will include a ton of loops and reiteration and I'm sure it's not the best way to go about it.

[kd2017]

I'm at a loss as to the best way to go about this.. I'm sure I can cook something up in VBA to create a temp table that mimics the one above but it will include a ton of loops and reiteration and I'm sure it's not the best way to go about it.

I'm a beginner so I won't answer as to the best way to go about this. I just wanted to say that it seems to me that creating the second table from the first table pictured above would be fairly simple in VBA, not too many loops like you've stated.

[kd2017]

Again, I'm a beginner and am not claiming this to be the most efficient or best way to do this. The following code will query for the data in table one and organize it as shown in table two. Just two or three loops.

Public Sub test()
On Error GoTo error_proc
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim x As Integer
    Dim y As Integer
    Dim csum As Double
    Dim row_labels() As Double
    Dim col_labels() As String
    Dim data() As Double

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT investmentName, multiple, pct FROM tbl;")
    
    'csum = 0# 'not necessary

    If Not rs.BOF And Not rs.EOF Then
        rs.MoveLast 'to get rowcount
        rs.MoveFirst
        
        ReDim row_labels(rs.RecordCount * 3 - 2)
        ReDim col_labels(rs.RecordCount - 1)
        ReDim data(rs.RecordCount * 3 - 2, rs.RecordCount - 1) 'data(rows, cols)
        
        row_labels(0) = 0#
        
        For y = 1 To UBound(row_labels) - 3
            csum = csum + rs!pct
            row_labels(y) = csum
            row_labels(y + 1) = csum
            row_labels(y + 2) = csum
            rs.MoveNext
            y = y + 2
        Next y
        
        row_labels(UBound(row_labels)) = csum + rs!pct
        
        rs.MoveFirst
        
        For x = 0 To UBound(data, 2)
            'For y = 0 To UBound(data, 1) '<--- EDIT: not necessary
            '    data(y, x) = 0# '<--- is this necessary or do arrays ALWAYS init to zero??? idk...
            'Next y
            
            col_labels(x) = rs!investmentName
            
            data(x * 3, x) = rs!multiple
            data(x * 3 + 1, x) = rs!multiple
            rs.MoveNext
        Next x
        
        'At this point we now have THREE arrays populated with the requested data
        ' col_labels() = the column headings
        ' row_labels() = the running sum of pct
        ' data(,) = the data organized in (rows, cols)
        ' DO WITH WHAT YOU WILL
        
    End If
    
    rs.Close
    db.Close

exit_proc:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
error_proc:
    Debug.Print "Dude! Like, there was an error, bro."
    Resume exit_proc
End Sub

edit:
according to my super extensive research vba numeric types will default initialize to zero, so that will make my sub a little bit simpler. Everything processed in just 2 loops.
https://stackoverflow.com/questions/...default-values

[ChaseC]

Again, I'm a beginner and am not claiming this to be the most efficient or best way to do this. The following code will query for the data in table one and organize it as shown in table two. Just two or three loops.

Public Sub test()
On Error GoTo error_proc
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim x As Integer
    Dim y As Integer
    Dim csum As Double
    Dim row_labels() As Double
    Dim col_labels() As String
    Dim data() As Double

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT investmentName, multiple, pct FROM tbl;")
    
    'csum = 0# 'not necessary

    If Not rs.BOF And Not rs.EOF Then
        rs.MoveLast 'to get rowcount
        rs.MoveFirst
        
        ReDim row_labels(rs.RecordCount * 3 - 2)
        ReDim col_labels(rs.RecordCount - 1)
        ReDim data(rs.RecordCount * 3 - 2, rs.RecordCount - 1) 'data(rows, cols)
        
        row_labels(0) = 0#
        
        For y = 1 To UBound(row_labels) - 3
            csum = csum + rs!pct
            row_labels(y) = csum
            row_labels(y + 1) = csum
            row_labels(y + 2) = csum
            rs.MoveNext
            y = y + 2
        Next y
        
        row_labels(UBound(row_labels)) = csum + rs!pct
        
        rs.MoveFirst
        
        For x = 0 To UBound(data, 2)
            'For y = 0 To UBound(data, 1) '<--- EDIT: not necessary
            '    data(y, x) = 0# '<--- is this necessary or do arrays ALWAYS init to zero??? idk...
            'Next y
            
            col_labels(x) = rs!investmentName
            
            data(x * 3, x) = rs!multiple
            data(x * 3 + 1, x) = rs!multiple
            rs.MoveNext
        Next x
        
        'At this point we now have THREE arrays populated with the requested data
        ' col_labels() = the column headings
        ' row_labels() = the running sum of pct
        ' data(,) = the data organized in (rows, cols)
        ' DO WITH WHAT YOU WILL
        
    End If
    
    rs.Close
    db.Close

exit_proc:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
error_proc:
    Debug.Print "Dude! Like, there was an error, bro."
    Resume exit_proc
End Sub

edit:
according to my super extensive research vba numeric types will default initialize to zero, so that will make my sub a little bit simpler. Everything processed in just 2 loops.
https://stackoverflow.com/questions/...default-values

Very elegant solution and a much better way of coding it then I had initially thought would be required. Will test and let you know how it works out for me. Thank you so much!

[ChaseC]

Again, I'm a beginner and am not claiming this to be the most efficient or best way to do this. The following code will query for the data in table one and organize it as shown in table two. Just two or three loops.

Ended up working perfectly with a few changes. Thanks!