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/sampl...2007/data.html

Any suggestions will be highly appreciated.

My best
Pinda

On Mon, 3 May 2010 21:19:00 +0000 (UTC), NY wrote:

>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?
--

John W. Vinson [MVP]