minus doesn't work !!!

[NS]

Hi there,
I've got a query (below) that is suppose to return me a number. The first
select statemetn returns me a number and also the second one. I just want to
get the first minus the second one and this sql doesn't seem to work. Access
returns me an error saying : Syntax error in From clause. But it's fine.
They all run individually.

Can you see any problems?
-------------------------------------------------
select sum(sale.[Ord total amt]) as Expr1
from sale
minus
SELECT Sum(cashrec.amount) AS Expr1
FROM cashrec, crsale
WHERE (((cashrec.[Remittance advice #])=[crsale].[remittance advice #]));

 

[JohnFol]

It's not supposed to work like that. Think of it logically as subtracting
the result of a SQL select from another. The result is not an integer, but a
recordset, comprising metadata, fields collections etc.

Also, your select statements could return more that 1 row. So how would the
minus work?

Can you expand out slightly to explain if the first select is 1 row only, or
a row per record in cashrec / crsale ?

 

[MGFoster]

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

You might be able to do it like this:

SELECT SUM(Total) As Difference
FROM (
SELECT SUM([Ord total amt]) As Total
FROM sale

UNION ALL

SELECT -Sum(R.amount)
FROM cashrec As R INNER JOIN crsale As S
ON R.[Remittance advice #]=S.[remittance advice #]
) AS A

Note the minus sign before the SUM( in the 2nd SELECT statement.

The query uses a derived table in the parentheses of the main query's
FROM clause (SQL-92 syntax). If that doesn't work because Access
doesn't recognize the SQL-92 syntax, do this: Put the UNION query in a
separate QueryDef named SumQuery.

SumQuery:
SELECT SUM([Ord total amt]) As Total
FROM sale

UNION ALL

SELECT -Sum(R.amount)
FROM cashrec As R INNER JOIN crsale As S
ON R.[Remittance advice #]=S.[remittance advice #]

Then create a 2nd query that uses the SumQuery as a data source:

DiffQuery:
SELECT SUM(Total) As Difference
FROM SumQuery

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQmUajIechKqOuFEgEQI1gQCg/PkWsvwJtJ11F/h3xfVdfHNeE4MAn03H
lNO/dPrgLZ6ZnrUxt8d4ZU5B
=Uo6x
-----END PGP SIGNATURE-----