Anyone who can help my fried mind with this solution?
in [Queries]
|
Prev: DateDiff with variable criteria?
Next: Function is not availalbe in expressions in query expression error message (0/1)
From: NY on 3 May 2010 17:19 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 Pindar
From: PieterLinden via AccessMonster.com on 3 May 2010 18:00 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/Forums.aspx/access-queries/201005/1
From: John W. Vinson on 4 May 2010 01:11
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] |