CODEWITHSUNDEEP
ms-access
Curtis Moxam
Curtis Moxam

Reputation: 57

Access Error: The ORDER BY expression includes fields that are not selected by the query

I keep getting the same error: But as far as I can tell it is included in the Query:

SELECT TOP 1 tblOutlets.OutletName, tblOutlets.Parish, Count([Query OOSActions].OODate) AS CountOfOODate1
FROM tblOutlets INNER JOIN [Query OOSActions] ON tblOutlets.OutletID = [Query OOSActions].OutletLookup
WHERE ((([Query OOSActions].OODate) Between [Forms]![Settings]![StartDate] And [Forms]![Settings]![EndDate]) AND (([Query OOSActions].Classification)="Biscuit"))
GROUP BY tblOutlets.OutletName, tblOutlets.Parish
HAVING (((tblOutlets.Parish)="St. Mary"))
ORDER BY Count([Query OOSActions].OODate) DESC;

UNION

SELECT TOP 1 tblOutlets.OutletName, tblOutlets.Parish, Count([Query OOSActions].OODate) AS CountOfOODate1
FROM tblOutlets INNER JOIN [Query OOSActions] ON tblOutlets.OutletID = [Query OOSActions].OutletLookup
WHERE ((([Query OOSActions].OODate) Between [Forms]![Settings]![StartDate] And [Forms]![Settings]![EndDate]) AND (([Query OOSActions].Classification)="Biscuit"))
GROUP BY tblOutlets.OutletName, tblOutlets.Parish
HAVING (((tblOutlets.Parish)="St. Catherine"))
ORDER BY Count([Query OOSActions].OODate) DESC;

Upvotes: 1

Views: 710

Answers (2)

David
David

Reputation: 59

When using "TOP" and "ORDER BY" in the second SQL statement of a UNION query, I have experienced that it runs the TOP instruction first and then the "ORDER BY", so it makes no sense to order by anything here. With a simpler SQL example:

SELECT TOP 1 Ingresos.ID_Fra, Ingresos.Tipo, Ingresos.Numero, Ingresos.Fecha, "First Query" AS Tbl 
FROM Ingresos 
ORDER BY Ingresos.Fecha DESC
UNION
SELECT TOP 5 IngresosAnulados.ID_Fra, IngresosAnulados.Tipo, IngresosAnulados.Numero, IngresosAnulados.Fecha, "DEL" AS Tbl
FROM IngresosAnulados
ORDER BY Fecha DESC;

Source table: IngresosAnulados:

Records on IngresosAnulados

Assuming that the data in "IngresosAnulados" are these 10 records, the defined SQL statement shows the following result:

enter image description here

As you can see, it has taken five records from the second table and has ordered them by the field "Fecha", as specified, but we do not know what the criteria are for having selected the five records that it has chosen.

By the way, it is necessary to indicate the name of a field selected in the first query as the order of any of the union queries (except the first one). That's just what the error message says (The ORDER BY expression includes fields that are not selected by the query).

So you will need two independent queries (at least, an independent query for the second SQL statement) to get the record(s) that interests you in each of them and then join them in a UNION query if you really want the x top most of each UNION query to be displayed.

Upvotes: 0

Curtis Moxam
Curtis Moxam

Reputation: 57

I solved it by including ORDER BY only in the first query and then removing all ending semicolons. Thanks for that bit @Uueerdo

Upvotes: 1

Related Questions