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.
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.