Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Custom ORDER BY in MS Access

Posted on 2009-04-01
18
Medium Priority
?
1,431 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
18 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24041922
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
ID: 24041978
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
ID: 24042035
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:MNelson831
ID: 24042080
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
ID: 24042090
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 750 total points
ID: 24042132
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 750 total points
ID: 24042177
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
ID: 24042264
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
ID: 24042472
tselectro:  Did you try my solution at http:#a24042090 ??
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24042501
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
ID: 24042571
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
ID: 24042703
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
ID: 24042842
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
ID: 24043018
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
ID: 24043142
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
ID: 24043169
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
ID: 24046995
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
ID: 24051485
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 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