Sorting Xtab Column Headings

[croy]

For this SQL:

TRANSFORM Sum(Final.SumOfMonthlySppeffort) AS SumOfEffort
SELECT Final.Months
FROM Final
GROUP BY Final.Months, Final.Years, Final.Months
ORDER BY Final.Years, Final.Months
PIVOT Format([LocSort],"0.0")
WITH OWNERACCESS OPTION;

I get just what I want, with the exception of the order of
the columns. The column headings are (should be) numbers
like:

1; 1.1; 2; 7; 10; 10.1; 17; 19 [up to 20]

But in the query results, any with a decimal point show
like:

1_1; 10_1 [etc.]

And they are not in numerical order, but rather like an
alpha sort:

10_1; 17; 2; 20;

If I don't use the Format function on the column header
"LocSort", I get numbers like:

9.9999999973; 10_100003; [etc.]

I'd really like to keep this in a query output, as opposed
to going to a form or report (various reasons).

Is there a way to get these columns sorted, with the numbers
formatted like the actual data, and with decimal points
instead of underscores?

Thanks
croy

 

[Jerry Whittle]

One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'1','1.1','2','7','10','10.1','17','19'

The above should match the expected data. You can also make data not show up
by taking out a column. For example, if you remove 17 then that data won't
show. If you put in 18, it will create an empty column if you don't have any
matching data.

Of course this only works if you always know what your column headings need
to be.

 

[John Spencer]

I don't think you can get the numbers formatted with a decimal point since
these are column names and column names cannot have a decimal in them. You
MIGHT be able to use one of the following - just a guess.

PIVOT Format([LocSort],"\[00.0\]")

or

PIVOT Round([LocSort],1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[croy]

One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'1','1.1','2','7','10','10.1','17','19'

The above should match the expected data. You can also make data not show up
by taking out a column. For example, if you remove 17 then that data won't
show. If you put in 18, it will create an empty column if you don't have any
matching data.

Of course this only works if you always know what your column headings need
to be.

Thanks for the reply, Jerry.

I tried the same, but without the quote marks. Either way,
Access (2k) translates the decimal points to underscores.
Not a show-stopper, just a little irritating.

 

[croy]

I don't think you can get the numbers formatted with a decimal point since
these are column names and column names cannot have a decimal in them. You
MIGHT be able to use one of the following - just a guess.

PIVOT Format([LocSort],"\[00.0\]")

or

PIVOT Round([LocSort],1)

Thanks John.

I tried each of those. The first gave column headings like
_00_5_0, and the second gave the same as I'm getting: 10_1,
which isn't terrible, it just means the boss will have to
retype a few characters on his "quick and dirty" reports.