Subquery works alone, but not with query.

[Bee]

Hi,

I have written a subquery that works fine alone, but it returns -1 when with query.

The subquery is supposed to return a total of type currency.

Any help/suggestions will be very much appreciated.

SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Customer_No
GROUP BY tblCustomer.Customer_No;) AS Expr1
FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No
WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Customer_No
GROUP BY tblCustomer.Customer_No;))=True));

 

[FoFa]

Change : Exists (SELECT Sum(tblVariation_Order.VO_Price)
To: (SELECT Sum(tblVariation_Order.VO_Price) as MyCol

You may need to add some criteria to that subselect to tie it to the main query.
The Exist function returns true or false, hence you -1

 

[Bee]

it only works if I include ANY or ALL! If I remove them, it says: "A subquery of this kind cannot return more than one record. Revise the SELECT statement of the subquery to request only one record." I tried many combinations, but I must be doing something wrong.

SELECT tblHouse.House_No, tblHouse.House_Address, tblHouse.Town, tblVariation_Order.VO_Price = ALL

(SELECT sum(tblVariation_Order.VO_Price)  FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Customer_No GROUP BY tblCustomer.Customer_No ;) AS Expr1

FROM tblSite INNER JOIN (tblPhase INNER JOIN ((tblCustomer INNER JOIN tblHouse ON tblCustomer.Customer_No = tblHouse.Customer_No) INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No;

 

[FoFa]

Try THis first, see where it leads:

SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Sum(tblVariation_Order.VO_Price)
FROM tblCustomer 
INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Customer_No
INNER JOIN tblSite ON tblCustomer.Customer_No = tblHouse.Customer_No
INNER JOIN tblPhase ON tblSite.Site_No = tblPhase.Site_No
INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No
GROUP BY tblCustomer.Customer_No
having Sum(tblVariation_Order.VO_Price) <> 0;

 

[Bee]

Try THis first, see where it leads:

SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Sum(tblVariation_Order.VO_Price)
FROM tblCustomer 
INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Customer_No
INNER JOIN tblSite ON tblCustomer.Customer_No = tblHouse.Customer_No
INNER JOIN tblPhase ON tblSite.Site_No = tblPhase.Site_No
INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No
GROUP BY tblCustomer.Customer_No
having Sum(tblVariation_Order.VO_Price) <> 0;

I will try that. Thank you.

 

[Bee]

Hi Fofa,

I managed to fix it.

SELECT tblHouse.House_No, Sum(tblVariation_Order.VO_Price) AS SumOfVO_Price
FROM tblSite 
INNER JOIN (tblPhase 
INNER JOIN ((tblCustomer 
INNER JOIN tblHouse ON tblCustomer.Customer_No = tblHouse.Customer_No) INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No

GROUP BY tblHouse.House_No;

When I added a calculated field to the query, the sum function did not work properly!

Thanks for your help.
B