Crosstab Query column heading order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can I sort the column heading order in the way I want?

for now I have

mth Cust-A Cust-B Cust-C
1 12 33 77
2 33 223 3354
3 11 55 55

Can I have something like

mth Cust-B Cust-A Cust-C
1 33 12 77
2 223 33 3354
3 55 11 55

Thanks
Victor
 
AFAIK, the Column Headings are sorted automatically (and unreliably
sometimes) and you don't have the option to specify the sort order unless
you know the Column Heading text values in advance. In this case, you can
use "Column Headings" Property of the CrossTab Query.

This is useful if you want a CrossTab by month (since you know in advance
that there are only 12 months). However, if you don't know how many
customers and and the customer names, you can't use the "Column Headings"
Property.

Check Access Help on the "Column Headings" Property of CrossTab Queries.
 
In my experience, Access sorts these columns by alphabetical order. In
cases like your's, I'd prepend the customer's name with "1) ", "2) ", etc.
so you have:

Customer Name Rev per year
1) Cust-B $2000
2) Cust-A $1500
3) Cust-C $1000

(If you have more than 9 customers then you'll need to use "01)", "02) ",
etc). You can get this by doing something like:

SELECT
CStr(COUNT(rpy2.*) + 1) & ") " & rpy1.[Customer Name] AS [CNAME],
rpy1.[Rev per year]
FROM
RevPerYear rpy1
LEFT JOIN RevPerYear rpy2 ON
rpy1.[Rev per year] < rpy2.[Rev per year]


Of course, this will break if any 2 or more customers have the same Rev per
year - you'll get the correct sorting but you'll have some duplicates and
some breaks in your "1)", "2)", etc. numbers. But if you're ok with all of
this, then you can build a crosstab like:

mth 1) Cust-B 2) Cust-A 3) Cust-C
1 33 12 77
2 223 33 3354
3 55 11 55
 
Thanks you

Van T. Dinh said:
AFAIK, the Column Headings are sorted automatically (and unreliably
sometimes) and you don't have the option to specify the sort order unless
you know the Column Heading text values in advance. In this case, you can
use "Column Headings" Property of the CrossTab Query.

This is useful if you want a CrossTab by month (since you know in advance
that there are only 12 months). However, if you don't know how many
customers and and the customer names, you can't use the "Column Headings"
Property.

Check Access Help on the "Column Headings" Property of CrossTab Queries.
Click to expand...
 
Back
Top