Query sort order apparently ignoring ORDER BY

  • Thread starter Thread starter john.mctigue
  • Start date Start date
J

john.mctigue

I cannot get the following query (SQL below) to sort as desired. I
have attempted to apply Sort: Ascending on the first column,
MySortOrder. I do not have any sort order applied via datasheet
view. Also, the OrderBy property of the query is blank (I have tried
changing this to "MySortOrder" and, qualified by the query name,
"qryzAL_004AMLMetroRuralRemoteSubRptTEST.MySortOrder" to no effect).
The answer returned seems to be sorted on the second column, Locality,
in ascending order, thus:

MySortOrder Locality Count
26 Unknown 2
1 WA: Metro 21
3 WA: Remote 2
2 WA: Rural 1

rather than the desired

MySortOrder Locality Count
1 WA: Metro 21
2 WA: Rural 1
3 WA: Remote 2
26 Unknown 2

Thanks for your attention. Any help would be greatly appreciated.

Kind regards,
John

SELECT Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26) AS MySortOrder,
IIf(Not IsNull([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf
(Not IsNull([State]),[State],"Unknown")) AS Locality, Count(IIf(Not
IsNull([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf(Not
IsNull([State]),[State],"Unknown"))) AS [Count]
FROM (tblPostcodeMapping RIGHT JOIN tblLocalityPostcodes ON
tblPostcodeMapping.Postcode = tblLocalityPostcodes.Pcode) RIGHT JOIN
haema_Diagnosis ON tblLocalityPostcodes.PostcodeLocalityID =
haema_Diagnosis.PostcodeAtDiagnosis
GROUP BY Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26), IIf(Not IsNull
([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf(Not IsNull
([State]),[State],"Unknown"))
ORDER BY Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26);
 
Best method would be to add another column to tblLocalityPostCodes that
would contain a number field representing the sort order you want. I
would use a number field of type double. Then if I needed to add a new
locality and a new sort order I could average the numbers on each side
of the new record for its sort order value.

So if I had
Metro 1
Remote 2
and wanted to insert Rural into the table between Metro and Remote I
would give Rural the sort order value of 1.5. And if I needed to add
another record between Rural and Remove I would give that 1.75 as the
sort value.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


I cannot get the following query (SQL below) to sort as desired. I
have attempted to apply Sort: Ascending on the first column,
MySortOrder. I do not have any sort order applied via datasheet
view. Also, the OrderBy property of the query is blank (I have tried
changing this to "MySortOrder" and, qualified by the query name,
"qryzAL_004AMLMetroRuralRemoteSubRptTEST.MySortOrder" to no effect).
The answer returned seems to be sorted on the second column, Locality,
in ascending order, thus:

MySortOrder Locality Count
26 Unknown 2
1 WA: Metro 21
3 WA: Remote 2
2 WA: Rural 1

rather than the desired

MySortOrder Locality Count
1 WA: Metro 21
2 WA: Rural 1
3 WA: Remote 2
26 Unknown 2

Thanks for your attention. Any help would be greatly appreciated.

Kind regards,
John

SELECT Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26) AS MySortOrder,
IIf(Not IsNull([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf
(Not IsNull([State]),[State],"Unknown")) AS Locality, Count(IIf(Not
IsNull([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf(Not
IsNull([State]),[State],"Unknown"))) AS [Count]
FROM (tblPostcodeMapping RIGHT JOIN tblLocalityPostcodes ON
tblPostcodeMapping.Postcode = tblLocalityPostcodes.Pcode) RIGHT JOIN
haema_Diagnosis ON tblLocalityPostcodes.PostcodeLocalityID =
haema_Diagnosis.PostcodeAtDiagnosis
GROUP BY Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26), IIf(Not IsNull
([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf(Not IsNull
([State]),[State],"Unknown"))
ORDER BY Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26);
Click to expand...
 
Best method would be to add another column to tblLocalityPostCodes that
would contain a number field representing the sort order you want. I
would use a number field of type double.  Then if I needed to add a new
locality and a new sort order I could average the numbers on each side
of the new record for its sort order value.

So if I had
   Metro 1
   Remote 2
and wanted to insert Rural into the table between Metro and Remote I
would give Rural the sort order value of 1.5.  And if I needed to add
another record between Rural and Remove I would give that 1.75 as the
sort value.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================



[email protected] said:
I cannot get the following query (SQL below) to sort as desired.  I
have attempted to apply Sort: Ascending on the first column,
MySortOrder.  I do not have any sort order applied via datasheet
view.  Also, the OrderBy property of the query is blank (I have tried
changing this to "MySortOrder" and, qualified by the query name,
"qryzAL_004AMLMetroRuralRemoteSubRptTEST.MySortOrder" to no effect).
The answer returned seems to be sorted on the second column, Locality,
in ascending order, thus:
Click to expand...
MySortOrder        Locality        Count
26         Unknown         2
1          WA: Metro       21
3          WA: Remote      2
2          WA: Rural       1
Click to expand...
rather than the desired
Click to expand...
MySortOrder        Locality        Count
1          WA: Metro       21
2          WA: Rural       1
3          WA: Remote      2
26         Unknown         2
Click to expand...
Thanks for your attention.  Any help would be greatly appreciated.
Click to expand...
Kind regards,
John
Click to expand...
SELECT Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26) AS MySortOrder,
IIf(Not IsNull([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf
(Not IsNull([State]),[State],"Unknown")) AS Locality, Count(IIf(Not
IsNull([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf(Not
IsNull([State]),[State],"Unknown"))) AS [Count]
FROM (tblPostcodeMapping RIGHT JOIN tblLocalityPostcodes ON
tblPostcodeMapping.Postcode = tblLocalityPostcodes.Pcode) RIGHT JOIN
haema_Diagnosis ON tblLocalityPostcodes.PostcodeLocalityID =
haema_Diagnosis.PostcodeAtDiagnosis
GROUP BY Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26), IIf(Not IsNull
([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf(Not IsNull
([State]),[State],"Unknown"))
ORDER BY Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26);- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

John, Many thanks for replying with a pointer in the right direction.
I created additional tables giving a sort order for State and Metro/
Rural/Remote and joined these, in the query, to the relevant fields in
the LocalityPostcodes and PostcodeMapping tables respectively.
Sorting on these did the trick.

I am surprised that my attempt at a custom sort order did not work, as
I thought this was a common technique. Do you (or anyone else) know
why this might have failed (eg at a guess, because I was attempting to
sort on a calculated field which was based on another calculated
field)? I'm baffled.

Kind regards,
John
 
Random rare bugs in sorting/grouping. I've never come closer than
that to attributing a cause: sometimes complex sorting/grouping
queries don't return the correct result. .

(david)


Best method would be to add another column to tblLocalityPostCodes that
would contain a number field representing the sort order you want. I
would use a number field of type double. Then if I needed to add a new
locality and a new sort order I could average the numbers on each side
of the new record for its sort order value.

So if I had
Metro 1
Remote 2
and wanted to insert Rural into the table between Metro and Remote I
would give Rural the sort order value of 1.5. And if I needed to add
another record between Rural and Remove I would give that 1.75 as the
sort value.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================



[email protected] said:
I cannot get the following query (SQL below) to sort as desired. I
have attempted to apply Sort: Ascending on the first column,
MySortOrder. I do not have any sort order applied via datasheet
view. Also, the OrderBy property of the query is blank (I have tried
changing this to "MySortOrder" and, qualified by the query name,
"qryzAL_004AMLMetroRuralRemoteSubRptTEST.MySortOrder" to no effect).
The answer returned seems to be sorted on the second column, Locality,
in ascending order, thus:
Click to expand...
MySortOrder Locality Count
26 Unknown 2
1 WA: Metro 21
3 WA: Remote 2
2 WA: Rural 1
Click to expand...
rather than the desired
Click to expand...
MySortOrder Locality Count
1 WA: Metro 21
2 WA: Rural 1
3 WA: Remote 2
26 Unknown 2
Click to expand...
Thanks for your attention. Any help would be greatly appreciated.
Click to expand...
Kind regards,
John
Click to expand...
SELECT Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26) AS MySortOrder,
IIf(Not IsNull([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf
(Not IsNull([State]),[State],"Unknown")) AS Locality, Count(IIf(Not
IsNull([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf(Not
IsNull([State]),[State],"Unknown"))) AS [Count]
FROM (tblPostcodeMapping RIGHT JOIN tblLocalityPostcodes ON
tblPostcodeMapping.Postcode = tblLocalityPostcodes.Pcode) RIGHT JOIN
haema_Diagnosis ON tblLocalityPostcodes.PostcodeLocalityID =
haema_Diagnosis.PostcodeAtDiagnosis
GROUP BY Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26), IIf(Not IsNull
([Metro/Rural/Remote]),"WA: " & [Metro/Rural/Remote],IIf(Not IsNull
([State]),[State],"Unknown"))
ORDER BY Switch([Locality]="WA: Metro",1,[Locality]="WA: Rural",2,
[Locality]="WA: Remote",3,[Locality]="ACT",4,[Locality]="NSW",5,
[Locality]="NT",6,[Locality]="QLD",7,[Locality]="SA",8,[Locality]
="TAS",9,[Locality]="VIC",10,[Locality]="Unknown",26);- Hide quoted
text -
Click to expand...

- Show quoted text -
Click to expand...

John, Many thanks for replying with a pointer in the right direction.
I created additional tables giving a sort order for State and Metro/
Rural/Remote and joined these, in the query, to the relevant fields in
the LocalityPostcodes and PostcodeMapping tables respectively.
Sorting on these did the trick.

I am surprised that my attempt at a custom sort order did not work, as
I thought this was a common technique. Do you (or anyone else) know
why this might have failed (eg at a guess, because I was attempting to
sort on a calculated field which was based on another calculated
field)? I'm baffled.

Kind regards,
John
 
The problem IS probably connected to the fact that you are sorting by a
calculated field that uses another calculated field. I prefer the special
sort order field because it is much simpler to use and much simpler to modify
when needed. The problem with reusing a calculated value is that often the
value is not yet available when it is needed or the wrong calculated value is
available.

You should really not use a calculated value in the SWITCH function. You
should redo the calculation each time it is needed. A slightly simpler
expression to get the result you wanted for location would be the following.

Nz("WA: " + [Metro/Rural/Remote],NZ([State],"Unknown"))

So you can either replace every reference to Locality in the calculated field
mySortOrder with the above expression or change your switch function to the
following which does not rely on the calculated value at all.

Switch([Metro/Rural/Remote]="Metro",1
,[Metro/Rural/Remote]="Rural",2
,[Metro/Rural/Remote]"Remote",3
,[State]="ACT",4
,[State]="NSW",5
,[State]="NT",6
,[State]="QLD",7
,[State]="SA",8
,[State]="TAS",9
,[State]="VIC",10
,True,26) AS MySortOrder

I did guess as to which field I should be using to calculate each value. The
last set of arguments (True,26) basically said if no other pair was used then
return 26.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top