Anyone who can help my fried mind with this solution?

[NY]

Dear all,

I am writing code about custom sorting tecniques. This code will appear
in my upcoming book. I explain two ways: one by using the switch function
and the other by using a lookup table. Although both solutions work as they
are supposed to, with respect to the sorting criterion (state), the records
WITHIN each state do not appear in the same exact order. I am fried to think
anymore at this point. I wrote too much code.

This is the custom sorting order using switch

SELECT *
FROM Qry_Conditions
WHERE STATE in ("NY","CA","TX")
ORDER BY
SWITCH(
[state]= 'NY', 1,
[state]= 'CA', 2,
[state] = 'TX', 3
)


This is the custom sorting order using a lookup table

SELECT * FROM Qry_Conditions
INNER JOIN tblS_State ON Qry_Conditions.state = tblS_State.State;

The tbls_State contains:
1 NY
2 CA
3 TX

If you want to download the database to play with the code, you can do that
here:
http://www.databasechannel.com/sampledata/Access2007/data.html

Any suggestions will be highly appreciated.

My best
Pinda

 

[PieterLinden via AccessMonster.com]

Pinda,

Maybe this is not what you wanted... hard to tell.

How about...
SELECT States.[State Abbreviation], States.[State Name], States.
CustomSortOrder
FROM States
ORDER BY States.CustomSortOrder;


Where CustomSortOrder is a column in States with a unique index, so you can
sequence your states any way you want and just sort by the hidden SortField...
Can't think of any other sensible way of doing it...

 

[John W. Vinson]

I am writing code about custom sorting tecniques. This code will appear
in my upcoming book. I explain two ways: one by using the switch function
and the other by using a lookup table. Although both solutions work as they
are supposed to, with respect to the sorting criterion (state), the records
WITHIN each state do not appear in the same exact order.

I wouldn't expect them to. Access will display the records in whatever order
it finds convenient, based on the query plan and the disk storage order of the
records, unless you have a second sort field. Why would you *expect* unsorted
records to appear in sorted order?