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.
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 6938 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. -
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;
-
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.
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
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.
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?
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.
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! 😏
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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)...
|
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...
|
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.
...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
| |