Ambiguous outer join

[Laurel]

I have two tables, tblAcademics and tblScores. Each has an ID column and a
date column (Student_ID in both tables, and Assessment_Date and Score_Date
for the date columns. I want to create a query which has all of the
tblAcademics rows, whether or not there is an associated tblAttendance row,
matching on both ID and date. When I create two joins, and select option 2
(all of table 1 and only table 2 when the two values match), I get the error
"Ambiguous outer join. 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." I'm not sure how to implement this,
especially since both the ID and date are criteria on the select that are
entered at run time. This seems like a very common thing to have to do.
Can I make a combined column in each table somehow? Also, I need to add
another criterion on the 2nd table tblScores.PeriodCode = "O". I'm trying
to make this query because putting functions in the query, like "Dlookup" on
this and that in the report itself is slowing the report down in a server
environment to the point that it isn't usable.

 

[John Spencer]

Are you changing BOTH join lines? If you only change one then you will get
the message you are receiving.

Any easy way to make the change is to just join as normal and then switch to
the SQL view.

Look for INNER JOIN and change that to Left Join or Right Join (depending on
which table you want to show all records in).

Also, I need to add another criterion on the 2nd table tblScores.PeriodCode
= "O".

This will defeat the LEFT or RIGHT JOIN you are setting up.

You can handle this problem by creating a separate query on tblScores that
returns only the records with the specified PeriodCode. Save it and then
join this saved query to tblAcademics in place of using tblScores.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Roger Carlson]

When Access processes a multiple table query, it needs to determine the
order in which joins should be made. Should it join Table1 to Table2 first
and then join Table3? Or should it do it in some other order. This is part
of the Rushmore technology of the Jet

engine. It tries to determine the most efficient way to process the query.



In the case of standard Equi-Joins, the query result will not vary based on
the order that they are joined. However, this is not the case with
Outer-Joins. There are times, when using an Outer-Join, that the result of
the query will be different depending on the order in which joins are
created. Access cannot now determine the order to join the tables. This is
an Ambiguous Outer Join.



Another way to explain it, is in terms of what you see in the QBE grid. A
table which participates in an Outer join and the arrow is pointing
*towards* it cannot participate in either an Inner Join, or another Outer
Join where the arrow points towards it.



Graphically:

These are illegal:

1) Table1 -----> Table2 ----- Table3

2) Table1 -----> Table2 <---- Table3



This is legal:

3) Table1 -----> Table2 ----> Table3



In order to solve this, you are going to have to split the query into a
stacked query, just like Access suggests you should in the error message.
So you create a query joining the first two tables and, in a second query,
join the first query to the third table.



Thus:

1) Query1:

Table1 -----> Table2



2) Query2:

Query1 ------ Table2


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Laurel]

Here's the whole ugly thing. I didn't want to risk pairing it down. I want
to get tblAcademics when there are no qryScoresOverridePeriods or no
tblAttendance. I created qryScoresOverridePeriods on the advice of John
Spencer - next response. I'm answering his separately. The sql for
qryScoresOverridePeriods is after the main sql.

SELECT tblAcademics.Student_id AS Student_id, tblAcademics.Description AS
Description, tblAcademics.Assessment_Date AS Assessment_Date,
tblAcademics.Subject AS subject, tblAcademics.Type AS Type,
tblAcademics.Sequence, tblAcademics.Grade AS Grade, tblAcademics.Comment AS
Comment, tblStudents.Class_Code AS Class_Code, tblStudents.First_Name AS
First_Name, tblStudents.Last_Name AS Last_Name, tblAcademics.Assessment_Code
AS Assessment_Code, tblAttendance.Att_Date AS Att_Date,
tblAttendance.Present AS Present, tblAttendance.CP AS CP,
tblAttendance.TardyExp AS TardyExp, tblAttendance.TardyUnexp AS TardyUnexp,
tblAttendance.AbsentExp AS AbsentExp, tblAttendance.AbsentUnexp AS
AbsentUnexp, tblAttendance.Suspension AS Suspension, tblAttendance.Dismissal
AS Dismissal, tblAttendance.DepartureUnexp AS
DepartureUnexp,qryScoresOverridePeriods.Comments as Override_Comment
FROM tblAcademics,tblStudents, tblAttendance,qryScoresOverridePeriods
WHERE (tblAcademics.Student_id=tblstudents.student_id) And
(tblAcademics.Student_id=tblAttendance.Student_ID) And
(tblAcademics.Assessment_Date=tblAttendance.Att_Date) and
(tblAcademics.Student_id = qryScoresOverridePeriods.Student_id) and
(tblAcademics.Assessment_date = qryScoresOverridePeriods.Score_Date) ;

SELECT tblScores.*, tblScores.Period_Code
FROM tblScores
WHERE (((tblScores.Period_Code)="0"));

 

[Laurel]

See three responses below.

Are you changing BOTH join lines? If you only change one then you will
get the message you are receiving.

I get the error whether I set one or both joins to option 2.

Any easy way to make the change is to just join as normal and then switch
to the SQL view.> Look for INNER JOIN and change that to Left Join or
Right Join (depending on which table you want to show all records in).

As you can see from my original SQL in the response above, I didn't have any
INNER joins. But I redid a simpler version in the DESIGN window, and got
three INNER JOINs. In the SQL below, I had replaced each INNER with a RIGHT
or LEFT, but I get this error: "Join expression not supported."

SELECT tblAcademics.*, tblStudents.*, tblAttendance.*,
qryScoresOverridePeriods.*
FROM ((tblAcademics LEFT JOIN tblStudents ON tblAcademics.Student_id =
tblStudents.Student_ID) RIGHT JOIN qryScoresOverridePeriods ON
(tblAcademics.Assessment_Date = qryScoresOverridePeriods.Score_Date) AND
(tblStudents.Student_ID = qryScoresOverridePeriods.Student_ID)) LEFT JOIN
tblAttendance ON (tblAcademics.Assessment_Date = tblAttendance.Att_Date) AND
(tblAcademics.Student_id = tblAttendance.Student_ID);

 

[Laurel]

But since my queries must be limited by values in a form, how can I make two
queries respect those values? I only know how to make the query that is the
row source "know" about values in the form.

 

[John Spencer]

Generally, it is a good idea to make sure the joins all are in the same
direction. I think that the following MIGHT work.

SELECT tblAcademics.*, tblStudents.*, tblAttendance.*
, qryScoresOverridePeriods.*
FROM (((tblAcademics LEFT JOIN tblStudents
ON tblAcademics.Student_id = tblStudents.Student_ID)
LEFT JOIN qryScoresOverridePeriods
ON tblAcademics.Assessment_Date = qryScoresOverridePeriods.Score_Date AND
tblAcademics.Student_ID = qryScoresOverridePeriods.Student_ID)
LEFT JOIN tblAttendance
ON tblAcademics.Assessment_Date = tblAttendance.Att_Date AND
tblAcademics.Student_id = tblAttendance.Student_ID)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Roger Carlson]

Unless I'm missing something in the SQL above, I don't see where you are
limiting your query based on values in the form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Laurel]

The Open command that opens the report adds a where clause that Access adds
on as a filter.

 

[Laurel]

Yes, indeedy! Thanks very much!

Generally, it is a good idea to make sure the joins all are in the same
direction. I think that the following MIGHT work.

SELECT tblAcademics.*, tblStudents.*, tblAttendance.*
, qryScoresOverridePeriods.*
FROM (((tblAcademics LEFT JOIN tblStudents
ON tblAcademics.Student_id = tblStudents.Student_ID)
LEFT JOIN qryScoresOverridePeriods
ON tblAcademics.Assessment_Date = qryScoresOverridePeriods.Score_Date AND
tblAcademics.Student_ID = qryScoresOverridePeriods.Student_ID)
LEFT JOIN tblAttendance
ON tblAcademics.Assessment_Date = tblAttendance.Att_Date AND
tblAcademics.Student_id = tblAttendance.Student_ID)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.