Sort Row Headings for Crosstab Query

[wembleybear]

Hi

I have a created a crosstab query which gives me the results I need, but I want to sort the row headings differently. These are not numbers, but machine sizes which range from 4 Metre to 20 Metre. Currently, the crosstab gives me:

10 Metre
12 Metre
14 Metre
17 Metre
20 Metre
3 Metre
4 Metre
5 Metre etc

But I need to show it as:

3 Metre
4 Metre
5 Metre
10 Metre
12 Metre
14 Metre
17 Metre etc.

The field is short text data type and data comes from an ODBC linked table to SQL server table, and I am using Access 2013.

What is the best way to achieve this for my query?

Many thanks
Martyn

 

[CJ_London]

the problem is that '3 Metre' is text which sorts differently to numbers

You need to create another column with a numeric value of 10, 3 etc and sort on that

if the original value is '3 Metre' i.e. the number always appears first then you can use the val function to grab the number part of a text value

val("3 Metre")=3
val("10 Metre")=10

 

[bob fitz]

You could use:
SELECT YourTableName.YourFieldName
FROM YourTableName
ORDER BY CInt(Left([YourFieldName],2));

Will work provided no figure exceeds 99 and there is always a space between the number and "Metre".

Of course you will need to substitute YourTableName and YourFieldName

 

[plog]

Because those fields are text, it is sorting your columns as text. So you either need to convert that data to numbers, or make it so that they order correctly as text.

To convert to numbers, you would drop the Metre part and use Cint (http://www.techonthenet.com/access/functions/datatype/cint.php).

To convert to properly ordered text you'd probably need another table that lists all the distinct values and then adds characters to have them order correctly:

CurrentValue, SortedValue
1 Metre, 001 Metre
2 Metre, 002 Metre
...
12 Metre, 012 Metre
...
438 Metre, 438 Metre

 

[wembleybear]

Thanks all for your very useful suggestions, and following Bob's has made this work for me.

Martyn