Results 1 to 15 of 15

Thread: sorting problem

  1. #1
    Join Date
    Oct 2005
    Posts
    13

    Unanswered: sorting problem

    hi all,

    i have a sorting problem.
    I store subjectnumbring in a text key. The numbering is:
    1
    1.1
    1.1.1
    1.1.2
    1.1.2.1
    ..
    1.12
    and so on.
    Only because it is a texttype, when you sort, 12 comes before 2. So you wil get this:

    1
    1.1
    1.12 <----
    1.2

    how can i fix that ?
    thanks for the replys

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in short, change your data
    1.1 becomes 1.01, unless of course you expect more than 99 sub units in which case it becomes 1.001
    OR
    1.<space>1, unless of course you expect more than 99 sub units in which case it becomes 1.<space><space>1
    you may be able to kludge up some function to reformat your exisitng data

    alternatively (and I'm not reccomending this) is to store the sub elements as separate elements (eg your numbering index is stored as
    <level1>.<level2>.<level3>.......<levelx> the problem with this approach is that unless your users can sign off on the maximum number of levels then you design will always be floored by some muppet wanting to add just that extra level that blows the scheme.

    OR store them as a recursive self referencing table

    DocuemntID - an autonumber in the main document table

    sectiontable
    DocumentID a long integer referecning the DocumentID in the document table
    level ID an index (say byte) giving 0-255 leveles where 0 is the first element. your primary key being DocumentID+LevelID
    subsectionIndex a byte vlaue

    ie if your original document had a section ID 1024 and you wanted to store 1.1.3.14 sectiontable would have 4 entries
    1024,0,1 - equates to 1.x.x.x
    1024,1,1 - equates to x.1.x.x
    1024,2,3 - equates to x.x.3.x
    1024,3,14 - equates to x.x.x.14

  3. #3
    Join Date
    Oct 2005
    Posts
    13
    Quote Originally Posted by healdem
    in short, change your data
    1.1 becomes 1.01, unless of course you expect more than 99 sub units in which case it becomes 1.001
    OR
    1.<space>1, unless of course you expect more than 99 sub units in which case it becomes 1.<space><space>1
    you may be able to kludge up some function to reformat your exisitng data

    alternatively (and I'm not reccomending this) is to store the sub elements as separate elements (eg your numbering index is stored as
    <level1>.<level2>.<level3>.......<levelx> the problem with this approach is that unless your users can sign off on the maximum number of levels then you design will always be floored by some muppet wanting to add just that extra level that blows the scheme.

    OR store them as a recursive self referencing table

    DocuemntID - an autonumber in the main document table

    sectiontable
    DocumentID a long integer referecning the DocumentID in the document table
    level ID an index (say byte) giving 0-255 leveles where 0 is the first element. your primary key being DocumentID+LevelID
    subsectionIndex a byte vlaue

    ie if your original document had a section ID 1024 and you wanted to store 1.1.3.14 sectiontable would have 4 entries
    1024,0,1 - equates to 1.x.x.x
    1024,1,1 - equates to x.1.x.x
    1024,2,3 - equates to x.x.3.x
    1024,3,14 - equates to x.x.x.14

    Fist off thanks for the quick reply
    second, i tried recursive, but its pretty hard to get the data back. Dont really know how to start that kinda query. Readig last part of the reply, im not totaly sure what you mean?
    Saying, every section has 4 ocurrences ??
    And do you mean i have 4 fields for the selfreflecting table?
    Last edited by ice-9; 10-14-05 at 09:13.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As ever Healdom is giving an excelent and comprehensive answer.

    As a quick fix , though, would this do the trick:
    Code:
     
    SELECT text_key 
    FROM text_table
    ORDER BY CINT(REPLACE(text_key, ".", "")) ASC
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just realised - your codes could very quickly exceed an integer value. Perhaps try CDBL instead.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2005
    Posts
    13
    Hi,
    thanks for the reply, but this is the result

    MinuteCode Expr1001
    1 1
    1.1 11
    1.2 12
    1.1.1 111 <---
    1.12 112
    1.2.1 121
    12.2 122
    13.2 132
    2.2.2 222
    1.12.2 1122

    it IS the closed ive been!
    But you see at the arrow. The numbring issnt right. ie 1.1.1 needs to be under 1.1 and not under 1.2.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ice-9
    Hi,
    thanks for the reply, but this is the result

    MinuteCode Expr1001
    1 1
    1.1 11
    1.2 12
    1.1.1 111 <---
    1.12 112
    1.2.1 121
    12.2 122
    13.2 132
    2.2.2 222
    1.12.2 1122

    it IS the closed ive been!
    But you see at the arrow. The numbring issnt right. ie 1.1.1 needs to be under 1.1 and not under 1.2.
    Hi

    No probs (depending on the answer...) - how many spaces (max) could there be in a value?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Hi

    No probs (depending on the answer...) - how many spaces (max) could there be in a value?
    No - I'm wrong - soz. Will haave a think
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2005
    Posts
    13
    Quote Originally Posted by pootle flump
    Hi

    No probs (depending on the answer...) - how many spaces (max) could there be in a value?

    no spaces. it are two separate collums.
    sorry my bad, it was a bit confusing.


    MinuteCode Expr1
    1---------- 1
    1.1---------- 11
    1.2---------- 12
    1.1.1---------- 111
    11.1---------- 111
    1.12---------- 112
    11.2---------- 112
    11.4---------- 114
    1.2.1---------- 121
    12.2---------- 122
    13.2---------- 132
    2.2.2---------- 222
    11.1.1---------- 1111
    1.12.2---------- 1122
    Last edited by ice-9; 10-14-05 at 09:40.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ice-9
    no spaces. it are two separate collums.
    sorry my bad, it was a bit confusing.
    I should be experienced enough of reading output to have figured it out

    How many "."s max then? Basically, you need to order by the numerical value of each number between the dots (assuming you keep your structure as it is).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Oct 2005
    Posts
    13
    max 5 levels.

    realy appriciate the help

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Unless someone has a bright idea I really don't fancy writing the SQL that would achieve this. In case you fancy it, you would need to use a combination of CINT, MID, INSTR(TheCol, "."). Or you could parse it in a vb function. Or you can have another read of Healdom's ideas and think about structural changes.

    Soz
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Recursion is the only dynamic way to do this. Any other method short of manually parsing and sorting on your own is going to rely on static ceilings for numbers and nesting levels.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you need to retain your data then another option could be to write a function to recode the "x.x.x.x" to generate a sort key. depending on your volumne of data it could be put in as an element of the query, or as a column within the table

    if you add the function to a code module it can be called as getSortKey(myColumn)

    you could update your existing data using an update query
    you could add the fucntion to your data entry form

    Code:
    Public Function GenSortKey(strBlock) As Double
    'strBlock is the text block to be split
    'strSep is the separator to be used
    GenSortKey = 0
    If Len(strBlock) = 0 Then Exit Function 'return 0 if null
    Const NoBlocks As Integer = 5 'defines the number of blocks / WORDS EXPECTED
    'IF SET TO 5 THEN THE MAXIMUM NUMBER OF WORDS IT CAN HANDLE IS 5 IE 1.2.3.4.5
    Const BlockSize = 3 ' the maximum number of characters in your block / word
    '1 caters for 1.1 to 1.9
    '2 caters for 1.1 to 1.99
    Dim Word As Variant ' a variant array to hold the result of the split
    Dim iLoop As Integer
    Dim Factor As Double ' contains the multiple
    Factor = 1
    Word = Split(strBlock, ".")
    For iLoop = NoBlocks - 1 To 0 Step -1
      If UBound(Word) >= iLoop Then     'have we got something in this word?
        If IsNumeric(Word(iLoop)) Then  'check we have a numeric value for this word?
          GenSortKey = GenSortKey + Word(iLoop) * Factor
        End If
      End If
      Factor = Factor * 10 ^ BlockSize ' recalculate the offset for the next word
    Next iLoop
    End Function
    ***health wrning***
    The above function is a quick & dirty, the code could be tidied & optimised. Its not intended for review by the VB style police.
    Last edited by healdem; 10-14-05 at 14:23.

  15. #15
    Join Date
    Oct 2005
    Posts
    13
    thank you all,
    im checking atm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •