Results 1 to 4 of 4

Thread: Sorting Issues

  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Unanswered: Sorting Issues

    Hi all

    I have a database, of quite a size, one area of which logs all the company's tools.

    When I created the list, then the subsequent form, I rather short sightedly neglected to sort the records into a 'logical' order.

    Upon creating the form I didn't bother with a sort and therefore the records are sorted as per the primary key, which is 'Autonumber' - the trouble with this is that when adding new tools, of an identical nature to existing tools, the record appears further down the list, rather than with others of the same type (does that make sense?).

    With the tables and forms already set, is there a way I can change the sort criteria, so instead of by Primary Key, I can change it to sort by Tool Primary number, then by Tool Secondary number?

    All help and suggestions as ever greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id suggest you defien a table defining tool types

    table tooltypes
    type
    description

    then add a column to your main table, say tooltype with the same datatype (size etc) as the column in tooltypes
    then declare a relationship between the tools.tooltype and tooltypes.type

    then edit your main form
    addf the new column (or better yet add the new column as a listbox or combo box with the rowsource for the4 list/combo box being the table tooltypes, the bound column being tools.tooltype (the new column in tools)
    set the sort order on your form to the new column

    then shout and scream and swear at your computer untill you define a tool type for each and every tool

    there are refinements you can do
    ferinstance you coudl defien a tool type as the default too tyupe (add an extra column int he tooltypes that is a yes no called say IsDefault and order your list / combo box in the form in isdefault, description order.

    you could define a hierarchy in yopur tool types
    by adding another column in your tooltype table which identifiesd a parent category (same size type as the PK in tool types (type) and defien a realtionship within tooltypes that allows for a parent that is either null or points to another value.

    so you could have
    tooltypes
    |----Hand
    |----Power
    |----rechargeable
    |----mains powered

    or if it makes more sense
    tooltypes
    |----Drills
    |----Hand powered
    |----Power
    |----rechargeable
    |----mains powered

    what ever
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2011
    Posts
    14
    Thanks healdem for your response - I have now sorted this matter - so all records are in order of the tool number, I did it by Form properties and 'order by' - so all records follow the number sequence.

    However, an issue still occurs.

    I have a drop box on the form for a quick jump to the record required, but it only sorts in order of the primary key, not by tool number - is there a way I can do this?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    set an explicit sort sequence
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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