Union Select Top Doesn't Work

[SHIPP]

I am building a union select query on the fly. I am using Access 2003. When I
am selecting just one UPC it works fine and gives me the Top quantity that is
input to a form. However when I add on a union for multiple UPCs it no longer
picks the top quantity that was input. Apparantly it doesn't sort correctly.
My query is as follows:

SELECT TOP 5 1 AS Pallets, 5 AS
Required,ingres_wms_contdtl_tbl.so_prod_key,ingres_wms_bindtlst_tbl.wms_contdtl_puldt,
ingres_wms_bindtlst_tbl.wms_bin_key, ingres_wms_bindtlst_tbl.in_item_key AS
Item, ingres_wms_bindtlst_tbl.im_pack_key AS Pack,
ingres_wms_contdtl_tbl.in_desc AS Desc1,
ingres_wms_bindtlst_tbl.wms_contdtl_qty,
ingres_wms_contdtl_tbl.wms_contdtl_uom AS UOM,
ingres_wms_bindtlst_tbl.wms_conthdr_key, ingres_wms_bindtlst_tbl.in_lot_key
AS Lot, ingres_wms_bindtlst_tbl.wms_contst_key,
ingres_wms_bindtlst_tbl.wms_conthdr_lstmv FROM (ingres_wms_bindtlst_tbl INNER
JOIN ingres_wms_contdtl_tbl ON (ingres_wms_bindtlst_tbl.gl_cmp_key =
ingres_wms_contdtl_tbl.gl_cmp_key) AND (ingres_wms_bindtlst_tbl.in_whs_key =
ingres_wms_contdtl_tbl.in_whs_key) AND
(ingres_wms_bindtlst_tbl.wms_conthdr_key =
ingres_wms_contdtl_tbl.wms_conthdr_key) AND
(ingres_wms_bindtlst_tbl.wms_contdtl_key =
ingres_wms_contdtl_tbl.wms_contdtl_key)) INNER JOIN ingres_wms_bin_tbl ON
(ingres_wms_contdtl_tbl.gl_cmp_key = ingres_wms_bin_tbl.gl_cmp_key) AND
(ingres_wms_contdtl_tbl.in_whs_key = ingres_wms_bin_tbl.in_whs_key) AND
(ingres_wms_bindtlst_tbl.wms_bin_key = ingres_wms_bin_tbl.wms_bin_key) WHERE
(((ingres_wms_contdtl_tbl.so_prod_key) = "0002113008165") And
((ingres_wms_bindtlst_tbl.wms_bin_key) Like "U*") And
((ingres_wms_bindtlst_tbl.in_whs_key) = "8485") And
((ingres_wms_bindtlst_tbl.gl_cmp_key) = "US") And
((ingres_wms_bin_tbl.wms_bin_type) = "HIDDEN") And
((ingres_wms_bin_tbl.wms_bin_width) = 40.5) And
((ingres_wms_bin_tbl.wms_bin_hold) <> 1)) ORDER BY
ingres_wms_contdtl_tbl.so_prod_key,
ingres_wms_bindtlst_tbl.wms_contdtl_puldt,
ingres_wms_bindtlst_tbl.wms_bin_key

UNION

SELECT TOP 6 1 AS Pallets, 6 AS
Required,ingres_wms_contdtl_tbl.so_prod_key,ingres_wms_bindtlst_tbl.wms_contdtl_puldt,
ingres_wms_bindtlst_tbl.wms_bin_key, ingres_wms_bindtlst_tbl.in_item_key AS
Item, ingres_wms_bindtlst_tbl.im_pack_key AS Pack,
ingres_wms_contdtl_tbl.in_desc AS Desc1,
ingres_wms_bindtlst_tbl.wms_contdtl_qty,
ingres_wms_contdtl_tbl.wms_contdtl_uom AS UOM,
ingres_wms_bindtlst_tbl.wms_conthdr_key, ingres_wms_bindtlst_tbl.in_lot_key
AS Lot, ingres_wms_bindtlst_tbl.wms_contst_key,
ingres_wms_bindtlst_tbl.wms_conthdr_lstmv FROM (ingres_wms_bindtlst_tbl INNER
JOIN ingres_wms_contdtl_tbl ON (ingres_wms_bindtlst_tbl.gl_cmp_key =
ingres_wms_contdtl_tbl.gl_cmp_key) AND (ingres_wms_bindtlst_tbl.in_whs_key =
ingres_wms_contdtl_tbl.in_whs_key) AND
(ingres_wms_bindtlst_tbl.wms_conthdr_key =
ingres_wms_contdtl_tbl.wms_conthdr_key) AND
(ingres_wms_bindtlst_tbl.wms_contdtl_key =
ingres_wms_contdtl_tbl.wms_contdtl_key)) INNER JOIN ingres_wms_bin_tbl ON
(ingres_wms_contdtl_tbl.gl_cmp_key = ingres_wms_bin_tbl.gl_cmp_key) AND
(ingres_wms_contdtl_tbl.in_whs_key = ingres_wms_bin_tbl.in_whs_key) AND
(ingres_wms_bindtlst_tbl.wms_bin_key = ingres_wms_bin_tbl.wms_bin_key) WHERE
(((ingres_wms_contdtl_tbl.so_prod_key) = "0002113008166") And
((ingres_wms_bindtlst_tbl.wms_bin_key) Like "U*") And
((ingres_wms_bindtlst_tbl.in_whs_key) = "8485") And
((ingres_wms_bindtlst_tbl.gl_cmp_key) = "US") And
((ingres_wms_bin_tbl.wms_bin_type) = "HIDDEN") And
((ingres_wms_bin_tbl.wms_bin_width) = 40.5) And
((ingres_wms_bin_tbl.wms_bin_hold) <> 1))
ORDER BY ingres_wms_contdtl_tbl.so_prod_key,
ingres_wms_bindtlst_tbl.wms_contdtl_puldt,
ingres_wms_bindtlst_tbl.wms_bin_key;


Although the query runs without error, the order by statements do not work.
Any help would be appreciated.

 

[KARL DEWEY]

Use a follow-on query to sort and select the TOP number.

 

[John Spencer]

The UNION will ignore the ORDER BY statements in all but the last query.

WHAT you can try is to build two separate saved queries and then union the
results of the separate saved queries

SELECT *
FROM TheTop5Query
UNION
SELECT *
FROM TheTop6Query

This means that you may need to edit the SQL of two saved queries and then use
that to get your results.


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