Order by numbered list "1.2.3"

[robblack]

I have a requirement to sort data of the following format by ref:

ref heading
------------------
1 Section
1.1 Test
1.2 Blah
1.3 Another
1.4 Things
1.4.1 Stuff
1.4.2 Other
...
10.1.1 Something
10.10.1 Else
10.2 Blah blah
2 Some other section
2.1 Another sub-section
...
As this data is stored as a string, a simple "ORDER BY Ref" will not deliver the desired effect, but rather will sort the data alphabetically (1, 10, 2, 20, 3 etc.).

Is anyone able to suggest appropriate SQL syntax or point to some VB/VBA logic to address this issue?

Thanks in advance,


Rob.

 

[FoFa]

Truthfully, I would create a vba function that you could feed the string, and the portion of the number you want (dot delimited portion of you ref. number). Then you can setup sort columns, using the function to return the porper values, and sort on that.
So 1 would return 1 0 0
and 1.1.4 would return 1 1 4
etc. as numeric values.
Just my 2 cents worth

 

[gemma-the-husky]

i was just to going to type in a similar answer to that described by FOFA. That's the way to go, I think

 

[raskew]

Hi -

My first thought would be to go back to the creators of this monster and ask them to provide the logic. You are dealing with idiots here!

Best wishes - Bob

 

[DALeffler]

You need Access to first sort the numbers between the dots by the number of characters between the dots and then by the actual characters between the dots.

To do that, one way is to generate a string from the ref value that contains both. Then it should sort "correctly".

I use letters to represent how many characters there are between any two dots: A=1, B=2, C=3, ..., Z=26. Then concatenate to that letter the characters between the dots.

If you send the function this:

10.2 Some Text

It returns this:

B10A2

The "B" means there's 2 characters before the first dot, followed by the 2 characters, and the "A" means there's 1 character after the first dot followed by the actual character.

Likewise, if you send the function this

2 Some other section

It'll return this:

A2

And when the query that calls the function is sorted ascending by the calculated field, it will sort the way you want.

Function fDotSwap(sSTSB As String) As String
Dim sNumOfNums As String
Dim iDot1 As Integer

'look for 1st space (" ") in String To Sort By (sSTSB)...
sSTSB = Mid(sSTSB, 1, InStr(1, sSTSB, " ") - 1)
'add to sort string a "." (used for loop exit)
sSTSB = sSTSB & "."

Do While Len(sSTSB)
    'find the 1st "." in the sort string
    iDot1 = InStr(1, sSTSB, ".")
    'Take all characters before the 1st "." in the sort string, count
    'how many characters there are and add 64 to that value. This will
    'calculate the ANSI character for how many characters are in front
    'of the first "." -- A=1, B=2, C=3,--> Z=26
    sNumOfNums = Chr(64 + (Len(Mid(sSTSB, 1, iDot1 - 1))))
    'place the character representing the number of digits before
    'the 1st "." in front of the digit characters before the 1st "."
    fDotSwap = fDotSwap & sNumOfNums & Mid(sSTSB, 1, iDot1 - 1)
    'trim characters and "." from the sort string for next loop pass
    sSTSB = Mid(sSTSB, iDot1 + 1)
Loop

End Function

The table I used to test this function is shown in the 1st column with an ascending sort, and the query result is in the 2nd & 3rd column sorted ascending by the 3rd column.

ALPHABETIC SORT					tRefSorted					Sort-by String
1 title						1 title						A1
1.1 title					1.1 title					A1A1
1.1.10.1.10.2 title				1.1.9.1.9.2 title				A1A1A9A1A9A2
1.1.9.1.9.2 title				1.1.10.1.10.2 title				A1A1B10A1B10A2
1.10.1.10.1 title				1.2 title					A1A2
1.2 title					1.2.1 title					A1A2A1
1.2.1 title					1.2.1.1 title					A1A2A1A1
1.2.1.1 title					1.2.1.1.1.1 title				A1A2A1A1A1A1
1.2.1.1.1.1 title				1.2.1.2.1.2 title				A1A2A1A2A1A2
1.2.1.2.1.2 title				1.2.8 title					A1A2A8
1.2.10.1.1.1 title				1.2.9 title					A1A2A9
1.2.10.1.10 title				1.2.10.1.1.1 title				A1A2B10A1A1A1
1.2.10.1.9 title				1.2.10.1.9 title				A1A2B10A1A9
1.2.10.10.10 title				1.2.10.1.10 title				A1A2B10A1B10
1.2.8 title					1.2.10.10.10 title				A1A2B10B10B10
1.2.9 title					1.3 title					A1A3
1.3 title					1.3.1 title					A1A3A1
1.3.1 title					1.3.3 title					A1A3A3
1.3.3 title					1.10.1.10.1 title				A1B10A1B10A1
10.1 title					9.9.1 title					A9A9A1
10.1.10 title					9.9.9.99 title					A9A9A9B99
10.10.10.100 title				10.1 title					B10A1
10.10.10.99 title				10.1.10 title					B10A1B10
10.10.100.10.1000.10000000000.1 title		10.2 title					B10A2
10.10.100.10.1000.100000000000.1 title		10.2.2 title					B10A2A2
10.10.100.10.1000.9.9.6.7.8.3.2.1 title		10.2.9 title					B10A2A9
10.10.100.10.1000.99999999999.1 title		10.2.10 title					B10A2B10
10.10.8 title					10.9 title					B10A9
10.2 title					10.9.100 title					B10A9C100
10.2.10 title					10.10.8 title					B10B10A8
10.2.2 title					10.10.10.99 title				B10B10B10B99
10.2.9 title					10.10.10.100 title				B10B10B10C100
10.9 title					10.10.100.10.1000.9.9.6.7.8.3.2.1 title		B10B10C100B10D1000A9A9A6A7A8A3A2A1
10.9.100 title					10.10.100.10.1000.10000000000.1 title		B10B10C100B10D1000K10000000000A1
80 title					10.10.100.10.1000.99999999999.1 title		B10B10C100B10D1000K99999999999A1
9.9.1 title					10.10.100.10.1000.100000000000.1 title		B10B10C100B10D1000L100000000000A1
9.9.9.99 title					80 title					B80
90.1 title					90.1 title					B90A1

 

[WayneRyan]

Rob,

I too hate paragraph numbers. If they had a know number of components
it would be best to split them into separate numeric fields and just
sort, but I've seen them go to surprising depths like 1.2.3.4.5.6.7.8

The best solution is to format them so that they will sort as in the
previous posts. Assume that each segment of the overall number has a
maximum of 3-digits. You want to structure the number like --> 001.004.097
But not the "real" number, just a copy of it.

You can create a Public Function and use it to make a new field in the
query for your forms/reports.

NewField: strParagraphNumber([YourUnformattedNumber])

You don't have to display the field, just use it to sort by.

Crude attempt at the code.

Public Function strParagraphNumber(strOriginalNumber As String) As String
Dim strTemp As String

If Instr(1, strOriginalNumber, ".") = 0
   strParagraphNumber = Format(strOriginalNumber, "000")
   Exit Function
End If

strTemp = ""
While Instr(1, strOriginalNumber, ".") > 0
   strTemp = strTemp & _
             Format(Mid(strOriginalNumber, 1, Instr(1, strOriginalNumber, ".") - 1), "000") & _
             "."
   strOriginalNumber = Mid(strOriginalNumber, Instr(1, strOriginalNumber, ".") + 1)
   Wend
strParagraphNumber = strTemp
End Function

hth,
Wayne

 

[robblack]

Thanks to all who responded to this post. This problem was complicated by the fact I was using ADO from VB (I appreciate I didn't state this in my original post); as such, I was not able to use any functions contained in Access modules as part of my SQL query. "E.g. SELECT * FROM MYTABLE ORDER BY getSortNumber([REF])". Any solution I suppose would require post processing in VB (e.g. dumping data into collection/array and sorting).

For the time being I have managed to side step the problem. Thanks again.

Implementation of schemas/interfaces having recursive relationships (which clearly should have been used in this case) is perhaps one of the most challenging aspects of database development.

Rob.

 

[DALeffler]

How about pre-processing instead of post-processing?

IOW, add a sort column to the table so when records are created they have a sort code created as the record is written?