[AccessD] ORDER BY in a Union Query

[Rocky Smolin]

Don:

Using the field names from the first query didn't work BUT, using the
aliases DID!  

Thanks.

Rocky
 

-----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