doctortony
asked on
Ambiguous Outer Joins
I do a report periodically that uses 3 semesters of course failure data. The data for each semester is brought into Access as a separate table: e.g., Jan01, Jun01, Jan02. The report will use several fields from the current table—in this case, Jan02—and one field from each of the earlier tables, Jan01 and Jun01. I want to use a make table query to bring the selected fields from each table into a single table.
In each table, the primary key is a composite of the school’s 4-digit unit number and 2-digit grade (called UntGrd); so, e.g., 101009 signifies 9th grade in unit 1010.
I have no problem designating UntGrd as the primary key in each table, which implies that the values for UntGrd are unique in each table. I have no problem relating the tables and enforcing referential integrity. The most recent table (Jan02) is the primary table. Therefore, referential integrity implies that all records in Jan01 and Jun01 are in Jan02. There are no “orphans” in Jan01 and Jun01 although “childless parents” are permitted in Jan02. The joins from Jan02 to Jan01 and Jun01 are left outer joins (all records in Jan02 and only the matching records in Jan01 and Jun01).
My problem is that when I run the make table query, I get the message “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 you SQL statement.” I would expect the joins to be performed in the order that tables are called into the query; so, I obviously don’t understand the problem.
Doing a work-around is easy enough. I can run a make table query to join any two tables, say Jan01 and Jun01, to produce Temp01. Then, I can run another make table query to join Temp01 and Jan02. Fine, but I still would like to understand that error message. 100 points to anyone who can explain it.
In each table, the primary key is a composite of the school’s 4-digit unit number and 2-digit grade (called UntGrd); so, e.g., 101009 signifies 9th grade in unit 1010.
I have no problem designating UntGrd as the primary key in each table, which implies that the values for UntGrd are unique in each table. I have no problem relating the tables and enforcing referential integrity. The most recent table (Jan02) is the primary table. Therefore, referential integrity implies that all records in Jan01 and Jun01 are in Jan02. There are no “orphans” in Jan01 and Jun01 although “childless parents” are permitted in Jan02. The joins from Jan02 to Jan01 and Jun01 are left outer joins (all records in Jan02 and only the matching records in Jan01 and Jun01).
My problem is that when I run the make table query, I get the message “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 you SQL statement.” I would expect the joins to be performed in the order that tables are called into the query; so, I obviously don’t understand the problem.
Doing a work-around is easy enough. I can run a make table query to join any two tables, say Jan01 and Jun01, to produce Temp01. Then, I can run another make table query to join Temp01 and Jan02. Fine, but I still would like to understand that error message. 100 points to anyone who can explain it.
Doctortony,
Assuming you want to retrieve the primary key, and the
field F2 that is in all your tables, into a new table
called NewTableName, you should have the following SQL
statement:
--------------------------
SELECT
Jan02.UntGrd,
Jan02.F2,
Jun01.F2 AS F22,
Jan01.F2 AS F23
INTO
NewTableName
FROM
(Jan02 LEFT JOIN Jun01 ON Jan02.UntGrd = Jun01.UntGrd)
LEFT JOIN Jan01 ON Jan02.UntGrd =Jan01.UntGrd
;
--------------------------
F22 new name in new table for field Jun02.F2
F23 new name in new table for field Jan01.F2
You should see something similar in your query SQL.
Depending on your field/table names, you'll have brackets
or apostrophes surrounding the field/table names.
Note that you can edit the SQL statement (if you think
Access did not translate correctly) then, when going
back to Design window, Access will update the query from
the SQL you just edited (before editing, copy/paste the
query with another name,... in case ...)
Hope this help,
Sébastien
Assuming you want to retrieve the primary key, and the
field F2 that is in all your tables, into a new table
called NewTableName, you should have the following SQL
statement:
--------------------------
SELECT
Jan02.UntGrd,
Jan02.F2,
Jun01.F2 AS F22,
Jan01.F2 AS F23
INTO
NewTableName
FROM
(Jan02 LEFT JOIN Jun01 ON Jan02.UntGrd = Jun01.UntGrd)
LEFT JOIN Jan01 ON Jan02.UntGrd =Jan01.UntGrd
;
--------------------------
F22 new name in new table for field Jun02.F2
F23 new name in new table for field Jan01.F2
You should see something similar in your query SQL.
Depending on your field/table names, you'll have brackets
or apostrophes surrounding the field/table names.
Note that you can edit the SQL statement (if you think
Access did not translate correctly) then, when going
back to Design window, Access will update the query from
the SQL you just edited (before editing, copy/paste the
query with another name,... in case ...)
Hope this help,
Sébastien
ASKER
Sebastienm,
If I right click in the query designer window and choose SQL View, it does not show me the SQL. Instead, it tries to execute the query, and it returns the error message about ambiguous joins.
If I right click in the query designer window and choose SQL View, it does not show me the SQL. Instead, it tries to execute the query, and it returns the error message about ambiguous joins.
Can you tell me the fields you want from these tables.
I'll create the SQl string then send it back to you, then:
-Create a new blank query in Access
-In SQL View, paste the SQL string
-Go back to query designer
--> this should translate the SQL into the usual diagramm
I'll also try to save the Query and send it to you, so
that you can import it. If the table/field names are the
same, it should work.
Sébastien
I'll create the SQl string then send it back to you, then:
-Create a new blank query in Access
-In SQL View, paste the SQL string
-Go back to query designer
--> this should translate the SQL into the usual diagramm
I'll also try to save the Query and send it to you, so
that you can import it. If the table/field names are the
same, it should work.
Sébastien
ASKER
Sebastienm,
If it would help, I could email you an abbreviated copy of the Access database (all fields but only a few records per table). You can email me directly at apitruzzello@csc.cps.k12.i
If it would help, I could email you an abbreviated copy of the Access database (all fields but only a few records per table). You can email me directly at apitruzzello@csc.cps.k12.i
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sebastienm,
Thanks a million! What puzzles me is that, if you check the relationships window in the DB I sent you, you'll notice that there are only two relationships: Table3-->Table1 and Table3-->Table2. When I reconstructed the query (just now), I noticed that when I selected the three tables, indeed, Table1 was linked to Table2! I always thought that once the relationships were defined (in the relationships window), they were set. Any query acted on the relationships as they were set. I didn't know that the act of constructing a query could somehow insert a new relationship independent of what was specified in the relationships module. And, you're right, I did not see the link between Tables 1 and 2. Because I thought the relationships were set, I could have sat there till Hell freezes over, and I would not have seen the problem. Thanks, again.
Thanks a million! What puzzles me is that, if you check the relationships window in the DB I sent you, you'll notice that there are only two relationships: Table3-->Table1 and Table3-->Table2. When I reconstructed the query (just now), I noticed that when I selected the three tables, indeed, Table1 was linked to Table2! I always thought that once the relationships were defined (in the relationships window), they were set. Any query acted on the relationships as they were set. I didn't know that the act of constructing a query could somehow insert a new relationship independent of what was specified in the relationships module. And, you're right, I did not see the link between Tables 1 and 2. Because I thought the relationships were set, I could have sat there till Hell freezes over, and I would not have seen the problem. Thanks, again.
Thank you Tony.
Access tend to link fields with identical names eventhough
the relationaships are set.
In menu Tools>Option, tab Tables/Queries, there is an
option called AutoJoin. Unchecking it may cancel this
behavior (not sure)
From MS help:
"Access automatically creates joins if you add two tables
to a query and the tables each have a field with the same
or compatible data type and if one of the join fields is a
primary key"
Regards,
Sébastien
Access tend to link fields with identical names eventhough
the relationaships are set.
In menu Tools>Option, tab Tables/Queries, there is an
option called AutoJoin. Unchecking it may cancel this
behavior (not sure)
From MS help:
"Access automatically creates joins if you add two tables
to a query and the tables each have a field with the same
or compatible data type and if one of the join fields is a
primary key"
Regards,
Sébastien
Could you post the SQL (In the query Designer window,
right-click the window and choose SQL).
Thanks,
Sébastien