Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2008
    Posts
    5

    Unanswered: how to sort records of this type: 273a(b)1?

    Hello,

    I am a prosecutor who needs help sorting the office's database of criminal codes. The type of penal code section I mentioned in the title of the post creates quite a problem for sorting. Not only are the code sections contained in a text field, but the sections themselves vary from number to text to number to text, etc.

    The "ranking" of the sections, or the heirarchy of the outline, proceeds like this:

    123
    123.1
    123.1a (lower case "x" is uncommon)
    123.1a(a)1
    123.1a(a)1(b)
    123.1a(a)1(b)1(A)
    123.1a(a)1(b)1(A)1

    A common variant contains a slash. For example, California's penal code section for petty theft is 484. The section for "attempt" is 664. To charge attempted petty theft, we write 664/484.

    I am stumped. Numbers of different lengths, decimals, parentheses, slashes, numbers and letters interspersed. I don't see how I can make the Val expression work with this mess, nor do I see how padding with zeros is going to help. Then again, I am not proficient with something this advanced (to me, anyway).

    Thank you for any help you can give.

    Dave

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    from the data you have provided, access seems to do something reasonably intelligent:

    Field1 - DESC
    664/484
    664
    123.1a(a)1(b)1(A)1
    123.1a(a)1(b)1(A)
    123.1a(a)1(b)
    123.1a(a)1
    123.1a
    123.1
    123

    Field1 - ASC
    123
    123.1
    123.1a
    123.1a(a)1
    123.1a(a)1(b)
    123.1a(a)1(b)1(A)
    123.1a(a)1(b)1(A)1
    664
    664/484

    what are you trying to achieve with your sort that is not achieved by simply sorting?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Indeed... It seems to be nothing more required than a Text field that's sorted.

    Just don't use Val() ?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2008
    Posts
    5
    It just won't sort properly. There are 3 fields, and here is a short example:


    PC . . . . . 484(a) . . . . .BRIEF DESCRIPTION OF CHARGE
    PC . . . . . 484(a)-1
    PC . . . . . 484(a)-2
    PC . . . . . 653.22 <<
    PC . . . . . 653f
    PC . . . . . 653k
    PC . . . . . 653k-1
    PC . . . . . 537(b) <<
    PC . . . . . 537(e)
    PC . . . . . 597a
    PC . . . . . 602.1 <<
    PC . . . . . 602.1(a)
    PC . . . . . 602.5
    PC . . . . . 602(i)

    The penal code sections jump around like that from beginning to end. However, within each mini-clump they are sorted correctly, like in the example above.

    Sorry that I didn't mention this in my first post.

    Thanks again.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Short of separating the code parts into separate fields, your obviously going to need to break apart the value in the field and then sort on that broken-out value. For example, you might have an expression in a query such as:

    First Three: Cstr(Left([MyCodeField],3)) or Val(Left([MyCodeField],3) - or something to that effect which takes part of the values in the field (If it's always the first 3 characters, that isn't too bad. Otherwise you may find one time you're breaking apart the middle 3 characters and other times a different set of characters.)

    (but I'm not sure why StarTrekker mentioned not to use Val)

    Since you're dealing with multiple, multiple different types of formats here though, I might see if I could even create a table defining the different formats (which could then be utilized on the form to populate the format mask or format property) for data entry.

    You may want to do some research in the MSAccess help on: Type Conversion Functions.

    But if you always want to sort on the first 3 characters, that can easily be done above (except when you have a / and are representing 2 codes - how would you want to handle these type as far as sorting goes? - I might think relational table in this case and make it so you don't use a / but instead enter the 2nd, 3rd, etc...offenses in a relational table (ie. many to 1.)
    Last edited by pkstormy; 08-26-08 at 22:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed.

    I only suggested not to use Val because I thought the text sorting would just work, so I saw no reason to use it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Aug 2008
    Posts
    5
    I'm afraid that in an effort to be concise, it seems I omitted a detail that should have been included in my first post -- not all of the Code sections begin with 3 whole numbers, possibly followed by a decimal point, letter, bracket, or slash. We also have a few 2 digit and 4 digit Codes, and quite a number of 5 digit Codes. Fortunately, we do not have any single digit or 6 digit Codes.

    Here is a sample of 2 digit to 5 digit Codes:

    32
    69
    148
    242
    4140
    12020
    12030

    I would estimate that 95% of the Codes are of the "3 initial whole number" variety, but if I sorted only on the first 3 characters, the other 5% of the Codes would be randomly interspersed among the properly sorted ones.

    Finally, and I'm not sure if it's relevant, but why would some of the Codes sort properly? As I mentioned in my previous post, small clumps of them are sorted precisely, followed by other small clumps that are sorted correctly but out of numerical/heirarchical order. And those might be followed by a dozen records with no apparent sorting.

    Bah.

    I appreciate all of your responses and advice, and I also appreciate how quickly you guys have responded to my plea.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I have to run quickly here but from everything I'm reading from you ronnadavid, it kind of sounds like your going down the lines of data logic explaining the data itself (data about data). I'll try to explain that more later but I've gotta run.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    why would some of the Codes sort properly? As I mentioned in my previous post, small clumps of them are sorted precisely, followed by other small clumps that are sorted correctly but out of numerical/heirarchical order. And those might be followed by a dozen records with no apparent sorting.
    I don't know if this helps, but when you sort at text field, numbers work the same as letters... so:

    1
    100
    2
    250
    38

    This is sorted correctly. It makes sense if you substitute numbers for letters:

    B
    BAA
    C
    CFA
    DI

    Here, I have substituted 0 for A, 1 for B, 2 for C and so on. As you can see, the result is correctly sorted.

    That is why you are going to have to break up the entry into logical parts and sort them individually to get a perfect result.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    'another' alternative is write a function that inserts leading spaces or zeros so your numeric fields have all the same number of characters

    you can do this as either .....
    "032" 'as a compass direction is used in maritime use, the leading 0 is put there to make it clear and also so that helms will always receive 3 digits
    or
    " 32" ' put a leading space in front of numbers to make it more human friendly

    eg
    Code:
    public function PadCriminalCode(strCrimCode as string) as string
    'this fucntion is intended to convert a string representation of a code into a
    single number similar to an IP Address (except it allows for alphanumeric data
    'first off clean up the data
    strCrimCode = replace(strCrimCode, "(", ".") 'replace any occurrence of ( with a .
    strCrimCode = replace(strCrimCode, ")", ".") 'replace any occurrence of ) with a .
    dim strCodeBlocks 'array containing each block element
    dim strCodeBlock as string 'indiviudual element of the code block
    const NoChars = 4 ' identifies the number of character in each block
    'then go through the number and make sure there are NoChars digits per block
    for each strCodeBlock in strCodeBlocks
      if isnumeric(strCodeBlock) then 'if its a number add leading characters
        PadCriminalCode = PadCriminalCode & left("0000",NoChars - strlen(strCodeBlock)) & strCodeBlock
      else '  if its not a number add trailing characters
        'do we care/need to add padding here?... probably not
        'but we will so that the sub codes are all 4 digits long just in case
      PadCriminalCode = PadCriminalCode & strCodeBlock &  left("    ",NoChars - strlen(strCodeBlock))
      end if
      PadCriminalCode = PadCriminalCode & "."
    next CodeBlock
    PadCriminalCode = left(PadCriminalCode,strlen(padCriminalCode)-1 'remove the last character, a "."
    end function
    code is air code, so its not tested, it may! not work, it may! need debugging

    code goes into a code module in the db containing the forms
    you would then call the functon either as the sort sequence in the SQL select
    eg
    select my, column, list from mytable order by PadCriminalCode(CriminalCode)
    OR (if you wnat to raise the wrath of db 'professionals' as a stored column int he db, that is always adjusted whenever the CriminalCode is set. that means you cannot use datasheet virews you MUST use forms)


    I almost felt like saying.. that was some 20 minutes work, call it 5 billable hours.. make the cheque out for &#163;150 and we'll call it quits, given the number of "creative" legal bills I've had over the years
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think you need to define "sorting properly". This is obviously based on business rules we aren't privy to. Could you take your data in post #4 and sort it, explaining how and why it differs from Access ordering? Also, are there any further variations (like the "less than 3 numbers at the beginning") you haven't accounted for yet?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon - I've just realised the point of your first post, but it doesn't explain all the variations in post #4.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm still intrigued to know the purpose of the sort that is not served by whatever the default access sort does. this is +/- the same comment as pootle's #11 (and my #2).

    pootle seems to have had some insight on the Nth read of #1, but i'm afraid i'm still in the dark.

    a sorted list is not inherently useful.
    what do you do with it next that makes a specific sort-order more valuable to you than the access default sort?

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I can see that a sorted list is a requirement
    I'm guesing it may even be a legal requirement.

    imagine some plonker lawmaker...
    section 123
    has, say, 30 sub sections
    123.1 to 123.30
    so without leading zeros/spaces
    123.1
    123.10
    123.11 to 123.19
    123.2
    123.20 to 123.29
    und so weiter

    with the addd joy of
    123a
    123.1(a)

    I agree the OP needs to clearly and concisely explain what the problem actually is (that'll be interesting for a lawyer.....)
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Aug 2008
    Posts
    5
    Pootle flump and izrider, the reason for this project is to condense the thousands of criminal Codes that exist into a report of the 300 or so that comprise over 99% of cases that we file against people who have been arrested.

    Another purpose of this report is to make it easy for us to charge a significant number of Codes that have many subparts. In other words, it will be a "cheat sheet". For instance:

    (These are from the Penal Code)

    PC . . . 273a(a) . . . under circumstances likely to produce great bodily harm or death, allows a child to be injured.
    PC . . . 273a(b) . . . under circ's not likely to produce great bodily harm or death, allows a child to be injured.
    PC . . . 273ab . . . with custody of child under 8, assaults the child with force likely to produce great bodily injury, child dies
    PC . . . 273d . . . . inflicted cruel or inhuman corporal punishment or an injury resulting in a traumatic injury.

    The various Codes are riddled with this sort of numbering. And yes, most of the time we all remember whether a crime is 273d or 273.5(a). But 273a(b) versus 273ab? That's a bit more difficult. Especially when our elected officials are constantly tinkering with the Codes. It wasn't too long ago that the abomination, 273a, existed. It was simply, properly, 273(a), then (b), then (c), etc.

    So, if the Codes are not sorted by number, subsection, sub-subsection, etc., in the heirarchy I specified in my first post, they will be (and are) strewn about the report, which only makes the task of finding the proper Code more difficult. For example, I know that I need to look in the 273 sections for child abuse/endangerment. What I might not remember is the specific subsection to charge. With a list of the sort I have now, there's no way of knowing how long it will take me to find the section that I need. I'm better off pulling out the published, "condensed", version of the penal code which is now 1,368 pages.

    I hope this is the information you guys were looking for.

    Healdum, I leave it to you to judge the clarity and conciseness of the response. However, if others decide that you are unreasonable, I reserve the right to declare your judgement null and void. As to your first post -- wow, thanks for code. Also, thanks for the "20 minutes of work, call it 5 billable hours" quote. I had such a good laugh at that one, I woke the dog. As someone who went through a divorce a few years back, I received the same sort of flaying.

Posting Permissions

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