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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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,
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,374
Members
410,911
Latest member
AniEx
Top