Help combining queries

[Jon]

I have an existing pair of Access queries which I need a bit of help
combining into one larger query (needed to run on a different app
where the only privilege present will be to run queries, no make-table
abilities or such).

Query 1 filters a table to specific criteria:

SELECT tblSupplierInfo.supplier_nme AS Name,
Sum(tblPurchaseOrders.total_cost) AS Total_Cost
FROM tblSupplierInfo RIGHT JOIN tblPurchaseOrders ON
tblSupplierInfo.supplier_id1 = tblPurchaseOrders.supplier_id
WHERE (((tblPurchaseOrders.status_id)<>3) AND
((Month([tblPurchaseOrders].[purchase_dte]))=[Forms]![frmUnionStation]!
[txtStartMonth]) AND ((Year([tblPurchaseOrders].
[purchase_dte]))=[Forms]![frmUnionStation]![txtStartYear]))
GROUP BY tblSupplierInfo.supplier_nme;

Query 2 generates a report based on Query 1 that gives the top 25
suppliers by dollar value and a 26th line which is the sum of all the
other suppliers tagged as "Other":

SELECT TOP 25 Query1.Name, Query1.Total_Cost
FROM Query1
ORDER BY Query1.Total_Cost DESC
UNION ALL SELECT "Other", sum(Query1.Total_Cost)
FROM Query1
WHERE Query1.Name NOT IN (
SELECT TOP 25 Query1.Name
FROM Query1
ORDER BY Query1.Total_Cost DESC);

The part before the UNION I can combine easily enough... What's giving
me fits is trying to make the part after that UNION work right. Can
anyone help out here?

 

[Jon]

Ah thank you... but I now have a new problem. I went to cut'n'paste
your answer and I'm getting a "Syntax Error in FROM clause", and the
cursor's on the second last ")" in the asterisked line below. What am
I doing wrong here??

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Replace all the "Query1" in the FROM clause with the bracketed [].
contents of Query1.  Note the period (.) to the right of the end bracket
(].) - this is necessary.  A query in the FROM clause, like this, is
called a derived table.

SELECT TOP 25 Q.Name, Q.Total_Cost
FROM [SELECT SI.supplier_nme AS Name,Sum(PO.total_cost) AS Total_Cost
       FROM tblSupplierInfo AS SI RIGHT JOIN tblPurchaseOrders ASPO ON
       SI.supplier_id1 = PO.supplier_id
       WHERE (((PO.status_id)<>3) AND
            ((Month([PO].[purchase_dte]))= ****
            [Forms]![frmUnionStation]![txtStartMonth]) AND

            ((Year([PO].[purchase_dte]))=
            [Forms]![frmUnionStation]![txtStartYear]))
       GROUP BY SI.supplier_nme]. AS Q
ORDER BY Q.Total_Cost DESC

UNION ALL SELECT "Other", sum(Q.Total_Cost)
FROM [SELECT SI.supplier_nme AS Name, Sum(PO.total_cost) AS Total_Cost
      FROM tblSupplierInfo AS SI RIGHT JOIN tblPurchaseOrders AS POON
      SI.supplier_id1 = PO.supplier_id
      WHERE (((PO.status_id)<>3) AND
            ((Month([PO].[purchase_dte]))=
            [Forms]![frmUnionStation]![txtStartMonth]) AND
            ((Year([PO].[purchase_dte]))=
            [Forms]![frmUnionStation]![txtStartYear]))
      GROUP BY SI.supplier_nme]. AS Q
WHERE Q.Name NOT IN (
   SELECT TOP 25 Q.Name
   FROM [SELECT SI.supplier_nme AS Name, Sum(PO.total_cost) AS Total_Cost
        FROM tblSupplierInfo AS SI RIGHT JOIN tblPurchaseOrders AS PO ON
        SI.supplier_id1 = PO.supplier_id
        WHERE (((PO.status_id)<>3) AND
              ((Month([PO].[purchase_dte]))=
              [Forms]![frmUnionStation]![txtStartMonth]) AND
              ((Year([PO].[purchase_dte]))=
              [Forms]![frmUnionStation]![txtStartYear]))
        GROUP BY SI.supplier_nme]. AS Q
   ORDER BY Q.Total_Cost DESC);

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSQjcc4echKqOuFEgEQKjTgCgs3OYEV5WNZ3FaqqL2DW35nnGKwUAoLrh
n6xdJXe0XqAZ8kg33FS0pPeD
=SilZ
-----END PGP SIGNATURE-----

I have an existing pair of Access queries which I need a bit of help
combining into one larger query (needed to run on a different app
where the only privilege present will be to run queries, no make-table
abilities or such).

Query 1 filters a table to specific criteria:

SELECT tblSupplierInfo.supplier_nme AS Name,
Sum(tblPurchaseOrders.total_cost) AS Total_Cost
FROM tblSupplierInfo RIGHT JOIN tblPurchaseOrders ON
tblSupplierInfo.supplier_id1 = tblPurchaseOrders.supplier_id
WHERE (((tblPurchaseOrders.status_id)<>3) AND
((Month([tblPurchaseOrders].[purchase_dte]))=[Forms]![frmUnionStation]!
[txtStartMonth]) AND ((Year([tblPurchaseOrders].
[purchase_dte]))=[Forms]![frmUnionStation]![txtStartYear]))
GROUP BY tblSupplierInfo.supplier_nme;

Query 2 generates a report based on Query 1 that gives the top 25
suppliers by dollar value and a 26th line which is the sum of all the
other suppliers tagged as "Other":

SELECT TOP 25 Query1.Name, Query1.Total_Cost
 FROM Query1
 ORDER BY Query1.Total_Cost DESC
UNION ALL SELECT "Other", sum(Query1.Total_Cost)
 FROM Query1
 WHERE Query1.Name NOT IN (
  SELECT TOP 25 Query1.Name
  FROM Query1
  ORDER BY Query1.Total_Cost DESC);

The part before the UNION I can combine easily enough... What's giving
me fits is trying to make the part after that UNION work right. Can
anyone help out here?