Derek Brown
asked on
Correct ordering
Hi All
Example: I have data in a field like this:
adesc
AA001162
AAAAAAA
AABBBBB
AAZZZZZ
AA12345
B1.2
B1.12
C10
C2
D10
D2
I want it to be sorted as this:
adesc
AAAAAAA
AABBBBB
AAZZZZZ
AA001162
AA12345
B1.12
B1.2
C10
C2
D10
D2
Example: I have data in a field like this:
adesc
AA001162
AAAAAAA
AABBBBB
AAZZZZZ
AA12345
B1.2
B1.12
C10
C2
D10
D2
I want it to be sorted as this:
adesc
AAAAAAA
AABBBBB
AAZZZZZ
AA001162
AA12345
B1.12
B1.2
C10
C2
D10
D2
Isn't this the correct required output?
adesc
AA001162
AA12345
AAAAAAA
AABBBBB
AAZZZZZ
B1.2
B1.12
C2
C10
D2
D10
adesc
AA001162
AA12345
AAAAAAA
AABBBBB
AAZZZZZ
B1.2
B1.12
C2
C10
D2
D10
ASKER
I will try that Gustav
Hnasr
Actually that would more than suffice.
Ideally
B1.2 should come after
B1.12
But what you have is much better than what I get
Hnasr
Actually that would more than suffice.
Ideally
B1.2 should come after
B1.12
But what you have is much better than what I get
Input:
Table a (aid, adesc)
aID adesc
01 AA001162
02 AAAAAAA
03 AABBBBB
04 AAZZZZZ
05 AA12345
06 B1.2
07 B1.12
08 C10
09 C2
10 D10
11 D2
Required Output:
aID adesc
01 AA001162
05 AA12345
02 AAAAAAA
03 AABBBBB
04 AAZZZZZ
06 B1.2
07 B1.12
09 C2
08 C10
11 D2
10 D10
Process:
Adding a function in modules.
Convert each numeric piece to a 10 digit string by adding 0's to beginning of number.
Rejoin strings and sort.
Query:
Table a (aid, adesc)
aID adesc
01 AA001162
02 AAAAAAA
03 AABBBBB
04 AAZZZZZ
05 AA12345
06 B1.2
07 B1.12
08 C10
09 C2
10 D10
11 D2
Required Output:
aID adesc
01 AA001162
05 AA12345
02 AAAAAAA
03 AABBBBB
04 AAZZZZZ
06 B1.2
07 B1.12
09 C2
08 C10
11 D2
10 D10
Process:
Adding a function in modules.
Convert each numeric piece to a 10 digit string by adding 0's to beginning of number.
Rejoin strings and sort.
Query:
SELECT a.aID, a.adesc
FROM a
ORDER BY finedAlphaNumeric([adesc]);
sort-alpha-numeric.accdb
A solution is not possible if you neither can define nor decide how to sort.
/gustav
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav
You cannot decide what sorting system you need when you do not know and when your customer does not know what he is going to get from his customer. It is simply a case of getting the best guess. Or perhaps just something better than the standard access stuff.
Hnasr
I cannot open your file I am still on Access 2002. Can you convert it please.
Dale
I will give that a try. Rushing to finish something this week!!!
You cannot decide what sorting system you need when you do not know and when your customer does not know what he is going to get from his customer. It is simply a case of getting the best guess. Or perhaps just something better than the standard access stuff.
Hnasr
I cannot open your file I am still on Access 2002. Can you convert it please.
Dale
I will give that a try. Rushing to finish something this week!!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is simply a case of getting the best guess.
Well, then my straight solution is the best, simple as it is. It fulfills your example data and sorting.
/gustav
My comment was based on this challenging requirement:
https://www.experts-exchange.com/questions/28979884/Orderby-function.html?anchorAnswerId=41867276#a41867276
Although it handles the simple ABC123 entry, I may assume entries like,
adesc
A1B2C3D
A01X
A2
2A
2A10
A10
Which sorts to:
adesc
2A
2A10
A1B2C3D
A01X
A2
A10
customer can enter door numbers with any alpha numeric combination, inc decimal points anywhere. So writing code or a function may be too much
https://www.experts-exchange.com/questions/28979884/Orderby-function.html?anchorAnswerId=41867276#a41867276
Although it handles the simple ABC123 entry, I may assume entries like,
adesc
A1B2C3D
A01X
A2
2A
2A10
A10
Which sorts to:
adesc
2A
2A10
A1B2C3D
A01X
A2
A10
ASKER
Brilliant Guys. Thank you
ASKER
How do I sort using these functions as "Desc"
ARe you talking to me?
ORDER BY StripTrailingNumbers([your Field]), TrailingNumbers([yourField ]) DESC
or if you want the Alpa part descending as well
ORDER BY StripTrailingNumbers([your Field]) DESC, TrailingNumbers([yourField ]) DESC
ORDER BY StripTrailingNumbers([your
or if you want the Alpa part descending as well
ORDER BY StripTrailingNumbers([your
Descending order:
-sort-alpha-numeric-2.mdb
SELECT a.aID, a.adesc, findAlphaNumeric([adesc])
FROM a
ORDER BY findAlphaNumeric([adesc]) DESC;
Modified function to properly sort entries such as 01X, and 1A .-sort-alpha-numeric-2.mdb
ASKER
Brilliant. Thank you.
Derek
Derek
Welcome!
Order By
Left([DoorCode], 2),
Val("9" & Mid([DoorCode], 3))
/gustav