Solved

Custom ORDER BY in MS Access

Posted on 2009-04-01
18
1,347 Views
Last Modified: 2013-11-28
Hello, it is possible to customize the order by from a query? I have one table in MS Access 2007 with a text field. I want to order the result form the query in following way:

1. Field with suffix "_HH"
2. Field with suffix "_H"
3. Field with suffix "_L"
4. Field with suffix "_LL"
5. Other suffix (alt. order by ASC)

Thank you!

SELECT tblVariable.TagName FROM tblVariable

ORDER BY tblVariable.TagName ASC

Open in new window

0
Comment
Question by:tselectro
  • 8
  • 7
  • 3
18 Comments
 
LVL 15

Expert Comment

by:MNelson831
Comment Utility
Try uising a CASE statement in the order by clause:

Order By
     CASE
          WHEN right(FieldName, 2) = 'HH' then 1
          WHEN right(FieldName, 2) = '_H' then 2
          WHEN right(FieldName, 2) = 'LL' then 3
          WHEN right(FieldName, 2) = '_L' then 4
          Else 5
     END

0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
I would use the SWITCH function like this

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",5,True,5)

Cheers, Andrew

PS Access doesn't support the SQL CASE statement
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
How about:

ORDER BY Mid(fldStr,InStrRev(fldStr,"_")),fldStr

MNelson831: Does A2007 handle Case?  Unless it's been changed in A2007, when you provide an integer in the ORDER BY field, Jet picks up the field in the SELECT clause indicated by the number.
0
 
LVL 15

Expert Comment

by:MNelson831
Comment Utility
mea culpa - I jumped here from the SQL Server page and didn't realize the question was in Access.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
To correct Andrew's:

SELECT fldText FROM tblName ORDER BY Switch(Right(fldText,3)="_HH",1,Right(fldText,2)="_H",2Right(fldText,2)="_L",3,Right(fldText,3)="_LL",4,True,5) & fldText;

0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 250 total points
Comment Utility
You could also build a seperate table that look slike this this:

TextData                  SortOrder
_HH                             1
_H                               2
_LL                              3
_L                               4


Then left join that table like this:

Select * from MyRegularTable left outer join MySortTable
on Right(MyFieldName, len(MySortTable.TextData)) = MySortTable.TextData
order by nz(MySortTable.SortOrder,99)



0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 250 total points
Comment Utility
To correct it and to fix the ones missed also by GRayL

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2,Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",4,True,5)

I think that covers them all.

Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Andrew:  Pls run this simple query in A2007

SELECT anyFld FROM anyTable ORDER BY 1;

using your own field and table names.  Does that work, ie. order by anyFld?

0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
tselectro:  Did you try my solution at http:#a24042090 ??
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Andrew, I don't have A2007.  Does the use of an integer in the ORDER BY clause call up the relative field from the SELECT clause?

Ray
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
GRayL there is an error in the SWITCH(), it's missing a comma after the 2.
Also please note the requested order of HH, _H, _L and LL this is not a standard sort order.
Finall Yes ORDER BY 1 works in Access 2007 as it did in previous versions.
Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Then you realize you were awarded a solution where the asker did not test it.  Your solution would have tried to call up a field, depending on how the Switch() evaluated.  If it rendered a value higher than the number of fields, it will throw an error.
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
GRayL, I dont understand what you are getting at. I fixed the issue in the posting that was accepted as an assisted solution. The Switch will carry out 5 tests, the first 4 against the value of the last 2 characters of the field and the 5th being the ELSE as the 9th parameter is True and will return 5.

As to whether it was tested by tselectro then I don't know, as far as I can tell the assisted solution works.

Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
your query:

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2,Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",4,True,5)

Calls up a field named Suffix ?? in the Switch() function.  However Switch() evaluates, one value of 1 thru 5 per record, will result in Jet asking for the first through fifth field from the SELECT clause - for each record.  As you only listed one field, as soon as Switch() evaluates to 2 or higher, it will generate an error - if not before during compile.

That is why I ampersanded the field name to the result of the switch function.  Sorry I copied your missing comma but them's the breaks.  Understand now?

0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Suffix is the name of the field, it is checking the RIGHT most 2 characters of the field then same way the CASE example whould have done in SQL Server it is not referencing additional fields, it is just a flat form ofthe nesting of IIF's

I think you may be mixing up SWITCH() in VBA with CHOOSE() in VBA

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
GRayL, just realised what you are getting at with the ORDER BY 2 sorry. No it is a value that will be sorted as a number 2 rather than the column location. Bit slow of me realising what you mean, one eye on the footie.
Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Hi GRayL, a bit more refreshed after too much celebrating of the footie. I have double checked this and as I thought as soon as you put an expression in the ORDER BY you loose the ability to ORDER BY column position for that specific ORDER BY column. This is in Access and as far as I am aware is the same in all other SQL languages that support ORDER BY 1

ORDER BY 1,2 will sort by the first column then the second
ORDER BY 1, IIf(IsNull([Field2]),2,1) will sort sort by the first column then the NOT NULL Field2 Records followed by the NULL Field2 records
ORDER BY IIf(IsNull([Field2]),1,2) DESC, 1 will sort sort by the NOT NULL Field2 Records followed by the NULL Field2 records then the first column.

I hope this clarifies the situation for everyone with regards to the ORDER BY column location.

Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now