Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Access query defaulting to strange sort order

    I have an Access database with reasonably large volumes of fairly simple data imported from another source. (I also have several similar databases that work in much the same way as the one in question.)

    I have created a second version of the database, in a separate folder, copied from the original. The supporting linked databases have also been copied into this separate folder and everything has been relinked.

    Some changes have been made to a query that is run at the end of the analysis procedure for the second database, but in all other respects - table, queries, forms, VBA procedures - the databases are identical.

    Early in the analysis procedure (long before the modified query is accessed), an SQL query is opened from a VBA procedure, based on an existing query and a table linked by a 3-letter code.

    This query opens in the original database, by default, with a sort order based on a 3-letter code as the primary sort key, and a date field as a secondary sort key. This is because the original query used in the SQL is sorted in this way.

    In all previous incarnations of this database, the SQL query opens with the code in alphabetical order, and the date in ascending order within each code.

    In the latest version, which has had no changes made to this early part of the procedure, the first 85 dates for the first (and only, apparently, the first) code are out of sequence, being placed 256 records further down the sequence. In all other respects, the dates appear to be in order. The offending dates are in the range 10/Aug/2001 to 6/Dec/2001 (using Australian date formats of 10/8/2001 to 6/12/2001).

    Now, I understand that all I need to do is to include a sort in the SQL query. However, due to the volume of data, this takes it from an almost instantaneously-opening query to one that takes many minutes to open if I introduce a sort, and in the past the sequence of the imported data and first query has been enough to make things work.

    What I don't understand is how the default sequence, drawn from the underlying query, has suddenly changed in this one copy of the database. Can anyone suggest where I should be looking for settings that might need to be adjusted?

    Thanks.

    Alison C

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Reflecting on my original post, written in great frustration and some haste, I realise I can probably distil the problem to one main point - the failure of a query to use the sort order of an underlying query in the absence of any further sorting instructions.

    I have a query with no sort fields set.
    It is one of many similar or identical queries over several similar databases.
    This query is based on another, sorted query, and a linked table that contains a series of codes, in order to select only the records containing those codes from the sorted query.
    In this query, and this one only, the sort sequence of the underlying query is ignored, in that the first 85 records are repositioned starting at record 256.

    I'm trying to work out whether there is some setting that might be causing this one query to behave differently.

    Cheers

    Alison C

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by alifrog View Post
    Reflecting on my original post, written in great frustration and some haste, I realise I can probably distil the problem to one main point - the failure of a query to use the sort order of an underlying query in the absence of any further sorting instructions.
    Actually, I believe the sort order of an underlying query is inconsequential to the resultant order of a query. The final sort order (lacking any direct ORDER BY instructions) is based on how the optimizer decides how to run the query.

    My advice is to not sort the input query, but rather add an ORDER BY to the final query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    alifrog (2011-05-11)

  5. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Thanks, Mark -

    That was the conclusion I had reached, after so many viewings of this post didn't seem to turn up an answer. I have added the Order By to the final query, and it now runs correctly, albeit more slowly. But I still don't understand enough about the mechanics of Access to get why the optimiser acts in one way on this particular copy of the database on one computer, while not doing so in any other version on any computer.

    I think that will have to remain one of life's imponderables!

    Cheers

    Alison C

  6. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by alifrog View Post
    Thanks, Mark -

    That was the conclusion I had reached, after so many viewings of this post didn't seem to turn up an answer. I have added the Order By to the final query, and it now runs correctly, albeit more slowly. But I still don't understand enough about the mechanics of Access to get why the optimiser acts in one way on this particular copy of the database on one computer, while not doing so in any other version on any computer.

    I think that will have to remain one of life's imponderables!

    Cheers

    Alison C
    Basically you are right; this is one of life's imponderables! I think you just have to accept that you can't depend on Access to return records in any particular order unless you specifically give it an output ORDER BY. How and why the optimizer does what it does is just beyond our control.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Sedgefield, South Africa
    Posts
    16
    Thanks
    1
    Thanked 0 Times in 0 Posts
    As a non-American user (from South Africa), I frequently bump into sort problems involving dates. Usually I play safe by making sure that the dates are all captured initially in proper date format, by using CDate(). This may not be relevant in this case, but it's worth being suspicious when dates are involved. I also endorse the inclusion of the ORDER BY clause as suggestede by Mark Liquorman.

  8. #7
    New Lounger TVJohn's Avatar
    Join Date
    May 2011
    Location
    London, UK
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by alifrog View Post
    ... it now runs correctly, albeit more slowly...
    Forgive me if I'm telling you something really obvious here, but it's always worth checking: you have got indexes on those two columns, haven't you? I know Access gets some bad press, but I've found that judicious use of indexes will make it really fly, unless your database is so big that you really shouldn't be using Access at all.

    John

  9. #8
    New Lounger
    Join Date
    May 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Alison,
    I develop databases for a living and a couple of years ago encountered exactly the same situation you describe. Relational Database principles do not guarantee anything about the order of records returned by a query unless you put an 'ORDER BY' in the query. However earlier versions of Microsoft database engines (this also applies to SQL Server) used to maintain the order of earlier queries in a chain but, as of the latest versions, this no longer applies and if you want the data in a particular order you must include an ORDER BY in the final query. I agree that it's a bit annoying but it's not a bug.
    Regards
    David

  10. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Thanks, David -

    That explains why I've only recently encountered it, despite working with Access since Version 1.

    I'll make sure to be very specific with my Order By instructions in the future!

    Cheers

    Alison C

  11. #10
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Sorry, Mike - I somehow missed a couple of posts when I first was dealing with this problem. I agree with you about the dates, and am almost paranoid to ensure that anywhere I use them I coerce them to the UK/Australian format; I think it came down to David's observation that more recent versions of Access no longer hold the sort order of underlying queries, making the ORDER BY caluse that you endorsed the solution to my problem!

  12. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Sorry, John - I somehow missed a couple of posts when I first was dealing with this problem. Yes, I'm, usually fairly careful with my indexes - as you say, it's all about judicious use to make things fly!judicious The dateabase ire T The database has a substantial volume of raw data, but is well within the capabilities of Access, so I think it's just a matter of adapting to the way later versions deal with sorting.

Posting Permissions

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