Order by criteria, with exceptions 2

[PaultheS]

I'm currently working on a database to print individual customer statements. I use a recordset to get all transactions relevant to a particular customer, and then it prints all of these onto a form (paper form, not Access). The SQL statement looks something like this...

SELECT [Balance], [TransactionID]
FROM [tbl: Transactions]
WHERE [CustomerID] = '4775'
ORDER BY [TransactionID]

4775 is just an example, of course. Now, the problem is, there are a couple of transactions that need to be at the bottom of the form. Also, the rest of the transactions need to be sorted by TransactionID. Is there any way I can order the recordset by TransactionID, with an exception to place certain TransactionID's at the end of the recordset?

If there's no simple way, does anyone have other design suggestions for this problem?

 

[GingerR]

How do you determine the "certain transactions" that go at the bottom? is there something in their data, or is it something you/a user only knows? If not in the data, once they are slated as "certain ones that go to the bottom", will they always go to the bottom?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PaultheS]

Sorry, I should've clarified.

Certain specific TransactionID numbers should always be at the bottom. For example, the records should all be ordered by TransactionID, except for records with TransactionID's of 1500 or 2500, which should appear at the bottom.

All of the "certain transactions" are known ahead of time and the numbers will not change.

 

[PHV]

ORDER BY IIf(TransactionID=1500 OR TransactionID=2500, 1, 0), TransactionID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaultheS]

Great idea, thanks PHV.