Query sort order 1

[FoxProProgrammer]

Hello folks,

Here is an example of some data stored in a table:

R1
R4
R2
R3
R12
R11
C1
C2
C3
C22
C21
AR2
AR1

How do I write a Query to return the data sorted as follows:

AR1
AR2
R1
R2
R3
R4
R11
R12
C1
C2
C3
C21
C22

What I get is:

AR1
AR2
R1
R11
R12
R2
R3
R4
C1
C2
C21
C22
C3

Is there an easy way to do this, or am I going to have to write what looks like fairly complex string manipulations that separate the numbers from the characters, sort the numbers, and put the chars and nums back together after they are sorted?

Thanks,

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Oops, sorry!

I want to sort the data as follows:

AR1
AR2
C1
C2
C3
C21
C22
R1
R2
R3
R4
R11
R12


dz
dzaccess@yahoo.com

 

[Remou]

Would something like this:
[tt]SELECT IIf(IsNumeric(Mid([field1],2,1)),Left([field1],1),Left([field1],2)) AS AlphPart, IIf(IsNumeric(Mid([field1],2,1)),Mid([field1],2),Mid([field1],3)) AS NumPart
FROM tblTable
ORDER BY IIf(IsNumeric(Mid([field1],2,1)),Left([field1],1),Left([field1],2)), IIf(IsNumeric(Mid([field1],2,1)),Mid([field1],2),Mid([field1],3));[/tt]
work, or have I missed your point?

 

[Melagan]

Holy smokes...what about this?

SELECT t.yourfield
FROM tblYourTable AS t
ORDER BY t.yourfield;

I made a table with example data above and this query sorted it as needed by FoxPro. Remou's wonderful code definitely splits the alpha and numeric data apart and sorts it out, but do you have to do that? I think the ORDER BY clause will sort alpha-numeric data exactly the way FoxPro wants it to -- or maybe I missed a point myself!




~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Man... Melagan was too fast to post a reply again. My code doesn't do what FoxProProgrammer needs after all.

R1
R11
R12
R2
R3

...alpha-numeric sorting =\

Someone's signature line has a good quote, "A good programmer looks both ways before crossing a one-way street."


~Melagan
______
"It's never too late to become what you might have been.

 

[Remou]

Melagan
It does not work in my version of Access (2000) the R's are ordered so:
R1
R11
R12
R2
R3
R4
Just as FoxProProgrammer found, rather than:
R1
R2
R3
R4
R11
R12

 

[FoxProProgrammer]

Well, thanks to both of you.

As you found out, Access sorts the alphas before the numbers.

So...

R1
R3
R11

sorts as

R1
R11
R3

I tried Remou's Query and get some very odd results.

Sample data:

C1
D1
D2
DS1
DS2
L1
Q1
Q2
R1
R10
R2
R3

sorts as:

C
D
D
DS
DS
L
Q
Q
R
R
R

So, it almost works, but the Query results truncated the numeric part. I could probably figure out why the results truncate the numeric part, but one problem with this solution is that I can't guarantee that the maximum number of alpha characters will be 2. The number of alpha characters is unknown. That's why I initially thought that I would have to look for the position of the first number in the field using the inStr function. If I'm interpreting Remou's code correctly, it assumes that there are a maximum of two alpha characters. I could easily (well sort of!) expand it to three characters, but what if there are four chars before the number? The only thing that I can guarantee is that the format will be Alpha & Num, but the number of Alpha or Num chars isn't known or fixed.

Something that looks like it should be so easy turns out to be a pain in the ***!

Any follow on suggestions?

Thanks a lot!

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Geez, it's getting late...

My comment that Access sorts the alphas before the numbers isn't really what I meant to say.

Access treats the entire field as a string, which makes sense, so R11 comes before R2. But in electronics, R2 comes before R11. LOL Actually the R stands for Resistor, and then they are numbered in sequential order...R8, R9, R10, etc. I think that Remou is on to something with his code, but it needs some tweaking.

dz
dzaccess@yahoo.com

 

[Remou]

I did not include the field, just an idea. maybe this is better:
[tt]SELECT tblTable.Field1
FROM tblTable
ORDER BY IIf(IsNumeric(Mid([field1],2,1)),Left([field1],1),Left([field1],2)), IIf(IsNumeric(Mid([field1],2,1)),Mid([field1],2),Mid([field1],3));[/tt]

 

[FoxProProgrammer]

ok, the numeric part didn't really get truncated. It's just in a different field in the Query output. I just noticed that. I fixed that by creating another field in the Query output named Part that simply concatenates AlphPart and NumPart.

SELECT IIf(IsNumeric(Mid([field1],2,1)),Left([field1],1),Left([field1],2)) AS AlphPart, IIf(IsNumeric(Mid([field1],2,1)),Mid([field1],2),Mid([field1],3)) AS NumPart, AlphPart & NumPart AS Part
FROM tblTable
ORDER BY IIf(IsNumeric(Mid([field1],2,1)),Left([field1],1),Left([field1],2)), IIf(IsNumeric(Mid([field1],2,1)),Mid([field1],2),Mid([field1],3));

But it still doesn't sort the way that I need. In fact it sorts exactly the same as ORDER BY Field1 without all the string functions.

dz
dzaccess@yahoo.com

 

[Remou]

You are right, it is late:
[tt]SELECT tblTable.Field1
FROM tblTable
ORDER BY IIf(IsNumeric(Mid([field1],2,1)),Left([field1],1),Left([field1],2)), Val(IIf(IsNumeric(Mid([field1],2,1)),Mid([field1],2),Mid([field1],3)));[/tt]

 

[FoxProProgrammer]

By Golly your last solution works!

I tried it on data that has at most two alpha characters. I'll take a close look at the ORDER BY clause later tonight or tomorrow to see exactly what you are doing and whether it matters if there are more than two alpha characters in the data. Thanks so much, Remou!



dz
dzaccess@yahoo.com

 

[Remou]

Thanks. The number of alphas does matter, that is why I had some doubts about the use of such SQL to you.

 

[FoxProProgrammer]

Hello Remou:

I expanded the ORDER BY clause in your SQL to allow for up to three alpha characters. It appears to work, but I was wondering if you could please look at it when you get a chance and let me know if you see any errors. I included the entire Query to eliminate any possibility of typing it wrong here. I don't think that there would ever be more than three alpha characters, but if there are, I could add another nested IIF to allow for it. I have to wonder about the performance of this Query, but since the table is relatively small, speed doesn't seem to be an issue.

Thanks so much for your help!

SELECT Components.circuitID, Components.SRUID, PartNumbers.PartID
FROM SRU INNER JOIN (PartNumbers INNER JOIN Components ON PartNumbers.PartID = Components.partID) ON SRU.SRUID = Components.SRUID
WHERE (((Components.SRUID)=[cbo_SRU].[Value]))
ORDER BY IIf(IsNumeric(Mid([CircuitID],2,1)),Left([CircuitID],1),IIf(IsNumeric(Mid([CircuitID],3,1)),Left([CircuitID],2),Left([CircuitID],3))), Val(IIf(IsNumeric(Mid([CircuitID],2,1)),Mid([CircuitID],2),IIf(IsNumeric(Mid([CircuitID],3,1)),Mid([CircuitID],3),Mid([CircuitID],4))));

dz
dzaccess@yahoo.com

 

[Remou]

Looks fine to me, but that means very little
If things get more complicated, a user defined function might be a better bet.

 

[FoxProProgrammer]

Don't cut yourself short, Remou! You have guru status in my book. Thanks for the second look.



dz
dzaccess@yahoo.com