Query that returns same record last every time run

[EddyLLC]

I'm querying a field in a table that contains two letters, for example MF, SE, DT, and so on. I would like to order the query results so a specific pair is returned last every time the query is run. I guess the sort really doesn't matter I just want DT for example to be returned last in the query every time it is run. So the results of the query would look like this, the order of the first three matters not:
MF
CD
HO
DT

Is this possible?
Thanks

 

[MajP]

If you want to sort things in some natural contextual order then add to the table a sort order field.

tblPairs
PairText
sortOrder

So for things like

Big 3
Small 2
Giant 4
Tiny 1

You can order by sortID
tiny
Small
Big
Giant

In your case you could
MF 1
CD 1
HO 1
DT 0

 

[traingamer]

Or

link to a sort order table (similar to MajP's suggestion)

Or

use a calculated field as an Order By clause (not a good choice for big tables)

Order By iif([yourfield] = "DT", 0, 1) desc

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Andrzejek]

Or....

If you want to have this first (in any order):
[tt]
MF
CD
HO[/tt]

and then always have [tt]DT[/tt] last

To get the first portion you may want to do:

SELECT MyField, '10' AS ORDERFIELD
FROM MyTable
WHERE MyField <> 'DT'

To get the second part, you may do this:

SELECT MyField, '20' AS ORDERFIELD
FROM MyTable
WHERE MyField = 'DT'

So, to get what you want, you may do this:

SELECT MyField, '10' AS ORDERFIELD
FROM MyTable
WHERE MyField <> 'DT'[blue]
UNION[/blue]
SELECT MyField, '20' AS ORDERFIELD
FROM MyTable
WHERE MyField = 'DT'
ORDER BY 2

BTW, I hope Access can do UNIONs....

Have fun.

---- Andy