Query error with aggregate and criteria

[pbassett]

I'm trying in a single query to exclude variances of $0 using data from 2 tables. I create a temp table from 2 queries and query that with aggregates and criteria.
<code>
SELECT x.Vendor, Sum(x.Budget) AS Budget, Sum(x.Actuals) AS Actuals, [Budget]-[Actuals] AS Variance
FROM (
SELECT Vendor, 0 AS Budget, Actuals FROM [Vendor Actuals]
UNION ALL
SELECT Vendor, Budget, 0 AS Actuals FROM [Vendor Budget]
) AS x
GROUP BY x.Vendor
HAVING ((([Budget]-[Actuals])<>0));
</code>
The HAVING statement causes this error:
<code>
You tried to execute a query that does not include the specified expression 'Not [Budget]-[Actuals])=0' as part of an aggregate function."
</code>
Using 2 queries I can get the results, where the first query doesn't have the criteria and the second query calls that query with criteria. Of course I prefer avoiding the need for 2 queries.

 

[xenou]

I guess you want a where clause not a having clause?

Such as:

SELECT x.Vendor, Sum(x.Budget) AS Budget, Sum(x.Actuals) AS Actuals, [Budget]-[Actuals] AS Variance
FROM (
SELECT Vendor, 0 AS Budget, Actuals FROM [Vendor Actuals]
UNION ALL
SELECT Vendor, Budget, 0 AS Actuals FROM [Vendor Budget]
) AS x 
WHERE ((([Budget]-[Actuals])<>0))
GROUP BY x.Vendor
;

Given the definition of your subquery X, this is also equivalent:

SELECT x.Vendor, Sum(x.Budget) AS Budget, Sum(x.Actuals) AS Actuals, [Budget]-[Actuals] AS Variance
FROM (
SELECT Vendor, 0 AS Budget, Actuals FROM [Vendor Actuals] WHERE Actuals <> 0
UNION ALL
SELECT Vendor, Budget, 0 AS Actuals FROM [Vendor Budget] WHERE Budget <> 0
) AS x 
GROUP BY x.Vendor
;