SQL Statement could not be executed because it contains ambiguous outer joins. Error

[dbaker5204]

I have a query which contains 3 tables and I am running into a problem where the query eliminates data that isnt included in one of the tables that I have linked. I tried changing the relationship link so that it would show everything from the one table and the fields from the other table that were equal and I get the follow error:
The SQL Statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. Well, I tried this and the data is still being eliminated- perhaps I am not doing it correctly...

I have a a table that has cases logged (tbl_MSP_Cases), a table that has all the agents names in it (tbl_MSP_RepNames) and a table that has the number of calls the agents took (tbl_MSP_CMS_AgentData). What is happening is, if agents dont log their calls, the whole day does not show up for them. I want it to show, but just show a blank for calls logged. The rep table needs to be included because the way the data feeds over, the agent names are not formatted the same in the other two tables.

Any help would be appreciated.

 

[MarkK]

Hi, maybe post the SQL. Otherwise the error message specifically describes how to solve this problem, so without seeing what you've got, it's tough to suggest anything.
hth
Mark

 

[dbaker5204]

This?

SELECT tbl_MSP_Cases.[Agent Name], tbl_MSP_Cases.[Opened Date], tbl_MSP_Cases.[Case Number], tbl_MSP_Cases.Subject, tbl_MSP_Cases.Status, tbl_MSP_Cases.[Created By], tbl_MSP_CMS_Agent_Data.ACD_Calls
FROM tbl_MSP_RepandSupNames INNER JOIN (tbl_MSP_Cases RIGHT JOIN tbl_MSP_CMS_Agent_Data ON tbl_MSP_Cases.[Opened Date] = tbl_MSP_CMS_Agent_Data.CMS_Date) ON (tbl_MSP_RepandSupNames.[Salesforce Name] = tbl_MSP_Cases.[Agent Name]) AND (tbl_MSP_RepandSupNames.[CUIC/CMS Name] = tbl_MSP_CMS_Agent_Data.CMS_Name);

 

[MarkK]

What is happening is, if agents dont log their calls, the whole day does not show up for them. I want it to show...

I don't think this can work at all since, if the agent did not log any calls for a particular day, then that day's data does not exist. It will not then be a matter of re-jigging the joins.
Mark

 

[dbaker5204]

So, there wont be a way to make it show that they still took calls? And just didnt log them?

 

[isladogs]

Are you SURE that is the query that causes the ambiguous joins message.
Your query has 3 tables with an INNER join and a RIGHT join
Perhaps the RIGHT join arrow is for a one to many join and is in the wrong direction?

The screenshot below is a deliberate example of an ambiguous join.
In that case it has 2 outer joins and the flow is in opposite directions!

 

[CJ_London]

try

SELECT tbl_MSP_Cases.[Agent Name], tbl_MSP_Cases.[Opened Date], tbl_MSP_Cases.[Case Number], tbl_MSP_Cases.Subject, tbl_MSP_Cases.Status, tbl_MSP_Cases.[Created By], tbl_MSP_CMS_Agent_Data.ACD_Calls
FROM tbl_MSP_RepandSupNames INNER JOIN (tbl_MSP_Cases RIGHT JOIN tbl_MSP_CMS_Agent_Data ON tbl_MSP_Cases.[Opened Date] = tbl_MSP_CMS_Agent_Data.CMS_Date)[COLOR=red] AS T[/COLOR] ON (tbl_MSP_RepandSupNames.[Salesforce Name] = [COLOR=red]T[/COLOR].[Agent Name]) AND (tbl_MSP_RepandSupNames.[CUIC/CMS Name] = [COLOR=red]T[/COLOR].CMS_Name);

You also can't join a single table to two tables with a right join

e.g.

 

[MarkK]

If there is no data, there is no way you can rejig the SQL to show it, because it doesn't exist.
Mark

 

[Pat Hartman]

When you need to use inner and outer joins in a way that Access objects to, you can usually get around the problem by breaking up the query into two parts. Join two of the tables in one query. Then change the master query to join a table to the new query.

 

[dbaker5204]

Well they took calls, they just didnt log them...

 

[CJ_London]

did you try the solution in post #7?

 

[The_Doc_Man]

So, there wont be a way to make it show that they still took calls? And just didnt log them?

Garbage in, garbage out. Nothing in, nothing out. Very simple.

Your INNER JOIN guarantees the "Nothing in, nothing out" prediction will be true.

Well they took calls, they just didnt log them...

Well, actually, that solution is SIMPLE. But it ain't within Access. Your agents are paid to take calls, right? If they don't take calls, they don't get paid. And the only way to prove they TOOK calls is to log them. So... no calls logged? No pay.

Stated another way, a part of their job is to document their interactions with the end customers. If they don't log the calls, they are not doing that part of their jobs. If you aren't the boss, then you need to talk to the boss and 'splain it to him/her.

I'll toss in "Old Programmer's rule #2" - Access won't tell you anything you didn't tell it first, or at least tell it how to figure it out.

Well, if you didn't tell Access anything about the call, it won't tell anyone else about the call either. It isn't a hard concept.

You can perhaps get their attention another way. You can tell them that it is like either income taxes or rest room activities... you aren't finished with either until the paper work is done.

 

[Pat Hartman]

It is quite reasonable to want to see all active agents whether or not they took calls.

Try the solution in #9

 

[dbaker5204]

I got it to work, thank you!

 

[Pat Hartman]

What did you do?

 

[dbaker5204]

What was suggested in #9.

 

[Pat Hartman]

Thanks for letting others know it works.