Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Unanswered: Sort by State-->City-->Street but keep Field Order Street-City-State ?

    My dB Table list, from left to right . . . Street, City, State.
    I need to apply a multiple Sort Query using State-->City-->Street in that order.

    It then reverse my field Order, unfortunately - so I have to go into Design mode and rearrange the fields back to Stree/City/State after each time that I run the Query.

    Is there a way to fix this so that the field order is maintained while sorting the fields in reverse order as described ?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what indexes are you using
    you shouldn't have to change the design each time you want to change the order

    make sure all columns that are in this requirement are indexed

    I forget how but its possible to change the underlying recordset order
    ..you may have to switch to unbound forms and populate the controls yourself. given the volume of data you have that may not be a daft idea in any event

    if all else fails you could change the recordset to
    select my,column,lidt, from mytable order by my,sort,order
    then do a refresh ( I think)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Posts
    258
    In Table Design, click View|Indexes. You can specify the order of indexes and their sort order from there.

    Regards,

    Ax

  4. #4
    Join Date
    Aug 2009
    Posts
    7
    How do you sort by Indexes? I only know how yo sort by Fields. Actually I have no idea who to work directly with Indexes other that to select "Yex (allow Dupes)". Is there a link that explains this ?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the sort order is the sort order, indexes improve performance (speed) of retrieval of the data based on the sort order.

    so the general advice is index all columns which will be used frequently in searches or where clauses.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Why not create a calculated field in your query:

    SortField: [State] & [City] & [Street]

    Then sort the query on SortField?

    You can untick the "Show" checkbox if you don't want the calculated field to show in the query results.
    Last edited by Missinglinq; 09-01-09 at 19:27.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    May 2009
    Posts
    258
    Quote Originally Posted by lifeson99
    How do you sort by Indexes? I only know how yo sort by Fields. Actually I have no idea who to work directly with Indexes other that to select "Yex (allow Dupes)". Is there a link that explains this ?
    The order in which the indexes appear is the order in which the records are sorted. For example, the indexes are probably listed as follows:
    Code:
    Street
    City
    State
    You can drag the indexes to move them around, so you'd put them like so:
    Code:
    State
    City
    Street
    Your other option is just to use a query with an order by clause, which will allow temporary sorting:
    Code:
    ORDER BY State, City, Street
    Ax

  8. #8
    Join Date
    Aug 2009
    Posts
    7
    I that the exact text that you enter in the Criteria field?
    Or is it an Access Macro line ?

  9. #9
    Join Date
    May 2009
    Posts
    258
    It is for the actual SQL query, not the criteria field. That is, entering the query explicitly, outside of the query builder.

Posting Permissions

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