Sort Field in order not alphabetical 1

[netrusher]

I have an Audit Table tracking changes on a form. It works good! I have a query to show me original values and new values of changed fields. One of the Fields is called FieldName. There are 9 possible values that can appear in FieldNames:

1. MCN Number
2. MCN Originator
3. OMS Status
4. Process
5. SOE Code
6. SOE Date Needed
7. SOE Desc
8. SOE Worker
9. What Changed

These are the only possible values for FieldName. I can sort A-Z or Z-A but I really want a different order.
I would like to have all the SOE Codes be at the top and the SOE Desc etc. etc. Is there anyway to have the Values appear in different sort orders? Thanks for your help.

 

[Trevil]

You could create a table with the values listed in "FieldName", then add aanother column named "SortSeq" with the desired sort order. In your query, join the new table and sort on the "SortSeq" field.

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[lespaul]

or you could do something like:

ORDER BY iif(InStr("SOE", FieldName) > 0, 1, 0), Fieldname

this will then sort first by SOE vs no SOE and then alphabetically.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Trevil]

Good point lespaul

but you would need
ORDER BY iif(InStr("SOE", FieldName) > 0, 0, 1), Fieldname
insted of
ORDER BY iif(InStr("SOE", FieldName) > 0, 1, 0), Fieldname

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[lespaul]

oooppppss, you're right, I got the 0 and 1 backwards...good catch.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[netrusher]

Thanks Trevil