How do I sort this union query?

[SF]

Hi,

I have the following union query. It work fine exept that the result set
does not sort (on second column).


SELECT tblTargetCommunes.TC_ContactID, tblOrganization.Org_Abbreviation
FROM tblOrganization INNER JOIN tblTargetCommunes ON
tblOrganization.Org_ID=tblTargetCommunes.TC_ContactID
WHERE (((tblTargetCommunes.TC_PactUnitID)=9))
GROUP BY tblTargetCommunes.TC_ContactID, tblOrganization.Org_Abbreviation
ORDER BY tblOrganization.Org_Abbreviation;
UNION
SELECT -1, " All " AS Org_Abbreviation FROM tblOrganization

Could someone advice?

SF

 

[John W. Vinson]

Hi,

I have the following union query. It work fine exept that the result set
does not sort (on second column).


SELECT tblTargetCommunes.TC_ContactID, tblOrganization.Org_Abbreviation
FROM tblOrganization INNER JOIN tblTargetCommunes ON
tblOrganization.Org_ID=tblTargetCommunes.TC_ContactID
WHERE (((tblTargetCommunes.TC_PactUnitID)=9))
GROUP BY tblTargetCommunes.TC_ContactID, tblOrganization.Org_Abbreviation
ORDER BY tblOrganization.Org_Abbreviation;
UNION
SELECT -1, " All " AS Org_Abbreviation FROM tblOrganization

Could someone advice?

SF

The Order By needs to be on the final SELECT clause. You can order by the
fields by their field position rather than by name (needed since "All" doesn't
have a name):

SELECT tblTargetCommunes.TC_ContactID, tblOrganization.Org_Abbreviation
FROM tblOrganization INNER JOIN tblTargetCommunes ON
tblOrganization.Org_ID=tblTargetCommunes.TC_ContactID
WHERE (((tblTargetCommunes.TC_PactUnitID)=9))
GROUP BY tblTargetCommunes.TC_ContactID, tblOrganization.Org_Abbreviation
UNION
SELECT -1, " All " AS Org_Abbreviation FROM tblOrganization
ORDER BY 2;

 

[John Spencer]

Yoou can order by Org_abbreviation. THe sort clause gets the field names from
the first query in the Union. Normally those names do not include the table
name - rare exception being if first query had two tables with identically
named fields.

SELECT Table1.FieldA, Table2.FieldA
FROM Table1 INNER JOIN Table2
ON Table1.PrimaryKey = Table2.PrimaryKey
UNION
SELECT Table3.FieldA, Table3.FieldB
FROM Table3
ORDER BY

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County