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