Subquery with ORDER BY

[CaptainBob007]

Hi all -

I'm having a little trouble with a subquery and I'm not sure why. Here's what I'm running:

SELECT DISTINCT tblPermit.PerID, 
tblProperty.AddNum & " " & tblProperty.AddStreet AS Address,
tblPermit.Description, 
tblPermit.PerNum,
tblProperty.AddStreet,
tblProperty.AddNum
FROM tblProperty INNER JOIN (tblPermit LEFT JOIN tblInsp ON tblPermit.PerID=tblInsp.PerID)
ON tblProperty.PropID=tblPermit.PropID
WHERE NOT tblPermit.PerID IN (SELECT PerID FROM tblInsp WHERE ((InsStatus = "Fail") OR (InsStatus IS NULL)))
ORDER BY tblProperty.AddStreet, tblProperty.AddNum;

It comes up with all the results I want, but it doesn't order them. Any ideas as to why would be most appreciated. Thank you in advance.

~Bob

 

[lespaul]

how is it sorting the records and how do you actually want them sorted? Your current order by sorts by the street name and then the house number. Is AddNum a number field or is it text?

Leslie

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

 

[CaptainBob007]

Right now it's just sorting them by primary key, which is probably only because my sample data happens to be in that order anyway. AddNum is a number field. I have plenty of other queries which sort by street name and then address number, so I dont know why this might be any different..

 

[PHV]

Are you sure the DISTINCT predicate is mandatory ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886