I have the following query set up in Access:
SELECT DISTINCT preparaciones.FECHA, muelles.DESTINO, muelles.ORDEN_TMP
FROM (preparaciones INNER JOIN detalle_prep ON preparaciones.N_PREP = detalle_prep.N_PREP) INNER JOIN muelles ON detalle_prep.DESTINO = muelles.M_ADAIA
GROUP BY preparaciones.FECHA, muelles.DESTINO, muelles.ORDEN_TMP, detalle_prep.ACCIONES, detalle_prep.PREPARADA
HAVING (((detalle_prep.ACCIONES)=True) AND ((detalle_prep.PREPARADA)=False))
ORDER BY preparaciones.FECHA, muelles.ORDEN_TMP;
This is returning me the following:
But I need all the records whose ORDER_TMP is equal to 99 to pass at the end of the query, respecting the date. That is, the first record dated 11/23/2021 - VALLADOLID - 99 should be above the CADIZ record, leaving the last three records as follows:
11/23/2021 - VALLADOLID - 99
12/17/2021 - CADIZ - 99
12/17/2021 - VALLADOLID - 99
How could I do this?
Thanks.
To achieve this we use the union set operator . First we make a query like the one you have done but in the where clause we put:
to get the records whose field docks.ORDEN_TMP is different from 99 .
The second query is also the same as the one you have made but this time in the where clause we put:
to get the records whose field docks.ORDEN_TMP is equal to 99 .
So using the union operator we concatenate both results of each query.
For the union operator to work, both queries must return the same number of fields and be of the same type in their corresponding order.