Is this possible? Sorting a query categorically and not alphabetically

[Rohdester]

Hi,

I'm making a query. I have a field called "Status". It's a look-up with the following values: "Not interested", "Possible Subject", "Agreement under neogtiation", "Agreement negotiated" and "Company contacted".

I would like to have the query sorted like this:

Subject
Company contacted
Agreement under neogtiation
Agreement negotiated

(now in actuality these strings are not in English but in Danish, but that doesn't really matter)

but it seems I can only sort it alphabetically and not categorically. Or what?

Thanks,
Jacob

 

[cable]

you will need to split the status in to a seperate table, with a 'SortBy' type of field to give you a numerical sort order.

Or your lookup could be multiple columns, the first being your sort order id, the other the text, and then the sort order id is what is actually stored.
Obviously this makes the data less readable without the table or form design so the first method is better.

 

[Rohdester]

Hey thanks a lot for the input. I considered doing that but I've tried something else.

I wrote a function which returns a number based on the string passed to it and then I sorted ascending by that function in the query. And it works.

I'm wondering if there's a downside to that - perhaps performancewise?

 

[cable]

probly not the quickest no

or very portable, you wont be able to convert that on to SQL Server for example

 

[The_Doc_Man]

If you are concerned about performance, the fastest way to do this is to store the preferred sort order as a field. Can't be that many values to store. AND you can store the sort order field as part of the lookup table.

Then when you sort, JOIN the raw table to its lookup, including the sort-order field, and specify your sort order on that field.

Any time your issue is a non-standard sort order based on a preferred order of seeing responses, put the sort order number as the code number that gets stored with the original table.

I.e. if you want to see veeblefetzers before you see doomaflodgers, you make the code for veeblefetzers = 1 and make the code for doomaflodgers = 2. Then you have indicators that are unique AND can also serve as a sort key. That gives you the best performance in the smallest space.