Query not sorting correctly between two values with the same count

[Cark]

I have a query which orders my dataset based on the number of times each ATA4DIGIT. I have attached the layout in Image 1.

This query is then linked into my second query which pulls a few relevant fields from the main table and the first query causes the second query's dataset to be ordered based on the Count of ATA4DIGIT from the first query.

When I look at the second query in table view (or look at the export in Excel), the rows seem to be sorted based on ATA4DIGIT, however a few of the rows seems to be in the incorrect order.

As you can see in the image RowsInWrongOrder, a block of 28-22 is first, then 29-15, then 36-11 and then it gets messy around 28-41, 22-11 and 79-41.
If it helps with the troubleshooting, these 3 ATA4DIGITs are all tied with 7 counts each. 28-41 x7, 22-11 x7 and 79-41 x7.

My question is, how do I go about editing my query so that my ATA4DIGITs are grouped together better.

As far as I am aware my first query is working perfectly just how I want it to, so I believe all I need to change is the way my second query sorts its records. The sorting order I want to use is: 1st sort by Count of ATA4DIGIT (the number of instances they appear in my table based on the first query's criteria), 2nd sort by ATA4DIGIT (the code e.g 28-41 etc) and then a 3rd level sort by my date column called DOFYEAR (which is in DD/MM/YYYY format).

How would I go about doing this? I am wanting to keep the column format as is shown in the second query's image.

 

[Frothingslosh]

Your final image only shows one of the three fields you're sorting on. Without the other two fields visible, we can't begin to tell you what's wrong.

My first inclination is that it's doing precisely what it has been told to do, and you don't see that because you don't see the other sort fields. Since your primary sort order is a field not displayed, it's almost guaranteed that the DISPLAYED field will look to be jumbled.

 

[Cark]

Does this show you what you need to know for troubleshooting? This image is from my Excel export (as I wanted to hide some of the confidential data), but is a perfect copy of what is in the Query as it is just a transferspreadsheet export.

Let me know if you want to see anything else.

 

[Frothingslosh]

Your third displayed column is ModelLink, but you said your third sort is done on DOFYEAR.

What I see so far is that it is definitely sorting by [Count of TblTcDel]. However, your query screenshot doesn't show any other sorting being done. Of course, your screenshot also almost certainly isn't showing every field, as indicated by how DOFYEAR doesn't even show up in the displayed QBE grid.

Could I get you to post the actual SQL for that second query so I can see the ORDER By clause? From the screenshot, it doesn't look like you're actually sorting by ATA4DIGIT, but that could very well be because the actual sort is in the part not included in the screenshot. The SQL code would let me see that.

 

[Cark]

Seems as though I have been having yet another nightmare with queries. These queries that I am asking about now have plagued me for weeks with each time there being a different issues / something that I have broken by changing things.

Seems as though this time I hadn't added an extra column to sort by ATA4DIGIT from the first query. My second query's end bit now looks like the attached image.

I haven't quite added the sorting by date yet as the sorting by date isn't 100% essential, I just wanted to make sure I could get the sorting by ATA4DIGIT working properly first. Hopefully I shouldn't have any more issues with it.

Weirdly, I didn't have this problem with a similar query design when I didn't have any sorting criteria and used ATA2DIGIT (a column which just takes the first 2 from ATA4DIGIT) and mapped the first query onto the second. Queries are mysterious things.....

 

[The_Doc_Man]

Cark, the problem in English is easy to state.

When you layer queries and have an ORDER BY in a deeper layer, it is often overridden by the top level layer. There is no guarantee that an inner sort will be preserved by an outer sort.

If you want a multi-column ordering, you can use more than one field in an ORDER BY clause. Just do your COUNT functions (and any other SQL Aggregates) in an inner layer and then do ALL of the sorting in the outermost (presentation) query. You don't even have to show all of the fields by which the sort occurs. They just have to be present in the recordset that is feeding the outer query.

SELECT A, B, C FROM TABLE X ORDER BY D, E, F ;

If you need reverse order, you can of course include DESC behind the field name in any member of the ORDER BY clause.

 

[Frothingslosh]

As a rule, it's best to do all the sorting in the FINAL query when you're using stacked queries. The only real exception I can think of offhand would be when the sort order of a lower-level query is actually important to its own results - usually when you're doing a SELECT TOP X rather than SELECT.

That way you don't get into the habit of assuming that record order will be the same as in the lower level queries. It CAN be, and often will be, but it is by no means guaranteed.

Additionally, sorting lower level queries and then re-sorting them in the final query uses up processing time. On smaller recordsets (say, < 100k records) it probably isn't a big deal, but as you get into larger and larger recordsets, the execution times can sometimes start skyrocketing.

***

I assume by the last post that you're now getting the results you wanted?

 

[Cark]

Those two posts were really informative. Yes, I am now getting the results I was wanting.

Your last post FrothingSlosh seemed to explain the reason for me not having an issue with my ATA2DIGIT query and having an issue with my ATA4DIGIT query. In my First ATA2DIGIT query I was isolating the top 10, whereas in my First ATA4DIGIT query I was just ordering the list by count.

Following this issue, queries are a little less mysterious than they once were