Help combining queries

  • Thread starter Thread starter Jon
  • Start date Start date
J

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?
 
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).
Click to expand...
Query 1 filters a table to specific criteria:
Click to expand...
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;
Click to expand...
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":
Click to expand...
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);
Click to expand...
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?
Click to expand...
Click to expand...
 
Back
Top