How do I add a subquery and field in an aggregate query

[vanleurth]

I'm trying to determine the last month's electricity reading so I can
substract it from this current month value for electricity reading.
I'm using a subquery to determine last months value.
The problem is that I have to substract those values [a].
[RESIDUAL_CONSUMPTION] minus [CnspYearAgo] in a aggregation function.

I get the following message;
"You tried to execute a query that does not include the specified expression
[a].[RESIDUAL_CONSUMPTION] as part of an aggregate."

Has anyone tried to add a subquery result and a field value in an aggregate
query?

Please let me know how or send me a link to a reference.

Thank you,

V.

Here is my query;

SELECT [tbl buildings].[Official Building Name], a.BUILDING, a.BILL_PERIOD, a.
ACCOUNT_TYPE, Sum(a.RESIDUAL_CONSUMPTION) AS SumOfRESIDUAL_CONSUMPTION,
(SELECT sum( b.[RESIDUAL_CONSUMPTION]) FROM [tbl readings] as b WHERE (b.
BILL_PERIOD=DateAdd("yyyy",-1,a.BILL_PERIOD) AND b.BUILDING=a.BUILDING AND b.
ACCOUNT_TYPE = a.ACCOUNT_TYPE) ) AS CnspYearAgo, [a].[RESIDUAL_CONSUMPTION]-
[CnspYearAgo] AS Delta
FROM [tbl readings] AS a LEFT JOIN [tbl buildings] ON a.BUILDING = [tbl
buildings].[Official Building Number]
GROUP BY [tbl buildings].[Official Building Name], a.BUILDING, a.BILL_PERIOD,
a.ACCOUNT_TYPE, a.METER_SITE_TYPE, a.BILL_CODE
HAVING (((a.BILL_PERIOD)=#8/1/2008#) AND ((a.ACCOUNT_TYPE)="ELEC COGEN GEN
FUND") AND ((a.METER_SITE_TYPE)="CONSUMP") AND ((a.BILL_CODE)="J"));

 

[Ken Snell MVP]

There are a couple of different ways to do this.

One is to use the First function for the subquery:

SELECT [tbl buildings].[Official Building Name], a.BUILDING, a.BILL_PERIOD,
a.
ACCOUNT_TYPE, Sum(a.RESIDUAL_CONSUMPTION) AS SumOfRESIDUAL_CONSUMPTION,
First((SELECT sum( b.[RESIDUAL_CONSUMPTION]) FROM [tbl readings] as b WHERE
(b.
BILL_PERIOD=DateAdd("yyyy",-1,a.BILL_PERIOD) AND b.BUILDING=a.BUILDING AND
b.
ACCOUNT_TYPE = a.ACCOUNT_TYPE) ) AS CnspYearAgo, [a].[RESIDUAL_CONSUMPTION]-
[CnspYearAgo]) AS Delta
FROM [tbl readings] AS a LEFT JOIN [tbl buildings] ON a.BUILDING = [tbl
buildings].[Official Building Number]
GROUP BY [tbl buildings].[Official Building Name], a.BUILDING,
a.BILL_PERIOD,
a.ACCOUNT_TYPE, a.METER_SITE_TYPE, a.BILL_CODE
HAVING (((a.BILL_PERIOD)=#8/1/2008#) AND ((a.ACCOUNT_TYPE)="ELEC COGEN GEN
FUND") AND ((a.METER_SITE_TYPE)="CONSUMP") AND ((a.BILL_CODE)="J"));


The other way is to include the subquery in the GROUP BY clause:

SELECT [tbl buildings].[Official Building Name], a.BUILDING, a.BILL_PERIOD,
a.
ACCOUNT_TYPE, Sum(a.RESIDUAL_CONSUMPTION) AS SumOfRESIDUAL_CONSUMPTION,
(SELECT sum( b.[RESIDUAL_CONSUMPTION]) FROM [tbl readings] as b WHERE (b.
BILL_PERIOD=DateAdd("yyyy",-1,a.BILL_PERIOD) AND b.BUILDING=a.BUILDING AND
b.
ACCOUNT_TYPE = a.ACCOUNT_TYPE) ) AS CnspYearAgo, [a].[RESIDUAL_CONSUMPTION]-
[CnspYearAgo] AS Delta
FROM [tbl readings] AS a LEFT JOIN [tbl buildings] ON a.BUILDING = [tbl
buildings].[Official Building Number]
GROUP BY [tbl buildings].[Official Building Name], a.BUILDING,
a.BILL_PERIOD,
a.ACCOUNT_TYPE, a.METER_SITE_TYPE, a.BILL_CODE,
(SELECT sum( b.[RESIDUAL_CONSUMPTION]) FROM [tbl readings] as b WHERE (b.
BILL_PERIOD=DateAdd("yyyy",-1,a.BILL_PERIOD) AND b.BUILDING=a.BUILDING AND
b.
ACCOUNT_TYPE = a.ACCOUNT_TYPE) ) AS CnspYearAgo, [a].[RESIDUAL_CONSUMPTION]-
[CnspYearAgo]
HAVING (((a.BILL_PERIOD)=#8/1/2008#) AND ((a.ACCOUNT_TYPE)="ELEC COGEN GEN
FUND") AND ((a.METER_SITE_TYPE)="CONSUMP") AND ((a.BILL_CODE)="J"));


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'm trying to determine the last month's electricity reading so I can
substract it from this current month value for electricity reading.
I'm using a subquery to determine last months value.
The problem is that I have to substract those values [a].
[RESIDUAL_CONSUMPTION] minus [CnspYearAgo] in a aggregation function.

I get the following message;
"You tried to execute a query that does not include the specified
expression
[a].[RESIDUAL_CONSUMPTION] as part of an aggregate."

Has anyone tried to add a subquery result and a field value in an
aggregate
query?

Please let me know how or send me a link to a reference.

Thank you,

V.

Here is my query;

SELECT [tbl buildings].[Official Building Name], a.BUILDING,
a.BILL_PERIOD, a.
ACCOUNT_TYPE, Sum(a.RESIDUAL_CONSUMPTION) AS SumOfRESIDUAL_CONSUMPTION,
(SELECT sum( b.[RESIDUAL_CONSUMPTION]) FROM [tbl readings] as b WHERE (b.
BILL_PERIOD=DateAdd("yyyy",-1,a.BILL_PERIOD) AND b.BUILDING=a.BUILDING AND
b.
ACCOUNT_TYPE = a.ACCOUNT_TYPE) ) AS CnspYearAgo,
[a].[RESIDUAL_CONSUMPTION]-
[CnspYearAgo] AS Delta
FROM [tbl readings] AS a LEFT JOIN [tbl buildings] ON a.BUILDING = [tbl
buildings].[Official Building Number]
GROUP BY [tbl buildings].[Official Building Name], a.BUILDING,
a.BILL_PERIOD,
a.ACCOUNT_TYPE, a.METER_SITE_TYPE, a.BILL_CODE
HAVING (((a.BILL_PERIOD)=#8/1/2008#) AND ((a.ACCOUNT_TYPE)="ELEC COGEN GEN
FUND") AND ((a.METER_SITE_TYPE)="CONSUMP") AND ((a.BILL_CODE)="J"));