Link to home
Create AccountLog in
Avatar of dzirkelb
dzirkelbFlag 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.
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

My that is a lot of or statements. Just add "order by fieldname" at the end.
Avatar of 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.
Order by field1,field2,field3

If this isn't right I'm confused. Please explain in more detail
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
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
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.
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
                              ...
                            )

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.

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




 
Avatar of NicKno
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?
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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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)
forgive my shouting

DID YOU TRY THE FUNCTION METHOD I RECOMMENDED ABOVE?

that technique will accomplish what you want.
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.
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,

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.
Yeah, I have a problem with a lack of availability of SQL Server as well.  That would make my life sooooo much easier.
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
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')
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...
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.


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.