Results 1 to 10 of 10
  1. #1
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35

    What determines the sequence records are displayed in queries without any sorts?

    I'm digging into some field formatting issues in another post, but ran into a question about how queries sequence the data.

    There are four Unions that ultimately bring together 60-70 individual linked Excel tables into one table. Three of the Unions are simple Select statements that collectively include all of the tables. The final Union simply selects these three Unions to create the final table.

    I expected the three unions to sequence the data in the same order as the Select statements, but one appears to not follow that approach. My practice has been to keep the Select statements alphabetical order by file name, but I noticed that was not the case for the one Union. It's as if the Union were sorting the Selects by file name before actually retrieving the records.

    How do Unions with just simple Select * from filename statements, and without any sorts or other selection criteria, sequence their output?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    AFAIK a query returns data in no particular order unless it is specified in the table/query
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I echo what Bob wrote. Unless an order is specified in the query, the records may be in a different order each time you open the query.
    Whilst it may sort by the first field, there is no guarantee of that happening.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Bob is correct when it comes to simple SELECT queries, but something about your post isn't clear.
    the final Union simply selects these three Unions to create the final table.
    A make table query ought to be making a table with the results of the union. Also, within the last week or so I read something about an effect pertaining to the order of union statements but I can't recall what that was about. Something to do with the first (or was it last?) union has an over-riding effect. I might be able to find it, but I think Bob might have been involved in that thread as well.
    Bob: it was at AWF
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Normally the ORDER BY is based on the final section of the UNION
    See https://www.techonthenet.com/sql/union.php
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    Thanks all of you!

    I'm not sure I understand completely, but there is no ORDER BY in any of the Unions. They're just a series of SELECT * statements. Didn't see anything in the union.php link that explained sequencing when there are no ORDER BYs. Might have just missed it.

    As far as the Union of Unions, I seem to recall in the past that there's a limit to the number of SELECT statements in a Union, so I parsed the list over three Unions. (I did group them by whether the accounts were current, inactive, or archive, but the result was that none of them exceeded the SELECT statement limit.

    I could have selected multiple Unions in the relationship table in the final Query, but chose instead to add the last Union which simple selected the other three.

    The final Query uses that Union, along with the lookup tables, in the relationship model. The Query pulls every record from the Union (*), and adds additional fields that contain the resolved lookups. A couple Excel files link to that Query and then used Excel functions to analyze and report.

    Hope that's clearer.

  7. #7
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    I did some testing and seem to have confirmed that a UNION with multiple :SELECT * from" statements are sorting the data by the first field, even though there is no SORT by in the UNION.

    This isn't a big deal. I'm searching down an issue where Access was formatting a numeric field as text, and wanted to see which records the UNION first encountered. I'd assumed it would have been from the first records of the first file in the SELECT * list, but the sort I'm seeing makes me wonder whether the UNION is sorting before or after extracting the records.

    I do have a separate post on how Access decides on field formatting.

  8. #8
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    ...should have been "SELECT * from", not :SELECT * from".

    This is an example of the statements: SELECT * from [filename]

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Tom Carp View Post
    I did some testing and seem to have confirmed that a UNION with multiple :SELECT * from" statements are sorting the data by the first field, even though there is no SORT by in the UNION.

    This isn't a big deal. I'm searching down an issue where Access was formatting a numeric field as text, and wanted to see which records the UNION first encountered. I'd assumed it would have been from the first records of the first file in the SELECT * list, but the sort I'm seeing makes me wonder whether the UNION is sorting before or after extracting the records.

    I do have a separate post on how Access decides on field formatting.
    AFAIK If the sort order is important in any way then you should specify it otherwise it may suddenly sort differently, even though it may have seemed to sort the same way many hundreds or times.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    Thanks. The sort order isn't important. Interesting that there seems to be no predictable "default" sort.

    Looking into the sort issue was to help isolate where an Access field formatting decision was being made. There are a few UNIONs and a QUERY that combine transaction data from a number of Excel linked tables, and it appears at least one is ending up with a couple numeric fields formatted as numbers stored as text. Was just trying to narrow the list assuming the first records in a UNION determined the field formatting.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-13-2020, 04:24 PM
  2. Replies: 0
    Last Post: 07-16-2016, 09:32 AM
  3. Macro to run queries in sequence
    By Castillb in forum Macros
    Replies: 1
    Last Post: 06-30-2015, 10:48 AM
  4. Replies: 3
    Last Post: 01-02-2011, 07:17 PM
  5. Run queries in sequence and combo box
    By thart21 in forum Queries
    Replies: 10
    Last Post: 05-28-2010, 03:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums