Setting SORT BY

[mdsoren]

I have tried to search the forum, but don't see anything like what I am trying to do...

I have my SQL statement built on the fly based on values selected from 5 combo boxes users may choose as many or as few, but at least one. No matter which elements they are searching the database for, the report always returns the same fields in the report, Company Name, Attorney's name and state company is organized in.

My problem is I want to sort the resulting recordset based on a "constructed" field -- I am not sure the technical term, it's NOT an alias, I use a SELECT inside of my main SELECT to return names from an addressbook table.

The tables being searched only have ID#'s referencing the addressbook wherever a name is involved, but I need to retrieve the name.

Here's a sample of the SQL my app generates when a user wants to see all companies in 'Iowa'...

SELECT DISTINCT 
    tblCorp.ID, tblCorp.ResponsibleAttorney,  tblOrganization.StateOorA,
  (SELECT FullName FROM tblAddressBook
  WHERE tblCorp.ID = tblAddressBook.ID) AS EntityName,
  SELECT FullName FROM tblAddressBook
  WHERE tblCorp.ResponsibleAttorney = tblAddressBook.ID) 
  AS RespAtty
FROM tblCorp, tblOrganization
WHERE tblCorp.ID = tblOrganization.CorpForeignKey AND 
    (tblOrganization.StateOorA = 'Iowa')
ORDER BY tblCorp.ResponsibleAttorney

This sorta works, except that the Responsible Attorney field in tblCorp is just a number. The attorney's companies are at least grouped together, but the report is not in alphabetical attorney order, but instead ordered by their ID number from the addressbook. As attorneys get added and deleted from the addressbook they will no longer be in alphabetical order as they are currently.

How do I get it to sort on the "constructed" field named 'RespAtty' instead of 'ResponsibleAttorney'???

 

[swampBoogie]

use

order by 4

i.e. order by the 4:th item in the select list.

 

[mdsoren]

BIG THANX SwampBoogie!!! That fixed it. Whoopie!!!