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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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,
 

UBSDev

New Member
Joined
Apr 25, 2006
Messages
26
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" ?
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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
 

UBSDev

New Member
Joined
Apr 25, 2006
Messages
26
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?
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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,
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,130
Messages
5,857,550
Members
431,885
Latest member
Rsdg

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
Top