CODEWITHSUNDEEP
sqlms-access
Kevin
Kevin

Reputation: 41

Sorting data in Access database where the column has numbers and letters

Please help me because I have been unable to get this right.

What is the access SQL to select this column(columnA) so that it returns a resultset with distinct values sorted first according to numbers and then to letters.

Here is the columns values: {10A,9C,12D,11G,9B,10C,9R,8T}

I have tried 'Select distinct ColumnA from tblClass order by 1' but it returns {10A,10C,11G,12D,8T,9B,9C,9R} which is not what I want.

Thank you in advance.

Upvotes: 4

Views: 3664

Answers (5)

Sippe
Sippe

Reputation: 1

I had a similar problem and used a dummie workaround:

changing a list of {10A,10C,11G,12D,8T,9B,9C,9R}

into {10A,10C,11G,12D,08T,09B,09C,09R} by adding the 0 before each <10 number.

now all items are the same length and access will sort correctly into {08T, 09B, 09C, 09R, 10A, 10C, 11G, 12D} .

To achieve this, I copied this column into excel column A and used IF(LEN(A2)<3, concatenate("0", A2))

Upvotes: -1

HansUp
HansUp

Reputation: 97101

You can use the Val() function for this. From the help topic: "The Val function stops reading the string at the first character it can't recognize as part of a number"

Val(10A) will give you 10, Val(9C) will give you 9, and so on. So in your query, order by Val(ColumnA) first, then ColumnA.

SELECT DISTINCT Val([ColumnA]) AS number_part, ColumnA
FROM tblClass
ORDER BY Val([ColumnA]), ColumnA;

Upvotes: 5

Jacob
Jacob

Reputation: 43229

SELECT DISTINCT ColumnA
FROM tblClass 
ORDER BY CInt(LEFT(ColumnA,len(ColumnA)-1)), RIGHT(ColumnA,1);

If there last character is a letter and the others are a number.

Upvotes: 1

Tory Netherton
Tory Netherton

Reputation: 753

order by 1?

Don't you mean order by ColumnA?

SELECT DISTINCT ColumnA
FROM tblClass
ORDER BY ColumnA

Upvotes: 0

OTTA
OTTA

Reputation: 1081

Your data type is a string so it's sorting correctly, to get the result you want you're going to have to split your values into numeric and alphabetic parts and then sort first on the numeric then the alphabetic. Not being an Access programmer I can't help you with exactly how you're going to do that.

Upvotes: 0

Related Questions