Order By Clause Issue

[josh]

MeasName MeasValue

Left Subclavian Carotid Artery M Peak Systolic Velocity 77.8
Right Subclavian Carotid Artery M Peak Systolic Velocity 139
Left Subclavian Carotid Artery P Peak Systolic Velocity 120
Right Subclavian Carotid Artery P Peak Systolic Velocity 61.3
Left Axillar Artery Peak Systolic Velocity 142
Right Axillar Artery Peak Systolic Velocity 140
Left Brachial Artery D Peak Systolic Velocity 147
Right Brachial Artery D Peak Systolic Velocity 105
Left Brachial Artery P Peak Systolic Velocity 134
Right Brachial Artery P Peak Systolic Velocity 146
Right Innominate Artery Peak Systolic Velocity 110
Left Radial Artery D Peak Systolic Velocity 134
Right Radial Artery D Peak Systolic Velocity 73.9
Left Radial Artery M Peak Systolic Velocity 62.9
Right Radial Artery M Peak Systolic Velocity 147
Left Radial Artery P Peak Systolic Velocity 119
Right Radial Artery P Peak Systolic Velocity 121
Left Ulnar Artery D Peak Systolic Velocity 127
Right Ulnar Artery D Peak Systolic Velocity 150


Left Ulnar Artery M Peak Systolic Velocity 73.1
Right Ulnar Artery M Peak Systolic Velocity 117
Left Ulnar Artery P Peak Systolic Velocity 127
Right Ulnar Artery P Peak Systolic Velocity 134

Given the data above, is there a way for me to sort this with the right measurement first, then by measurement name in the specified order - Subclavian Carotid Artery, Axillar Artery, Brachial Artery, Radial Artery, Ulnar Artery, Innominate Artery? I am able to sort so that the Right measurements go first and I am able to parse the Name and extract just the actual name of the artery like "Subclavian Carotid Artery" hoping to use that for the actual sort.

Here is the query that I have come up so far -

SELECT MeasName, MeasValue, 

Mid(MeasName,InStr(MeasName," ")+1,(InStr(MeasName,"Artery")+6)-(InStr(MeasName," ")+1)) AS String1, 

Switch(String1="Subclavian Carotid Artery",1,String1="Axillar Artery",2, String1="Brachial Artery",3, String1="Brachial 

Artery",4, String1="Radial Artery",5, String1="Ulnar Artery",6, String1="Innominate Artery",7) AS SortOrder
FROM MEASUREMENTS
ORDER BY MeasName DESC, SortOrder

When I run this query, I get the Enter Parameter Value for SortOrder and when I enter a value of 1, I get the data displayed but not in the sort order I like. Any help will be greatly appreciated. Thanks.

[June7]

"Left Subclavian Carotid Artery M Peak Systolic Velocity" is one value?

You would have to repeat the entire Switch expression in the ORDER BY clause in place of the SortOrder alias. If you don't care to see the value produced by the Switch don't include it in the SELECT clause, just ORDER BY. Are you building this in query designer or VBA?

[josh]

Thanks for the quick reply. Yes that is just one value and I am running this in query designer. I tried your suggestion and I still get the Enter Parameter Value for String1 this time. Here is my revised query -

SELECT MEASUREMENTS.MeasCode, MEASUREMENTS.MeasName, MEASUREMENTS.MeasValue, Mid(MeasName,InStr(MeasName," ")+1,(InStr(MeasName,"Artery")+6)-(InStr(MeasName," ")+1)) AS String1
FROM MEASUREMENTS
WHERE MeasCode <> "SubjectAge"
ORDER BY MEASUREMENTS.MeasName DESC, Switch(String1="Subclavian Carotid Artery",1,String1="Axillar Artery",2, String1="Brachial Artery",3, String1="BrachiArtery",4, String1="Radial Artery",5, String1="Ulnar Artery",6, String1="Innominate Artery",7)

Thanks for your help.

[June7]

My point is cannot have an alias field in the ORDER BY clause. I did not catch the String1 alias. Would have to repeat the String1 expression in place of every usage of String1 in the ORDER BY clause.

Option is to do another query that refers to the query with the constructed fields.

[josh]

I see. I will replace every occurence of the alias String1 in the Order By clause with the actual expression and find out what happens. Thanks again and will let you know what happens either later this afternoon or tomorrow for sure. thanks again for help

[josh]

That worked great for me. Thank you very much for your help. Very much appreciated.