Sub Query 'EXISTS' Function Problem

[ooch1]

Hello,

I am trying to amalgimate two queries using the EXISTS function, but i have run into a few problems.

Basically, i don't know how to reference the fields that were originally calculated in the first query.

The problems are with the following elements of the query, all of which had alias's created, but are no longer recognised:
[A].CountOfMeterPointRef) AS Meters
Sum( [A].AnnualRevenue) AS ConsumptionRevenue
Sum( [A].annualstandingCharge) AS AnnualStandingCharge
Sum( [A].TotalRevenue) AS SumOfTotalRevenue

SELECT TOP 50  [A].[Group ref],  [A].[Group Name], Count( [A].SiteRefNum) AS CountOfSiteRefNum, Sum( [A].CountOfMeterPointRef) AS Meters,  [A].[Site status], Sum(([Site AQ])/1000000) AS [TotalConsumption(GWh)], Sum( [A].AnnualRevenue) AS ConsumptionRevenue, Sum( [A].annualstandingCharge) AS AnnualStandingCharge, Sum( [A].TotalRevenue) AS SumOfTotalRevenue
FROM  [002-NBS_GAS_ROOTDATA_20040923]

WHERE EXISTS
(SELECT [B].[Site status], [B].[Group ref], [B].[Group Name], [B].SiteRefNum, Count([B].MeterPointRef) AS CountOfMeterPointRef, [B].[Site AQ], (([B]![Site AQ]*([B]![Price]))/100) AS AnnualRevenue, 365*Sum(([standingcharge])) AS annualstandingCharge, (([B]![Site AQ]*([B]![Price]))+(365*Sum([standingcharge])))/100 AS TotalRevenue
FROM [002-NBS_GAS_ROOTDATA_20040923] as [B]
GROUP BY [B].[Site status], [B].[Group ref], [B].[Group Name], [B].SiteRefNum, [B].[Site AQ], [B]![Site AQ]*[B]![Price]
HAVING ((([B].[Site status]) Not Like "*lost*" And ([B].[Site status]) Not Like "*inactive*"))
ORDER BY (([B]![Site AQ]*([B]![Price]))+(365*Sum([standingcharge])))/100 DESC)

GROUP BY  [A].[Group ref],  [A].[Group Name],  [A].[Site status]
HAVING ((( [A].[Group ref]) Not Like 0))
ORDER BY Sum( [A].TotalRevenue) DESC
;

All suggestions will be very much appreciated

OOch

 

[PHV]

Perhaps this ?
FROM [002-NBS_GAS_ROOTDATA_20040923] [highlight]As [A][/highlight]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PHV,

I did just pick up on that myself, but still no joy.

If i did this as 2 seperate queries it worlks fine, but obviously this involves the 2nd Query, looking at the first as appose to the original source table.

Does this help clarify the problem?

OOch

 

[PHV]

I see no reference to [A] in the subquery, so don't understand looking at the first as appose to the original source table

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PHV - Point taken what i did was so i could seterate the 2 query elements a little easier i referenced the same table as different alias's.
i.e.
FROM [002-NBS_GAS_ROOTDATA_20040923] as [A]
FROM [002-NBS_GAS_ROOTDATA_20040923] as

I suppose this may course problems, but i thought this wouldn't matters as the refs are within a second SELECT Statement?

OOch