[AccessD] ORDER BY in a Union Query

[McGillivray, Don]

Doh.  Only just noticed that you already aliased your columns.  Just refer to the aliases in the Order By clause and you should be good to go.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
Sent: Wednesday, March 02, 2016 9:14 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] ORDER BY in a Union Query

Hi Rocky,

The Order By clause has to come AFTER all of the unions, and may refer to the sort column(s) by name (as defined in the initial query of the union) or number.  So you should remove the Order By clause from the initial query, and add it to the end of the third one.  If you alias the columns in the initial query, your Order By clause can refer to the aliases, or you can just use the column names (not qualified by the table names) of the initial query.  You can also refer to them by an ordinal (1, 2, 3, etc., where the number equals the number of the column(s) to be ordered.)

Hoping this helps.

Don

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Wednesday, March 02, 2016 8:55 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] ORDER BY in a Union Query

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))
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com