[AccessD] ORDER BY in a Union Query

[Rocky Smolin]

Dear List:
 
I have a rather complicated UNION query, made up of three queries.  It
becomes the row source for a combo box and lists the CPA courses offered by
three different organization types - Firms, Societies, and Associations.
 
It's working now except for the order by.  I put the Order By clause in the
first query as you can see but it doesn't order correctly.  I put it into a
separate query for testing and it doesn't order by Course, Date,
Organization, Type, and City.  When I run it as a query it orders by the
Course ID - the first field selected.
 
Here's the query which I have broken up into three sections for easier
reading.  So how do I get the UNION of these three queries to order
correctly?
 
MTIA
 
Rocky
 
SELECT DISTINCT tblAccountingFirmCourseOfferings.fldAFCourseOfferingID,
tblCourses.fldCourseName AS Course,
tblAccountingFirmCourseOfferings.fldAFCourseOfferingDate AS [Date],
tblAccountingFirm.fldAFName AS Organization, "Firm" AS Type,
tblAccountingFirmCourseOfferings.fldAFCourseOfferingCity AS City FROM
(tblCourses RIGHT JOIN tblAccountingFirmCourseOfferings ON
tblCourses.fldCourseID = tblAccountingFirmCourseOfferings.fldCourseID) INNER
JOIN tblAccountingFirm ON
tblAccountingFirmCourseOfferings.fldAccountingFirmID =
tblAccountingFirm.fldAccountingFirmID WHERE (((tblCourses.fldCourseName) Is
Not Null) And ((tblAccountingFirm.fldAFName) Is Not Null)) ORDER BY
tblCourses.fldCourseName,
tblAccountingFirmCourseOfferings.fldAFCourseOfferingDate,
tblAccountingFirm.fldAFName 
 
UNION 
 
SELECT DISTINCT tblSocietyCourseOfferings.fldSocietyCourseOfferingID,
tblCourses.fldCourseName AS Course,
tblSocietyCourseOfferings.fldSocietyCourseOfferingDate AS [Date],
tblSociety.fldSocietyName AS Organization, "Society" AS Type,
tblSocietyCourseOfferings.fldSocietyCourseOfferingCity AS City FROM
tblSociety RIGHT JOIN (tblCourses RIGHT JOIN tblSocietyCourseOfferings ON
tblCourses.fldCourseID = tblSocietyCourseOfferings.fldCourseID) ON
tblSociety.fldSocietyID = tblSocietyCourseOfferings.fldSocietyID WHERE
(((tblCourses.fldCourseName) Is Not Null) And ((tblSociety.fldSocietyName)
Is Not Null))  
 
UNION 
 
SELECT DISTINCT
tblAssociationCourseOfferings.fldAssociationCourseOfferingID,
tblCourses.fldCourseName AS Course,
tblAssociationCourseOfferings.fldAssocCourseOfferingDate AS [Date],
tblAssociation.fldAssocName AS Organization, "Association" AS Type,
tblAssociationCourseOfferings.fldAssocCourseOfferingCity AS City FROM
(tblAssociation INNER JOIN tblAssociationCourseOfferings ON
tblAssociation.fldAssociationID =
tblAssociationCourseOfferings.fldAssociationID) INNER JOIN tblCourses ON
tblAssociationCourseOfferings.fldCourseID = tblCourses.fldCourseID WHERE
(((tblCourses.fldCourseName) Is Not Null) And ((tblAssociation.fldAssocName)
Is Not Null))