If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Anyone who can help my fried mind with this solution?
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 he http://www.databasechannel.com/sampl...2007/data.html Any suggestions will be highly appreciated. My best Pindar |
#2
|
|||
|
|||
Anyone who can help my fried mind with this solution?
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... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 |
#3
|
|||
|
|||
Anyone who can help my fried mind with this solution?
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] |
Thread Tools | |
Display Modes | |
|
|