Hi all,
I have a working inner join query, but right now it is need to modify them as left outer join. However, Access 2007 complains it is not supported.
Here is my Query:
SELECT EName, Sum(tPOItems.Charge*tDelivered.DQuantity) AS Amount
FROM tClient LEFT JOIN (tPOin INNER JOIN (tPOItems INNER JOIN tDelivered ON tPOItems.ID=tDelivered.POItemsID) ON tPOin.ID=tPOItems.POinID) ON tClient.ID = tPOin.ClientID
WHERE InvoiceID Is Not Null And CalcMonth=Forms!fGenMonthlyStatement.bCalcMonth And CalcYear=Forms!fGenMonthlyStatement.bCalcYear and tClient.Active = true
GROUP BY tClient.EName
This doesn't work because it is documented Left cannot be nested outside inner join. OK, then I do it another way around like this:
SELECT EName, Sum(tPOItems.Charge*tDelivered.DQuantity) AS Amount
FROM tDelivered inner join (tPOItems inner join (tPOin Right join tClient on tPOin.ClientID = tClient.ID) on tPOItems.POinID=tPOin.ID) on tDelivered.POItemsID = tPOItems.ID
WHERE InvoiceID Is Not Null And CalcMonth=Forms!fGenMonthlyStatement.bCalcMonth And CalcYear=Forms!fGenMonthlyStatement.bCalcYear and tClient.Active = true
GROUP BY EName
This time the Right join is in the deepest of the nest but access still complain it is not supported. Then, I tried to make the join simplier like this:
SELECT EName, Sum(tPOItems.Charge*tDelivered.DQuantity) AS Amount
FROM tClient left join tPOin.ClientID = tClient.ID , tDelivered, tPOItems
WHERE (InvoiceID Is Not Null And CalcMonth=Forms!fGenMonthlyStatement.bCalcMonth And CalcYear=Forms!fGenMonthlyStatement.bCalcYear and tClient.Active = true and tDelivered.POItemsID = tPOItems.ID and tPOItems.POinID=tPOin.ID and tPOin.ClientID = tClient.ID)
GROUP BY EName
Same result. Access 2007 is complaining this join is not supported.
What else can I do to make the query to return everything from tClient even if it is null in tPOin?
Thanks.
I have a working inner join query, but right now it is need to modify them as left outer join. However, Access 2007 complains it is not supported.
Here is my Query:
SELECT EName, Sum(tPOItems.Charge*tDelivered.DQuantity) AS Amount
FROM tClient LEFT JOIN (tPOin INNER JOIN (tPOItems INNER JOIN tDelivered ON tPOItems.ID=tDelivered.POItemsID) ON tPOin.ID=tPOItems.POinID) ON tClient.ID = tPOin.ClientID
WHERE InvoiceID Is Not Null And CalcMonth=Forms!fGenMonthlyStatement.bCalcMonth And CalcYear=Forms!fGenMonthlyStatement.bCalcYear and tClient.Active = true
GROUP BY tClient.EName
This doesn't work because it is documented Left cannot be nested outside inner join. OK, then I do it another way around like this:
SELECT EName, Sum(tPOItems.Charge*tDelivered.DQuantity) AS Amount
FROM tDelivered inner join (tPOItems inner join (tPOin Right join tClient on tPOin.ClientID = tClient.ID) on tPOItems.POinID=tPOin.ID) on tDelivered.POItemsID = tPOItems.ID
WHERE InvoiceID Is Not Null And CalcMonth=Forms!fGenMonthlyStatement.bCalcMonth And CalcYear=Forms!fGenMonthlyStatement.bCalcYear and tClient.Active = true
GROUP BY EName
This time the Right join is in the deepest of the nest but access still complain it is not supported. Then, I tried to make the join simplier like this:
SELECT EName, Sum(tPOItems.Charge*tDelivered.DQuantity) AS Amount
FROM tClient left join tPOin.ClientID = tClient.ID , tDelivered, tPOItems
WHERE (InvoiceID Is Not Null And CalcMonth=Forms!fGenMonthlyStatement.bCalcMonth And CalcYear=Forms!fGenMonthlyStatement.bCalcYear and tClient.Active = true and tDelivered.POItemsID = tPOItems.ID and tPOItems.POinID=tPOin.ID and tPOin.ClientID = tClient.ID)
GROUP BY EName
Same result. Access 2007 is complaining this join is not supported.
What else can I do to make the query to return everything from tClient even if it is null in tPOin?
Thanks.