Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Sorting (2000/SR1)

    I have a database that keeps track of our warehouse locations. We have both three and four digit numbered locations, as well as some that are a combination of numbers and letters. Such as 101, 1001, 201A, 2001A. I also have a field for "sections", such as 100, 1000, 200, 2000.

    The problem I run into is that when I sort by section, I get it in this order, 100, 1000, 200, 2000, etc. When I sort by Location number, I get 1001 through 1009, then 101, then 1010 through 1019, then 102 and so on. I know I could give each location a unique number that I could sort on, but I have over 600 locations and if I ever had to add a location, it could be a nightmare to adjust all the numbers so that it sorts correctly. How can I set this up so that I get the correct sorting? Thanks for any help.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sorting (2000/SR1)

    The problem is that you are sorting a field that is text, and text sorts differently than numbers. A suggestion - split your location into two fields, one which is a numeric field (probably a long integer), and another which holds the alpha suffix. You can recombine them in a query or on a form or report, but sort on the two fields in any query, and make the primary key the combination of the two to ensure uniqueness.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Sorting (2000/SR1)

    Hi Wendell,

    Thanks for the suggestion. That will work and I didn't even think about it. The only question I have left is how do I combine the 2 fields to make a primary key. Not quite sure on that one. Thanks again.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sorting (2000/SR1)

    Choose both fields while in table design view and click the primary key tool.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting (2000/SR1)

    But not all records will have an alphabetic suffix. A field in a primary key can't be empty (null). An AutoNumber field as primary key is better in this situation.

    This is a downside of having two fields if one of them can be empty. It is possible to put a unique index on the two fields, provided Ignore Null Values is set to Yes, but this will still allow duplicate records with only numeric values.

    An alternative would be to leave the Location as one field, and sort it as follows in a query:
    Add a calculated column Val([Location]), clear its Show check box and set Sort to Ascending.
    Add another calculated column Mid([Location],Len(Val([Location]))+1), clear its Show check box and set Sort to Ascending.

Posting Permissions

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