Sorting by Totals in a Crosstab Query

UBSDev

New Member
Joined
Apr 25, 2006
Messages
26
I have a cross-tab query with a column heading that produces a dozen or so columns.

I can sort the column heading alphabetically it seems, but I want to be able to sort the column headings in descending order of the total of each column...there must be a simple way to do this, although I can't seem to figure it out...
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Off the top of my head, in-other-words, not much thought into this.
Run a query that will give you the totals your cross-tab query will give you, but in separate records. Make sure the query is sorted descending order by the totals. Then read that query, getting what will be your column headings in the order you will want them for this running of the crosstab query. Then, place these headings, in your correct order, into the ColumnHeadings property of the QueryDef.
HTH,
 
Upvote 0
Thanks...I was working along those lines; although I'm passing variables through this cross-tab query and running it several times through code...I need to find a way to automate the changing of the column headings property.

Anyone know how I would convert this table:

Field1
A
B
C
D
E

into a string: "A", "B", "C", "D", "E" ?
 
Upvote 0
I'm going to assume you know how to set up a recordset in code. I'll use "rs" to be the recordset.
Code:
strMyString = ""   'Initialize strMyString to zero length.
strComma = ""  'Initialize strComma to zero length.
While Not rs.EOF
  strMyString = strMyString & strComma & """" & rs!Field1 & """"
  rs.movenext
  strComma = ","
WEnd
 
Upvote 0
Thanks Vic...once I have that string, how do I pass it into the 'column headings' property in my query? Or do I need to write the query in VB as well?
 
Upvote 0
You will need a querydef object variable.
Dim qrydef as QueryDef
Set qrydef = Currentdb().QueryDefs("MyQuery")

Then you can set the ColumnHeadings by changing the SQL property of the qrydef. The end of the SQL for a CrossTab query is the PIVOT phrase. The first line below is a PIVOT phrase without column headings assigned, and the second line below is a PIVOT phrase WITH the column headings assigned. So, just adding In (x,x,x,x,x,x,x) just before the semi-colon ( ; ) will assign your column headings. I just used a table that currently has 7 batches assigned, therefore the numbers 1 through 7. The order of your columns will be from left to right.

PIVOT tblCommissionPaid.BatchNumber;
PIVOT tblCommissionPaid.BatchNumber In (7,6,5,4,3,2,1);

HTH,
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top