Order by query question 1

[ParaTrooper]

How would I go about ordering the data in a query based on the level of the technician? I.e. I have a field called STHST_CERTIFCODE which has the following type of technicians:
ADVTECH
APPRENTICE
MASTERTECH
SPECTECH
TECHNICIAN

I want to order them like so:
MASTERTECH
SPECTECH
ADVTECH
TECHNICIAN
APPRENTICE

Thanks in advance.

 

[sko]

I would add another field to the table.

Rank Type
1 MasterTech
2 SpecTech
etc.

Then, sort by that field in your query.

 

[KyleS]

To go along with sko's suggestion, instead of putting the values in the table make another table:

TitleID Title
1 MASTERTECH
2 SPECTECH
3 ADVTECH
4 TECHNICIAN
5 APPRENTICE

Call it tblIndex if you want, then bring tblIndex into your query and sort by you TitleID field

SELECT Table1.*
FROM table1 INNER JOIN tblIndex ON Table1.Level = tblIndex.Title
ORDER BY tblIndex.TitleID

Something like that should work... Kyle

 

[ParaTrooper]

Thanks a lot Kyle that worked great!

I didn’t want to add another column to the table because of data redundancy and the data is uploaded daily from an extract.

 

[KyleS]

happy to help!

and thanks for the recognition! Kyle