Left Join is not supported, how could I do that? (1 Viewer)

[bad-aries]

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.

 

[Banana]

One quick and easy way to do this is to do the left join all by itself, save that query, then join the query to the rest of other tables.

If you don't want to have two queries, that's fine - just go and do it, then when you have a valid query, replace the query's name with the same ... LEFT JOIN ... ON ..., embedded in ()s. This should then work.

Basic issue is that Access is quite fussy with how we join and if we don't use the ()s in the correct position, it'll refuse to process the joins.

Also, you cannot mix theta-style joins and ansi joins. Since you're using outer joins, you have to use ANSI join all the way. That's why your "simple" query didn't work.

 

[bad-aries]

Hi Banana,
I have tried to replace all inner join with left join but Access 2007 gave me the exact same result. Can you please give me a little more details on the 2 alternative solutions you suggested?

Thanks.

One quick and easy way to do this is to do the left join all by itself, save that query, then join the query to the rest of other tables.

If you don't want to have two queries, that's fine - just go and do it, then when you have a valid query, replace the query's name with the same ... LEFT JOIN ... ON ..., embedded in ()s. This should then work.

Basic issue is that Access is quite fussy with how we join and if we don't use the ()s in the correct position, it'll refuse to process the joins.

Also, you cannot mix theta-style joins and ansi joins. Since you're using outer joins, you have to use ANSI join all the way. That's why your "simple" query didn't work.

 

[Banana]

First, you'd create a new blank query. It'd be thus:

SELECT * 
FROM a LEFT JOIN b on a.id = b.id;

Of course, replacing the a/b and id with the actual names of tables & column you want to left join on. Save the query.

Then back to your original query. Remove the same two table you used to left join and replace with single query and inner join to the other tables.

 

[namliam]

Left joins, right joins and inner joins rarely mix well....

Make sure in design view it "flows" one way.
> Left join
< Right join
- join
A - B - C - D
A - B - C > D
A > B > C > D
A < B - C - D
A < B < C < D
A < B - C > D ( I think is ok too )

Bad:
A > B - C - D
A - B < C - D
These types are possible if needed but you need to store the 'bad' inner joins in a seperate query first.