Order By [field] ="blah" (put those first)?

[Banaticus]

I have a list of people, all with types:
Qual <-Lowest
Pre A
Pre B
Pre C <-Highest

"Pre *" is the highest, Qual is the lowest ranking. I'd like a SQL statement to to Order By: Reverse alphabetical order if it starts with a P, then alphabetical order for the rest. Something like this:
ORDER BY table.field1 Like "P*" DESC, table.field1 ASC

 

[RV]

Why not simply using a ranking order by assigning ranking numbers to your rankings?

RV

 

[Banaticus]

In the main table [App Info], the field [App Type] is a lookup to the table [App Types], which consists of a column of possible types. I added a second column to table [App Types] which I named QAR. In QAR, I added 11 rankings for 11 of the types.

I now use the following SQL to pull up what I want, ranking them by:
Type then within type by the Date they passed the test, then within that by their last Name with all - ' " and spaces removed from the name (only when sorting, though).

SELECT [App Info].[Soc Sec #], [Last Name] & ', ' & [First Name] & ' ' & [MA] AS Name
FROM (([App Info] LEFT JOIN [App Types] ON [App Types].[Type of Apprentice]=[App Info].[App Type]) INNER JOIN [Desired Work] ON [App Info].[Soc Sec #]=[Desired Work].[Soc Sec]) INNER JOIN [Class Info] ON [App Info].[Soc Sec #]=[Class Info].[Soc Sec]
WHERE ([App Info].[App Type] aLike 'Pre%' OR [App Info].[App Type] aLike 'Qual%') AND [Class Info].[Class Name]='Qualified Applicant Exam'
ORDER BY [App Types].QAR, [Class Info].Date, CleanName([App Info].[Last Name]);