Solved Crosstab query report - Column order

[Nagesh]

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

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

 

[theDBguy]

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]

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]

Did you drag the column in design view or results view? Did you save afterwards?

 

[Nagesh]

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

 

[Deleted member 147267]

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

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

I guess we're not on the same page.

OK, I'm going to bow out.

 

[Nagesh]

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