1 inner join and 1 outer join and one table not joined in one query?

[ahvc]

Hello Access friends,
Can you please help me with a query which I have tried to make work for more than a month, but in vain.

Query 1: It works, but logic is wrong.
SELECT ...
FROM Profile, (tblClient INNER JOIN tblAuthorization ON tblClient.Member_ID = tblAuthorization.Member_ID) INNER JOIN monthly_log ON tblClient.Member_ID = monthly_log.Member_ID
WHERE ...
HAVING ...


Query 2: It fails, but logic is right.
SELECT ...
FROM Profile, tblClient INNER JOIN (tblAuthorization LEFT JOIN monthly_log ON tblAuthorization.Member_ID = monthly_log.Member_ID) ON tblClient.Member_ID = tblAuthorization.Member_ID
WHERE ...
HAVING ...

When query 2 is run, the following error is giving.

Run-time error '3258':
The SQL Statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement.

There is no key id to join in profile table, but I need that table in the query. IT has only one row, so no cartesian product is possible. I need tblclient inner join on tblauthorization, and tblauthorization to left outer on monthly_log. I need the profile table also. Can you please help.

THank you very much.
AHVC

 

[Jon K]

Try these two queries.

Query1:-
SELECT ...
FROM (tblAuthorization LEFT JOIN monthly_log ON tblAuthorization.Member_ID = monthly_log.Member_ID) LEFT JOIN tblClient ON tblAuthorization.Member_ID = tblClient.Member_ID
WHERE ...;

Query2:-
SELECT *
FROM Query1, Profile;

Run Query2.


Notes:
I believe you need to set two Left Joins in order to show all the records in tblAuthorization.

HAVING is used only after the Group By clause in a Totals query.

 

[ahvc]

2 queries worked

Jon
Thanks very much for the idea.. I have never used it like this before(using one query in another query).. THanks for showing it to me..

you saved lot of time for me..

Good day.
Regards
AHVC