468,469 Members | 2,666 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,469 developers. It's quick & easy.

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

2 2Bits
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.
5 Days Ago #1
3 797
Banfa
9,052 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT meeting.VolunteerId, meeting.ClientId, MIN(meeting.datetime), MAX(meeting.datetime) FROM meeting GROUP BY meeting.VolunteerId, meeting.ClientId;
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. 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;
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. 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;
  2.  
4 Days Ago #2
hwsilver
2 2Bits
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.
4 Days Ago #3
isladogs
259 Expert 256MB
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
3 Days Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by StealthBananaT | last post: by
reply views Thread by Charles Haven | last post: by
1 post views Thread by Jamie Burns | last post: by
reply views Thread by Bertrand GAVROY | last post: by
5 posts views Thread by jim | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.