If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting Xtab Column Headings
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 |
#2
|
|||
|
|||
Sorting Xtab Column Headings
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "croy" wrote: 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 . |
#3
|
|||
|
|||
Sorting Xtab Column Headings
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 wrote: 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 |
#4
|
|||
|
|||
Sorting Xtab Column Headings
On Wed, 16 Dec 2009 12:29:02 -0800, Jerry Whittle
wrote: 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 |
#5
|
|||
|
|||
Sorting Xtab Column Headings
On Wed, 16 Dec 2009 15:42:21 -0500, John Spencer
wrote: 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. -- croy |
Thread Tools | |
Display Modes | |
|
|