Another Exists problem 1

[ooch1]

Hi there i'm sure there is wizard some where that knows where i am going wrong with my exists subquery.

Basically, i started out with the following query, that provides a list of 55 MPRs and there associated MeterpointAQs, when i input NOvembers dates, as per below:

[COLOR=red]
SELECT [A02-GAS_YTD_Losses].MPR, Sum([A02-GAS_YTD_Losses]. [Meter point AQ]) AS [SumOfMeter point AQ]

FROM [A02-GAS_YTD_Losses] LEFT JOIN [A05-Md_Migrated_MPR_Tbl] ON [A02-GAS_YTD_Losses].MPR = [A05-Md_Migrated_MPR_Tbl].MigratedMPR

WHERE (([A05-Md_Migrated_MPR_Tbl].MigratedMPR Is Null) AND ([A02-GAS_YTD_Losses].[Ceased date] Between [Start Date] And [End Date]) AND ([A02-GAS_YTD_Losses].NA_Ctrt_STATUS Like "*tar*"))

GROUP BY [A02-GAS_YTD_Losses].MPR;
[/color]

The results of this would look like
MPR Meter point AQ
123456 1000
789123 2500
456879 4000....

Based on these results, I'm then trying to catagorise these 55 MPRs based on the MeterpointAQ being between the [LowValue] and [HighValue] in table [A12-VolCat_Tbl_2] and display the results as follows:

Vol_Band Count of MPRs Sum of MeterPointAQ
0-1000 1 1000
1001-5000 2 6500....

The code i have in place is:

[COLOR=green]
SELECT [A12-VolCat_Tbl_2].Vol_Band, Count([A02-GAS_YTD_Losses].MPR) AS CountOfMPR, Sum([A02-GAS_YTD_Losses].[Meter point AQ]) AS [SumOfMeter point AQ]

FROM [A02-GAS_YTD_Losses], [A12-VolCat_Tbl_2]

WHERE EXISTS 
[COLOR=red](SELECT [A02-GAS_YTD_Losses].MPR, Sum([A02-GAS_YTD_Losses].[Meter point AQ]) AS [SumOfMeter point AQ]
FROM [A02-GAS_YTD_Losses] LEFT JOIN [A05-Md_Migrated_MPR_Tbl] ON [A02-GAS_YTD_Losses].MPR = [A05-Md_Migrated_MPR_Tbl].MigratedMPR
WHERE (([A05-Md_Migrated_MPR_Tbl].MigratedMPR Is Null) AND  ([A02-GAS_YTD_Losses].[Ceased date] Between [Start Date] And [End Date]) 
AND ([A02-GAS_YTD_Losses].NA_Ctrt_STATUS Like "*tar*"))
GROUP BY [A02-GAS_YTD_Losses].MPR)[/color]

GROUP BY [A12-VolCat_Tbl_2].Vol_Band;
[/color]

What seams to be happening though, is that instead of just querying and grouping the 55 MPRs from the sub query it instead puts the total MPRs and the total AQ from the sub query against every category, regardless of what dates are input like so:

Vol_Band Count of MPRs Sum of MeterPointAQ
0-1000 800 15486
1001-5000 800 15486
5001-10001 800 15486

I have also tried including the WHERE criteria in the outer query, but then this throws back an answer of 64, which basically implies that it is ignoring the sub query?

I hope this makes sense to someone and they can let me know where i am going wrong??

OOch

 

[Golom]

You're not running the query in the EXISTS clause as a correlated subquery which means that, if it returns anything, then it will return it for EVERY record of the external query. That external query is in turn doing a cross join (i.e. every record of [A02-GAS_YTD_Losses] paired with every record of [A12-VolCat_Tbl_2].

You would probably get the same result of you eliminated the EXISTS clause because it always exists.

I'm not completely clear on your intent but you probably need this to be a correlated subquery. Something like

SELECT V.Vol_Band, Count(G.MPR) AS CountOfMPR, Sum(G.[Meter point AQ]) AS [SumOfMeter point AQ]

FROM [A02-GAS_YTD_Losses] As G, [A12-VolCat_Tbl_2] AS V

WHERE EXISTS 

    (SELECT *

     FROM [A02-GAS_YTD_Losses] As G1 LEFT JOIN [A05-Md_Migrated_MPR_Tbl] As M 
          ON G1.MPR = M.MigratedMPR

     WHERE 
        M.MigratedMPR Is Null 
        AND G1.[Ceased date] Between [Start Date] And [End Date] 
        AND G1.NA_Ctrt_STATUS Like "*tar*"
        [COLOR=red]AND G1.MPR = G.MPR[/color])

GROUP BY V.Vol_Band;

You don't really need the Grouping or the calculations in the EXISTS query since you really care only about "returns records" or "doesn't return records".

 

[ooch1]

Golom - I don't think i completley understand your diagnosis as to what i was doing wrong (a quick defintion of a "correlated sub query" would be extremely useful), but it has worked like a dream....many thanks!!

OOch

 

[Golom]

A correlated subquery is a query that contains another query inside it (for example, your EXISTS query) and the conditions in the subquery depend at least in part, on values from the outer or containing query. In a simplified example ...

Select M.TaskID, M.ActionDateTime, M.EmployeeName

From EmployeeTasks As M

Where M.ActionDateTime IN 

    (Select MAX(S.ActionDateTime) From Employees As S)

is an uncorrelated subquery. There is no reference to values from the outer query in the inner query. This will give you the employee with the last Action recorded in this hyopothetical example.

In contrast

Select M.TaskID, M.ActionDateTime, M.EmployeeName

From EmployeeTasks As M

Where M.ActionDateTime IN 

    (Select MAX(S.ActionDateTime) From Employees As S
    [b]WHERE S.EmployeeName = M.EmployeeName[/b])

is a correlated subquery and the subquery now returns the latest action date for the employee being processed in the outer query. This query produces the lastest action for each employee rather than just the single employee with the latest action as the first one did.

To the purists out there ...
Yes I know that duplicate values will for ActionDateTime will produce duplicate records ... but it is only an example.

 

[ooch1]

Golom - Superb, a very clear explanation, thank you very much!

OOch

 

[ooch1]

Golom - I have just encountered another slight problem with duplicates.

Basically the code below works fine, except when a MPR has 2 seperate end dates.

Becasue it exists twice, the outer query double counts, where as it would only be correctly included once in the sub query.

I have tried the select distinct method, but that will not work because it is looking at distinct rows anyway.

Is there any quick solution to this?

SELECT distinct  V.Vol_Band, G.MPR, Count(G.MPR) AS CountOfMPR, Sum(G.[Meter point AQ]) AS [SumOfMeter point AQ]
FROM [A02-GAS_YTD_Losses] AS G, [A12-VolCat_Tbl_2] AS V
WHERE (((Exists (SELECT distinct *

     FROM [A02-GAS_YTD_Losses] As G1 LEFT JOIN [A05-Md_Migrated_MPR_Tbl] As M 
          ON G1.MPR = M.MigratedMPR

     WHERE 
        M.MigratedMPR Is Null 
        AND G1.[Ceased date] Between [Start Date] And [End Date] 
        AND G1.NA_Ctrt_STATUS Like "*tar*"
        AND G1.MPR = G.MPR))<>False) AND ((G.[Meter point AQ]) Between [V].[LowCat] And [V].[HighCat]))
GROUP BY V.Vol_Band, G.MPR;
/[code]

OOch

 

[Golom]

Looking at your code ... "End Date" is unqualified so I assumed it to be a parameter (i.e. user entered) value. Is it really a field in one of the tables? If so, which table?

Further, even if there are multiple records that satisfy the condition

"G1.[Ceased date] Between [Start Date] And [End Date]"

that shouldn't make any difference from the case where there is only one because EXISTS returns TRUE if 1 or more records are returned by the query and FALSE if none are returned.

BTW - The "<>False" is unnecessary. EXISTS returns TRUE or FALSE so your test is equivalent to

TRUE <>FALSE Which is TRUE or
FALSE<>FALSE Which is FALSE.

in short, the first value is the answer. The test doesn't add anything.

 

[ooch1]

Golom - You are quite right about the end date being a parameter.

I have just run the report again and it seems to be working fine....i have no idea what was going on before, maybe it is just an access thing???

OOch