Union Query Problem 1

[rj51cxa]

The following query selects teams that are competing as Honours Only (HO) and not Honours Only (NH). I have created individual queries, both of which work, but when I combine them in a Union Query, I receive a dialog box with the following statement:

"The ORDER BY expression ([TotalFibua]+ [TotalRoberts]+[TotalRoupell]+[TotalMatch8]) includes fields that are not selected by the query. Only those fields requested in the first query can be included in the ORDER BY expression"

The only difference between the two queries is that TblUnit.HonoursOnly is selected as True or False.

The code is as follows:

SELECT TOP 2 "NH" AS Type, TblUnit.TxtUnit, [TotalFibua]+[TotalRoberts]+[TotalRoupell]+[TotalMatch8] AS Total, QryRobertsTeam.TotalRoberts, QryRoupellTeam.TotalRoupell, QryFibuaTeam.TotalFibua, QryMatch8_Team_Tot.TotalMatch8, TblBisleyYear.BisleyYear, TblUnit.Honours_Only
FROM (((TblBisleyYear INNER JOIN (TblUnit INNER JOIN QryFibuaTeam ON TblUnit.TxtUnit = QryFibuaTeam.TxtUnit) ON TblBisleyYear.BisleyYear = QryFibuaTeam.BisleyYear) INNER JOIN QryRobertsTeam ON (TblBisleyYear.BisleyYear = QryRobertsTeam.BisleyYear) AND (TblUnit.TxtUnit = QryRobertsTeam.TxtUnit)) INNER JOIN QryRoupellTeam ON (TblBisleyYear.BisleyYear = QryRoupellTeam.BisleyYear) AND (TblUnit.TxtUnit = QryRoupellTeam.TxtUnit)) INNER JOIN QryMatch8_Team_Tot ON TblUnit.TxtUnit = QryMatch8_Team_Tot.TxtUnit
WHERE (((TblUnit.Honours_Only)=False))
ORDER BY [TotalFibua]+[TotalRoberts]+[TotalRoupell]+[TotalMatch8] DESC , QryRobertsTeam.TotalRoberts DESC , QryRoupellTeam.TotalRoupell DESC , QryFibuaTeam.TotalFibua DESC , QryMatch8_Team_Tot.TotalMatch8 DESC;
UNION SELECT TOP 2 "HO" AS Type, TblUnit.TxtUnit, [TotalFibua]+[TotalRoberts]+[TotalRoupell]+[TotalMatch8] AS Total, QryRobertsTeam.TotalRoberts, QryRoupellTeam.TotalRoupell, QryFibuaTeam.TotalFibua, QryMatch8_Team_Tot.TotalMatch8, TblBisleyYear.BisleyYear, TblUnit.Honours_Only
FROM (((TblBisleyYear INNER JOIN (TblUnit INNER JOIN QryFibuaTeam ON TblUnit.TxtUnit = QryFibuaTeam.TxtUnit) ON TblBisleyYear.BisleyYear = QryFibuaTeam.BisleyYear) INNER JOIN QryRobertsTeam ON (TblBisleyYear.BisleyYear = QryRobertsTeam.BisleyYear) AND (TblUnit.TxtUnit = QryRobertsTeam.TxtUnit)) INNER JOIN QryRoupellTeam ON (TblBisleyYear.BisleyYear = QryRoupellTeam.BisleyYear) AND (TblUnit.TxtUnit = QryRoupellTeam.TxtUnit)) INNER JOIN QryMatch8_Team_Tot ON TblUnit.TxtUnit = QryMatch8_Team_Tot.TxtUnit
WHERE (((TblUnit.Honours_Only)=True))
ORDER BY [TotalFibua]+[TotalRoberts]+[TotalRoupell]+[TotalMatch8] DESC , QryRobertsTeam.TotalRoberts DESC , QryRoupellTeam.TotalRoupell DESC , QryFibuaTeam.TotalFibua DESC , QryMatch8_Team_Tot.TotalMatch8 DESC;

I would be grateful if someone could take a look and see what is causing this message.

Thanks a lot
John

 

[PHV]

ORDER BY 3 DESC, 4 DESC, 5 DESC, 6 DESC, 7 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rj51cxa]

Thanks PHV, that solved the problem.
Your help was, as ever, much appreciated.
Best Regards
John