Avatar of dzirkelb
dzirkelb
Flag for United States of America asked on

Need help with an order by clause in access 2007

I am running a query where I would want the results to be ordered in the order of the where clause statement, is this something possible?

Currently, this is what I have:
SELECT Field, Description, DataType, MaximumLength, PointsPossible FROM SchemaInfo WHERE (TableName = 'VideoShopData') AND ([Field] = 'Restaurant') OR ([Field] = 'ShopDate') OR ([Field] = 'AmountSpent') OR ([Field] = 'PKID') OR ([Field] = 'ShopStartTime') OR ([Field] = 'Item1') OR ([Field] = 'Item2') OR ([Field] = 'Item3') OR ([Field] = 'Item4') OR ([Field] = 'Drink') OR ([Field] = 'Item1Temp') OR ([Field] = 'Item2Temp') OR ([Field] = 'Item3Temp') OR ([Field] = 'Item4Temp') OR ([Field] = 'DrinkBrix') OR ([Field] = 'Item1Weight') OR ([Field] = 'Item2Weight') OR ([Field] = 'Item3Weight') OR ([Field] = 'Item4Weight') OR ([Field] = 'FriendlyGreeting') OR ([Field] = 'FriendlyGreetingNotes') OR ([Field] = 'AppreciativeClosing') OR ([Field] = 'AppreciativeClosingNotes') OR ([Field] = 'EmployeesBehavingProfessionally') OR ([Field] = 'EmployeesBehavingProfessionallyNotes') OR ([Field] = 'CustomersTreatedRespectfully') OR ([Field] = 'CustomersTreatedRespectfullyNotes') OR ([Field] = 'EmployeeAppearance') OR ([Field] = 'EmployeeAppearanceNotes') OR ([Field] = 'TranslationCorrect') OR ([Field] = 'TranslationCorrectNotes') OR ([Field] = 'ParkingLot') OR ([Field] = 'ParkingLotNotes') OR ([Field] = 'BuildingExterior') OR ([Field] = 'BuildingExteriorNotes') OR ([Field] = 'DriveThru') OR ([Field] = 'DriveThruNotes') OR ([Field] = 'FrontCounter') OR ([Field] = 'FrontCounterNotes') OR ([Field] = 'SelfService') OR ([Field] = 'SelfServiceNotes') OR ([Field] = 'OutOfProduct') OR ([Field] = 'OutOfProductNotes') OR ([Field] = 'OrderAccurate') OR ([Field] = 'OrderAccurateNotes') OR ([Field] = 'Appearance') OR ([Field] = 'AppearanceNotes') OR ([Field] = 'Portioning') OR ([Field] = 'PortioningNotes') OR ([Field] = 'Temperature') OR ([Field] = 'TemperatureNotes') OR ([Field] = 'GreetedWithinLimit') OR ([Field] = 'GreetedWithinLimitNotes') OR ([Field] = 'CustomerCount') OR ([Field] = 'TotalServiceTimeMinutes') OR ([Field] = 'TotalServiceTimeSeconds') OR ([Field] = 'PositiveFeedback') OR ([Field] = 'OverallComments')

It has a couple of the comments fields in the beginning of the results, when I want the results to be ordered in the order of the where statment of the Field = xxx.

is this possible or do I have to add another field to do the sort manually?

This is an access 2003 database.  I am basically creating my own schema info like I would in sql server.
DatabasesMicrosoft AccessSQL

Avatar of undefined
Last Comment
dzirkelb

8/22/2022 - Mon
Aaron Tomosky

My that is a lot of or statements. Just add "order by fieldname" at the end.
dzirkelb

ASKER
It is a function that I have built that draws the schema info of the fields in teh where statement.

That being said, I can't do a sort by field name as it will then put it in alphabetical order, and not the order of how they appear in the where clause.
Aaron Tomosky

Order by field1,field2,field3

If this isn't right I'm confused. Please explain in more detail
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Dale Fye

you might be able to use the SWITCH( ) function in the ORDER BY CLAUSE, something like:

ORDER BY SWITCH(TableName = 'VideoShopData' AND [Field] = 'Restaurant', 1, _
                              [Field] = 'ShopDate', 2. _
                              [Field] = 'AmountSpent', 3, _
                              [Field] = 'PKID', 4, _
                              [Field] = 'ShopStartTime', 5, _

and so on.

Another method would be to create another table which contains the [Field] and [Sort Order] (I frequently include [Sort_Order] fields in my lookup tables).  This way, you could join that table to your query on the [Field] field and use the [Sort_Order] field in the ORDER BY clause
Dale Fye

If you go with the "another table" route, you might want to consider adding a Yes/No field (I call mine "IsSelected") to the table which would allow you select specific [Field] values to include in the query.  Then, instead of checking for a specific value with syntax like:

WHERE [Field] = "a" or [Field] = "b" or ...

YOu would do something like:

SELECT Field, Description, DataType, MaximumLength, PointsPossible
FROM SchemaInfo
INNER JOIN tblFields
ON SchemaInfo.Field = tblFields.Field
WHERE TableName = 'VideoShopData' AND tblFields.IsSelected = True
ORDER BY tblField.Sort_Order
dqmq

expressing the where clause like this may be more readable:

WHERE ... and  [Field] in ('Restaurant','ShopDate',....'OverallComments')

I don't understand your sort order requirement, but it works just like SQL Server.  Please restate it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dqmq

Never mind my confusion on the sort order requirement. I get it now.  Use Switch function, but not quite as shown above:

order by switch([Field] = 'Restaurant', 1, _
                              [Field] = 'ShopDate', 2. _
                              [Field] = 'AmountSpent', 3, _
                              [Field] = 'PKID', 4, _
                              [Field] = 'ShopStartTime', 5
                              ...
                            )

dzirkelb

ASKER
It is hard to explain why I do it this way, but it is basically my own customized vbscript functions specific to this company.  They do not have a sql back end database, so I created my own table which will hold the data to query on multiple pages.

Now, that being said, I need it to go in the order of the where clause, just because of the specific code again.  The order of the where clause will determine the order they are displayed on the page.

Here is the error I am getting when trying with the switch commands:

[Microsoft][ODBC Microsoft Access Driver] Expression too complex in query expression 'SWITCH(TableName = 'VideoShopData' AND [Field] = 'Restaurant', 0, [Field] = 'ShopDate', 1, [Field] = 'AmountSpent', 2, [Field] = 'PKID', 3, [Field] = 'ShopStartTime', 4, [Field] = 'Item1', 5, [Field] = 'Item2', 6, [Field] = 'Item3', 7, [Field] = 'Item4', 8, '.

I also removed the TableName = 'VideoShopData" and received the same error.

Here is the entire query:

SELECT Field, Description, DataType, MaximumLength, PointsPossible FROM SchemaInfo WHERE (TableName = 'VideoShopData') AND ([Field] = 'Restaurant') OR ([Field] = 'ShopDate') OR ([Field] = 'AmountSpent') OR ([Field] = 'PKID') OR ([Field] = 'ShopStartTime') OR ([Field] = 'Item1') OR ([Field] = 'Item2') OR ([Field] = 'Item3') OR ([Field] = 'Item4') OR ([Field] = 'Drink') OR ([Field] = 'Item1Temp') OR ([Field] = 'Item2Temp') OR ([Field] = 'Item3Temp') OR ([Field] = 'Item4Temp') OR ([Field] = 'DrinkBrix') OR ([Field] = 'Item1Weight') OR ([Field] = 'Item2Weight') OR ([Field] = 'Item3Weight') OR ([Field] = 'Item4Weight') OR ([Field] = 'FriendlyGreeting') OR ([Field] = 'FriendlyGreetingNotes') OR ([Field] = 'AppreciativeClosing') OR ([Field] = 'AppreciativeClosingNotes') OR ([Field] = 'EmployeesBehavingProfessionally') OR ([Field] = 'EmployeesBehavingProfessionallyNotes') OR ([Field] = 'CustomersTreatedRespectfully') OR ([Field] = 'CustomersTreatedRespectfullyNotes') OR ([Field] = 'EmployeeAppearance') OR ([Field] = 'EmployeeAppearanceNotes') OR ([Field] = 'TranslationCorrect') OR ([Field] = 'TranslationCorrectNotes') OR ([Field] = 'ParkingLot') OR ([Field] = 'ParkingLotNotes') OR ([Field] = 'BuildingExterior') OR ([Field] = 'BuildingExteriorNotes') OR ([Field] = 'DriveThru') OR ([Field] = 'DriveThruNotes') OR ([Field] = 'FrontCounter') OR ([Field] = 'FrontCounterNotes') OR ([Field] = 'SelfService') OR ([Field] = 'SelfServiceNotes') OR ([Field] = 'OutOfProduct') OR ([Field] = 'OutOfProductNotes') OR ([Field] = 'OrderAccurate') OR ([Field] = 'OrderAccurateNotes') OR ([Field] = 'Appearance') OR ([Field] = 'AppearanceNotes') OR ([Field] = 'Portioning') OR ([Field] = 'PortioningNotes') OR ([Field] = 'Temperature') OR ([Field] = 'TemperatureNotes') OR ([Field] = 'GreetedWithinLimit') OR ([Field] = 'GreetedWithinLimitNotes') OR ([Field] = 'CustomerCount') OR ([Field] = 'TotalServiceTimeMinutes') OR ([Field] = 'TotalServiceTimeSeconds') OR ([Field] = 'PositiveFeedback') OR ([Field] = 'OverallComments') ORDER BY SWITCH(TableName = 'VideoShopData' AND [Field] = 'Restaurant', 0, [Field] = 'ShopDate', 1, [Field] = 'AmountSpent', 2, [Field] = 'PKID', 3, [Field] = 'ShopStartTime', 4, [Field] = 'Item1', 5, [Field] = 'Item2', 6, [Field] = 'Item3', 7, [Field] = 'Item4', 8, [Field] = 'Drink', 9, [Field] = 'Item1Temp', 10, [Field] = 'Item2Temp', 11, [Field] = 'Item3Temp', 12, [Field] = 'Item4Temp', 13, [Field] = 'DrinkBrix', 14, [Field] = 'Item1Weight', 15, [Field] = 'Item2Weight', 16, [Field] = 'Item3Weight', 17, [Field] = 'Item4Weight', 18, [Field] = 'FriendlyGreeting', 19, [Field] = 'FriendlyGreetingNotes', 20, [Field] = 'AppreciativeClosing', 21, [Field] = 'AppreciativeClosingNotes', 22, [Field] = 'EmployeesBehavingProfessionally', 23, [Field] = 'EmployeesBehavingProfessionallyNotes', 24, [Field] = 'CustomersTreatedRespectfully', 25, [Field] = 'CustomersTreatedRespectfullyNotes', 26, [Field] = 'EmployeeAppearance', 27, [Field] = 'EmployeeAppearanceNotes', 28, [Field] = 'TranslationCorrect', 29, [Field] = 'TranslationCorrectNotes', 30, [Field] = 'ParkingLot', 31, [Field] = 'ParkingLotNotes', 32, [Field] = 'BuildingExterior', 33, [Field] = 'BuildingExteriorNotes', 34, [Field] = 'DriveThru', 35, [Field] = 'DriveThruNotes', 36, [Field] = 'FrontCounter', 37, [Field] = 'FrontCounterNotes', 38, [Field] = 'SelfService', 39, [Field] = 'SelfServiceNotes', 40, [Field] = 'OutOfProduct', 41, [Field] = 'OutOfProductNotes', 42, [Field] = 'OrderAccurate', 43, [Field] = 'OrderAccurateNotes', 44, [Field] = 'Appearance', 45, [Field] = 'AppearanceNotes', 46, [Field] = 'Portioning', 47, [Field] = 'PortioningNotes', 48, [Field] = 'Temperature', 49, [Field] = 'TemperatureNotes', 50, [Field] = 'GreetedWithinLimit', 51, [Field] = 'GreetedWithinLimitNotes', 52, [Field] = 'CustomerCount', 53, [Field] = 'TotalServiceTimeMinutes', 54, [Field] = 'TotalServiceTimeSeconds', 55, [Field] = 'PositiveFeedback', 56, [Field] = 'OverallComments', 57)

In my code, I have a function that creates this query, so making it is not bad at all.  I just put the field names in one line of code at the beginning of the page, and then all teh functions I created use that to do whatever is needed.  For example, one page I revamped had over 1000 lines of code, now it has 30.

dqmq

So, the company-specific sort order is essentially buried in your code.  You could, of course, create another function that when given the field returns it's sort order.  That at least isolates the sort-order to that function.

But, my solution would be to create another table with two columns:  [field] and [sortseq].  Join to that table on [field] for the order by column.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dzirkelb

ASKER
I would really like to avoid creating a new table, because some pages would sort different from others.  One page may pull 3 fields, one may pull 5, one may pull 10.

In SQL, I have it like this:

SELECT column_name AS Field, sysproperties.value AS Description, data_type AS DataType, CHARacter_maximum_length AS MaximumLength FROM INFORMATION_SCHEMA.COLUMNS AS InformationSchema INNER JOIN sysproperties ON sysproperties.id = OBJECT_ID(InformationSchema.TABLE_SCHEMA + '.' + InformationSchema.TABLE_NAME) AND sysproperties.smallid = InformationSchema.ORDINAL_POSITION AND sysproperties.name = 'MS_Description' WHERE (table_name = 'CustomerContact') AND (column_name = 'ID' or column_name = 'FirstName' or column_name = 'LastName' or column_name = 'JobPosition' or column_name = 'JobTitle' or column_name = 'EmailAddress' or column_name = 'ContactPhoneNumber' or column_name = 'CompanyName' or column_name = 'DeeCustomer#' or column_name = 'StreetAddress' or column_name = 'City' or column_name = 'State' or column_name = 'EngineeringNewsletter' or column_name = 'EngineeringNewsletterElectronic' or column_name = 'PurchasingNewsletter' or column_name = 'PurchasingNewsletterElectronic' or column_name = 'ManagementNewsletter' or column_name = 'ManagementNewsletterElectronic' or column_name = 'EzineInsideSales' or column_name = 'EzineTech' or column_name = 'ControlPanelNewsletter' or column_name = 'ControlPanelNewsletterElectronic' or column_name = 'DreamClientAcquisitionProcess') ORDER BY CASE WHEN column_name = 'ID' THEN 1 WHEN column_name = 'FirstName' THEN 2 WHEN column_name = 'LastName' THEN 3 WHEN column_name = 'JobPosition' THEN 4 WHEN column_name = 'JobTitle' THEN 5 WHEN column_name = 'EmailAddress' THEN 6 WHEN column_name = 'ContactPhoneNumber' THEN 7 WHEN column_name = 'CompanyName' THEN 8 WHEN column_name = 'DeeCustomer#' THEN 9 WHEN column_name = 'StreetAddress' THEN 10 WHEN column_name = 'City' THEN 11 WHEN column_name = 'State' THEN 12 WHEN column_name = 'EngineeringNewsletter' THEN 13 WHEN column_name = 'EngineeringNewsletterElectronic' THEN 14 WHEN column_name = 'PurchasingNewsletter' THEN 15 WHEN column_name = 'PurchasingNewsletterElectronic' THEN 16 WHEN column_name = 'ManagementNewsletter' THEN 17 WHEN column_name = 'ManagementNewsletterElectronic' THEN 18 WHEN column_name = 'EzineInsideSales' THEN 19 WHEN column_name = 'EzineTech' THEN 20 WHEN column_name = 'ControlPanelNewsletter' THEN 21 WHEN column_name = 'ControlPanelNewsletterElectronic' THEN 22 WHEN column_name = 'DreamClientAcquisitionProcess' THEN 23 ELSE 99 END

And, this works the way I want it to, but I don't think that will go into access.
Dale Fye

I'm not sure how many elements the Switch function will accept, but I tried to implement your SWITCH statement in the immediate window, and got an error until I changed all of the single quotes to quotes.  Then it worked.

Although I personally prefer the separate table method, it might meet your needs to create a function and use that in your ORDER BY clause.  Something like:

Public Function fnSortBy(SomeValue as Variant) as long

    SomeValue = NZ(SomeValue, "zzz")
    Select Case SomeValue
        Case “Restaurant”
            fnSortBy = 0
        Case “ShopDate”
            fnSortBy = 1
        Case "AmountSpent”
            fnSortBy = 2
     ...
         Case Else 99
     End Select

End Function

Then your sort order would be determined by:

ORDER BY fnSortBy([Field])
dqmq

But consider a table structure like this:

Create table PageField(PageID, Field, SortSeq)


Then you don't need the query builder function and all the messy stuff embedded in your web script.  Just a query like this on each page:


SELECT S.Field, S.Description, S.DataType, s.MaximumLength, S.PointsPossible
  FROM SchemaInfo as S inner join PageField F on S.Field = P.Field
WHERE P.PageID = [PageID] ORDER BY S.SortSeq




 
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NicKno

Can't you just go to designer mode with your query and add an order to the expression then switch back to sql mode?
dzirkelb

ASKER
What do you mean, "add an order to the expression"?  I'm not sure how to do that.

As of now, I have made a seperate table for this, but I really don't want to do that if possible.
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
NicKno

As i explained:
- Open your Query (I think you should then be in SQL-View).
- Now switch to Designer Mode. (At the top left of the Access-Window under the Office-Button)
- now you should see all your fields and your huge expression displayed as single fields and some of them with conditions.
- go to your huge expression and select your wished order in the "Sort" row.

Now you can go back to SQL-Mode or Execute your Statement via the Execute-Button.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dzirkelb

ASKER
If I'm understanding you NicKno, all you are basically telling me to do is add a ORDER BY [Field] ASC to the end of my sql statement, which is the same as going into design mode and selecing the Ascending drop down under sort, is that correct?  If so, that is not what I need as it puts them in alphabetical order then, and I don't want that.  I want to display them in a specific order, which is not normal for any standards (not 1,2,3 and not a,b,c, but more like b,2,3,a,c,1...thats just an example)
Dale Fye

forgive my shouting

DID YOU TRY THE FUNCTION METHOD I RECOMMENDED ABOVE?

that technique will accomplish what you want.
dzirkelb

ASKER
Your function will not produce what I am looking for, I am not coding in Access, it is just the database that will house the data.  So, VB is out, VBscript is in.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Fye

did you try modifying the SWITCH function so that the expressions contained quotes rather than apostrophes?  So instead of:

SWITCH([Field] = 'Restaurant', 0, [Field] = 'ShopDate', 1, [Field] = 'AmountSpent', 2, [Field] = 'PKID', 3, [Field] = 'ShopStartTime', 4, [Field] = 'Item1', 5, [Field] = 'Item2', 6,

you would have:

SWITCH([Field] = "Restaurant", 0, [Field] = "ShopDate", 1, [Field] = "AmountSpent", 2, [Field] = "PKID", 3, [Field] = "ShopStartTime", 4, [Field] = "Item1", 5, [Field] = "Item2", 6,

dzirkelb

ASKER
The switch function will not work in Access, there are too many items in the switch clause.  The error is shown above, too complex a query.  I removed all but a couple and it worked fine, so I know it is the amount in the query that is causing the issue.

If only I could talk them into getting sql as a backend database, then I wouldn't have to build the SQL server's built in schema information...sucks.
Dale Fye

Yeah, I have a problem with a lack of availability of SQL Server as well.  That would make my life sooooo much easier.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dzirkelb

ASKER
Yes, i agree.  My main job I have whatever I want, which pretty much rules :)

However, this consulting job, their server is from 1999, is on it's last leg, and they still won't upgrade to server 2008 sbs with sql...argh
Aaron Tomosky

Just to shorten and clarify the SQL statement an probably make it faster, let's rewrite the middle section as "in". Like this:

Old way:
column_name = 'JobTitle' or column_name = 'EmailAddress' or column_name = 'ContactPhoneNumber' or column_name = 'CompanyName' or column_name = 'DeeCustomer#' or column_name = 'StreetAddress' or column_name = 'City' or column_name = 'State' or column_name = 'EngineeringNewsletter'

New way:
column_name in ( 'JobTitle','EmailAddress','ContactPhoneNumber','CompanyName','DeeCustomer#','StreetAddress','City' , 'State','EngineeringNewsletter')
Aaron Tomosky

Back to a possible solution:
Can you do a union all on the results? So just select where column_name = 'first sorted thing'
Union all
Select where column_name = 'second sorted thing'


Etc...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dqmq

The UNION idea will probably work, but you need to add the sorting column:


select * from
(
select 1 as sortseq, * where column_name = 'first sorted thing'
Union all
Select 2, *                   where column_name = 'second sorted thing'
....
) as x
order by sortseq

Don't know if it will survive your performance expectations, though.


dzirkelb

ASKER
ya, I'm afraid the union option is going to be too performance laggy.  I am going to stick with the adding a column in the schema table for each page that I need to sort by, it works, is fast, just a pain to do.