Solved Crosstab query report - Column order (1 Viewer)

Nagesh

Member
Local time
Tomorrow, 00:16
Joined
May 10, 2020
Messages
31
I am trying to create a report out of crosstab query where ;
a. Person as row headings
b. Category as column heading
c. Sum of Amount as value
d. and to obtain Sum (by row), Sum of amount as Row heading

Report generated from this crosstab query, is putting the 'Total' (row sum) in the second column rather than the last column (see attachments)
In the Report_Open -- > I have used recordset in vba to auto-populate the columns based on the number of Categories

Need help with the code to force Total (sum of amount) in the last column

Code:
Private Sub Report_Open(Cancel As Integer)

    Dim i As Integer
    Dim StrName As String

 
    On Error Resume Next
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter
    Set qdf = CurrentDb.QueryDefs("qryCrossTab")

    For Each prm In qdf.Parameters
        prm = Eval(prm.name)
    Next prm
    Set rst = qdf.OpenRecordset

    intcolCount = rst.Fields.Count
    intControlCount = Me.Detail.Controls.Count
    rst.Fields!Total.OrdinalPosition = intcolCount
       If intControlCount < intcolCount Then
        intcolCount = intControlCount
    End If
 

    '  Fill in information for the necessary controls.
    For i = 4 To intcolCount
        StrName = rst.Fields(i - 1).name
        Strval = rst.Fields(i - 1).Value
        Me.Controls("lblPgHdr" & i).Caption = StrName
        Me.Controls("tbxData" & i).ControlSource = StrName
    Next i
    '  Close the recordset.
   
    rst.Close
End Sub
 

Attachments

  • CrossTab query.JPG
    CrossTab query.JPG
    57.3 KB · Views: 429
  • CrossTab Report.JPG
    CrossTab Report.JPG
    58.4 KB · Views: 271

theDBguy

I’m here to help
Staff member
Local time
Today, 14:16
Joined
Oct 29, 2018
Messages
21,358
Hi. Just for fun, try opening your crosstab query from the Nav Pane. If the Totals column is on the left, try selecting the entire column and drag it all the way to the right. Close and save the query and open it again to verify if the Totals column stayed on the right. If so, try opening your report.
 

Nagesh

Member
Local time
Tomorrow, 00:16
Joined
May 10, 2020
Messages
31
Hi. Just for fun, try opening your crosstab query from the Nav Pane. If the Totals column is on the left, try selecting the entire column and drag it all the way to the right. Close and save the query and open it again to verify if the Totals column stayed on the right. If so, try opening your report.
Thanks for your response
I tried shifting 'total' field to right most in cross tab query
But report still shows the total field somewhere in the middle
 

isladogs

MVP / VIP
Local time
Today, 21:16
Joined
Jan 14, 2017
Messages
18,186
Did you drag the column in design view or results view? Did you save afterwards?
 

Nagesh

Member
Local time
Tomorrow, 00:16
Joined
May 10, 2020
Messages
31
Did you drag the column in design view or results view? Did you save afterwards?
I tried dragging both in design view and result view (separately) and I saved it also.
But no change in report
 

Micron

AWF VIP
Local time
Today, 17:16
Joined
Oct 20, 2018
Messages
3,476
I'm a bit confused by those suggestions. If you create a report, whether or not it was manual, wizard, whatever - from a ct query, why would you expect the position of the report controls to move because the position of the query fields was changed? A report is not like a datasheet, right? I'd expect the columns of a datasheet view to move based on the recordsource field order, but not a report. Am I wrong about how a report will behave based on the position of its controls?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:16
Joined
Oct 29, 2018
Messages
21,358
I'm a bit confused by those suggestions. If you create a report, whether or not it was manual, wizard, whatever - from a ct query, why would you expect the position of the report controls to move because the position of the query fields was changed? A report is not like a datasheet, right? I'd expect the columns of a datasheet view to move based on the recordsource field order, but not a report. Am I wrong about how a report will behave based on the position of its controls?
Hi. I was "hoping" the positions of the columns in the Report would move because the OP is using code in the Open event to assign the Control Source from the Crosstab query. So, I thought the code was simply going in order, and by moving the Totals column, I was trying to see if it will do the job. I guess not... I don't understand why, though.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:16
Joined
Oct 29, 2018
Messages
21,358
I tried dragging both in design view and result view (separately) and I saved it also.
But no change in report
Hi. My guess is you may have to amend your code to "skip" the Totals column until the end. Just a thought...
 

Micron

AWF VIP
Local time
Today, 17:16
Joined
Oct 20, 2018
Messages
3,476
I don't understand why, though.
Because once a report is created, the placement of the controls is fixed? You would have to build this report again, after moving the query fields - assuming it was built with a wizard, or drag the report control to the desired place in design view. I hope the OP realizes what happens to a report based on a normal ct query when the number of fields in the query changes because of changes in the data. You end up with errors and/or problems when the number of report controls and the number of query fields don't match unless you take steps to prevent that.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:16
Joined
Oct 29, 2018
Messages
21,358
Because once a report is created, the placement of the controls is fixed? You would have to build this report again, after moving the query fields - assuming it was built with a wizard, or drag the report control to the desired place in design view. I hope the OP realizes what happens to a report based on a normal ct query when the number of fields in the query changes because of changes in the data. You end up with errors and/or problems when the number of report controls and the number of query fields don't match unless you take steps to prevent that.
@Micron. I guess we're not on the same page. I understand the placement of the textbox controls on the report may be fixed, but the Control Source can be moved around. This is a very common approach for creating a dynamic report based on Crosstab Queries. Because a Crosstab Query can have varying number of columns, the usual approach is to dynamically assign (or map) each textbox to a column returned by the Crosstab query. It's the same approach for creating Calendar Forms, because each month could have a different number and placement for dates.

I hope the OP realizes what happens to a report based on a normal ct query when the number of fields in the query changes because of changes in the data. You end up with errors and/or problems when the number of report controls and the number of query fields don't match unless you take steps to prevent that.
And I think that "step" is in the OP's original post, which was the code in the Report's Open Event.
 

Nagesh

Member
Local time
Tomorrow, 00:16
Joined
May 10, 2020
Messages
31
Hi. My guess is you may have to amend your code to "skip" the Totals column until the end. Just a thought...
I did just that and using controls, i've managed to place the total column towards the end
Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:16
Joined
Oct 29, 2018
Messages
21,358
I did just that and using controls, i've managed to place the total column towards the end
Thanks
Hi. Congratulations! Glad to hear you got it sorted out. We were all happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom