473,288 Members | 1,704 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

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

7 Nibble
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.
Jun 8 '21 #1

✓ answered by 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?

7 6957
Banfa
9,065 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.  
Jun 10 '21 #2
hwsilver
7 Nibble
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.
Jun 10 '21 #3
isladogs
454 Expert Mod 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
Jun 10 '21 #4
hwsilver
7 Nibble
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.
Jun 17 '21 #5
isladogs
454 Expert Mod 256MB
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?
Jun 17 '21 #6
hwsilver
7 Nibble
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.
Jun 17 '21 #7
isladogs
454 Expert Mod 256MB
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! 😏
Jun 17 '21 #8

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

Similar topics

13
by: StealthBananaT | last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the...
0
by: Charles Haven | last post by:
Hello all, Is it possible to LEFT JOIN a table to itself? I want to see all records in a table where the year of the sales date is 2003 and where the salesman sold an item to a customer to...
1
by: Jamie Burns | last post by:
Hello, I am trying to perform a LEFT JOIN on a table which may or may not have matching rows. If there are no matching rows, it returns NULL's for all the missing fields. Is there anyway of...
0
by: Bertrand GAVROY | last post by:
Hi, I met an issue running a query in GENIO ETL (in a dataset). The dataset is made of left joins between 3 DB2 files. Thus it seems to loop ! When launched, the query generates 15Gb of...
0
by: pj | last post by:
/* Make two tables, then find that a left join between them works, unless you add a view on top of one table -- if you add a view and use it, the left join fails -- rather, it acts as an inner join...
14
by: Ryan | last post by:
I want to do the following SQL statement in Access. However, it won't allow me to have the secondary part of my join statement and tells me that this is not supported. OK, so Access doesn't support...
2
by: David F | last post by:
Hello, I have what seems like a simple left join query, but Access returns results like it's an inner join. I've tried numerous combinations without success. I have a table (ProjectList)...
5
by: jim | last post by:
Hi, I've browsed several posts, but still haven't found the answer I'm looking for. I have one table (A) that contains a list of values I want to return. I have two other tables (B) and (C)...
6
by: ipskalsi | last post by:
I have a table Multiple user at same time perform multiple operation on that table like insert , update , fetch etc. During fetching records i use many left outer join which decrease the...
5
by: jimatqsi | last post by:
I have some code that works fine in an all Access environment. I am moving the back-end to SQL Server 2012. After moving the back-end I am getting this error when I run a particular query. ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.