re: Can get a Left Join to Work in one summary table but not another in Access

[hwsilver]

I have pulled Clients, Volunteers and Meetings into tables into Access. I need to make a summary page for each Client and Volunteer. If they are matched then the summary should include the name of the client or volunteer with whom they were matched and the first and last times they met. To do this I made a Left Join between the volunteer table and the meeting table and then a rightjoin between the client table and the meeting table.

I am able to create a query with a rightjoin to create the report I want for volunteers.

I have unique Client IDs and Volunteer IDs and there are not lines without unique IDs in any of the tables.
When I try to make a similar query with a LeftJoin for the clients I'm told that I can't do it, because the report can't be based on an "ambiguous outer join", so I can't make one summary table that includes all clients.

[Banfa]

I think you are starting with the wrong table, This is about what volunteers and clients have had meetings, you only include the client and volunteer tables because you would prefer to see names not ids so you could start with something like.

SELECT meeting.VolunteerId, meeting.ClientId, MIN(meeting.datetime), MAX(meeting.datetime) FROM meeting GROUP BY meeting.VolunteerId, meeting.ClientId;

This will return your basic data, except that you have id's instead of names.

Next you join your volunteer and client tables to get names

SELECT volunteer.Name, client.Name, MIN(meeting.datetime), MAX(meeting.datetime) FROM meeting LEFT JOIN volunteer ON volunteer.id = meeting.VolunteerId LEFT JOIN client ON client.id = meeting.clientId GROUP BY meeting.VolunteerId, meeting.ClientId;

Now you should have names instead of ids in your results but you still don't have volunteers or clients who haven't been involved in any meetings. Those records are easy to include by changing the join to include records on all tables even if they don't have a matching id like so

SELECT volunteer.Name, client.Name, MIN(meeting.datetime), MAX(meeting.datetime) FROM meeting FULL JOIN volunteer ON volunteer.id = meeting.VolunteerId FULL JOIN client ON client.id = meeting.clientId GROUP BY meeting.VolunteerId, meeting.ClientId;

[hwsilver]

Hi. Thanks for your response. I tried creating the first table with the information I needed from the meeting table. It didn't work.
Access says that "the SQL statement could not be executed because it contains ambiguous outer joins".

I have been asked to create a report with a page for each client and another report with a page for each volunteer. It don't think that your recommendation does that, but I'm not sure. Perhaps it could if I group the output correctly.

[isladogs]

I'm finding it difficult to picture what you are having difficulties with.
A screenshot of your query designs would help or a cut down version of your database with anonymised data

In the meantime, you might find this article on my website useful: Query Join Types.
The article includes a section on ambiguous outer joins and how to deal with them

[hwsilver]

This is the SQL, with an INNER JOIN, but when I switch it to a LEFT JOIN I get an error message:
SELECT [FinCoaching Enrollment File 2].[Active Match], [FinCoaching Enrollment File 2].Uniq_Client_ID , [FinCoaching Enrollment File 2].[email ], [Coach Meetings Record 2].Uniq_Vol_ID, Max([Coach Meetings Record 2].[Coaching Session Date]) AS [MaxOfCoaching Session Date], Min([Coach Meetings Record 2].[Coaching Session Date]) AS [MinOfCoaching Session Date]
FROM [Coach Meetings Record 2] INNER JOIN [FinCoaching Enrollment File 2] ON ([FinCoaching Enrollment File 2].Uniq_Client_ID = [Coach Meetings Record 2].Uniq_Client_ID ) AND ([Coach Meetings Record 2].Uniq_Client_ID = [FinCoaching Enrollment File 2].Uniq_Client_ID );

There are no other relationships among the tables involved.

[isladogs]

There isn’t much point posting a query that does work.
Please post the SQL for the query that fails and give the error number/description.
I’m viewing this on my phone but it appears the query is based on just two tables. Ambiguous outer joins can only occur when 3 or more tables are used and where the join directions are in ‘opposite directions’.

However your SQL shows the SAME join expression has been used TWICE. WHY?
Remove one instance of the join (the one after the AND) and try again using the outer join

Did you look at the link I provided?

[hwsilver]

That did it. I don't know where the second ([Coach Meetings Record 2].Uniq_Client_ID = [FinCoaching Enrollment File 2].Uniq_Client_ID ) came from, but getting rid of it solved the problem! Thanks.

[isladogs]

Excellent.
I only noticed the 'double join' after I wrote the first part of my last reply.
In its original form, that would have created 2 join lines with opposing arrows when converted to an outer join...hence the ambiguous outer join message. First time I've ever seen that happen using only two tables! 😏