Ambiguous Outer Joins. What Are They? How To Work Around?
Oct 30, 2007
I have a database in which i am trying to create some forms in order to search for records. The database contains several tables. Some tables are linked using a compound key while others have a single PK. I created the search form which has different searching criteria. Once a user selects the criteria, they hit the search button (there are different buttons for each type of search) the button opens up a form to display all the data. The problem is that there are many tables to report on. I created a form with a subform. The tables associated with the main form are all being retrieved fine. however the subform doesn't seem to work. I created the subform using a query to select all the fields from the desired tables, i gave this query a criteria based on the main form so that the Id's would match. (ie. Main form ID = Firstable in subform.ID)
I got a blank sheet in the subform when i used the search. i presumed that my query was not pulling records because of the referential integrity rules. i went in and changed the relationship rules so that it would include all records from the first table and matching records from the second table. (middle option in the list when editing a relationship) I now get the following error when i try to run the query.
"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."
Could someone please tell me what this means as the help is... how shall we say it.. written in true Microsoft Fashion.
And though it tells me how to fix it, i'm not really sure what it's telling me to do. I am using Access2003 But the Database is in an access 200 file format.
Attached is a pdf of the query window showing the relationships and table structure; (sorry for the quality) the linkage is also permanent at the relationship window. I created a form (columnar) of Rooms; loaded a subform (columnar) of the projects; and then loaded the students (tabular) as a subform on the projects subform. The data entry is flawless; tabs through each field and form to form in sequence.
After entering several rooms data I tested it at the query level by loading the three tables: rooms, projects, and students, and the permanentely established linkage with junctions came in automatically. I thought I was home free--but when I run the query, I get zero records.
When I attempt various joins, thinking this will yeild all records from the many tables and their match, I get "ambiguous outer joins" and it says to run a separate query and add it to the SQL Statement?
Thanks for any help,
Almost funtional in Ann Arbor........
Oh, and thanks Pat Hartman for the tip on linkage -- although I may have screwed it up anyway.
I'm new to the Access SQL world but I have a fairly complicated query that needs to be written for a project I'm working on. I am having trouble grasping the join concepts in access. My Query so far is:
SELECT u.user_logon_tx, ur.aff_sec_role_id, r.sec_role_ds, c.sec_cls_ds, o.sec_obj_ds, ci.aff_sec_cls_id, ci.sec_cls_item_ds, IIf(p.perm_in Is Null,0,p.perm_in) AS perm FROM aff_sec_cls_item AS ci, aff_sec_user AS u, aff_sec_role AS r, aff_sec_user_role AS ur, aff_sec_obj AS o, aff_sec_cls AS c, aff_sec_obj_item_role_perm p, ci LEFT OUTER JOIN p ON ci.aff_sec_cls_item_id=p.aff_sec_cls_item_id WHERE u.aff_sec_user_id=1 And ur.aff_sec_user_id=u.aff_sec_user_id And ur.aff_sec_role_id=r.aff_sec_role_id And o.aff_sec_obj_id=1155 And o.aff_sec_cls_id=ci.aff_sec_cls_id And o.aff_sec_cls_id=c.aff_sec_cls_id And ur.aff_sec_role_id=p.aff_sec_role_id And o.aff_sec_cls_id=p.aff_sec_cls_id And o.aff_sec_obj_id=p.aff_sec_obj_id;
I have gotten it to work by using just the tables I'm doing the outer join on but when I add everything else I get the Ambiguous Join error. I wish I was using Oracle Thanks for the help.
I'm an Oracle guy, not an access guy, so I need some help please. I have 2 questions at least.
I have this query:
SELECT tOrders.ID, tCustomers.[First Name], tCustomers.[Last Name], t_OrderDetails.Product_ID, tProducts.[Prod Desc], tProducts.Price, t_OrderDetails.Qty FROM tYears INNER JOIN (tProducts INNER JOIN ((tMonths INNER JOIN (tCustomers INNER JOIN tOrders ON tCustomers.ID = tOrders.Customer_ID) ON tMonths.ID = tOrders.Month_ID) INNER JOIN t_OrderDetails ON (tOrders.ID = t_OrderDetails.ID) AND (tOrders.Month_ID = t_OrderDetails.Month_ID) AND (tOrders.Year_ID = t_OrderDetails.Year_ID)) ON (tProducts.ID = t_OrderDetails.Product_ID) AND (tProducts.Month_ID = t_OrderDetails.Month_ID) AND (tProducts.Year_ID = t_OrderDetails.Year_ID)) ON tYears.ID = tOrders.Year_ID WHERE (((tYears.Year)=[Forms]![pYEAR-MONTH-rpt]![Year]) AND ((tMonths.Month)=[Forms]![pYEAR-MONTH-rpt]![Month]));
And I want every ROW in the tProducts table to show up for each record in t_OrderDetails. The Orders Table is tOrders and each detail row is in 't_OrderDetails'. I went into design view and changed the links from t_orderdetails to tproducts as follows in this screenshot:
Anyway, in Oracle, I would just simply add (+) to each join column in tProducts but here the message states I must create a subquery and then include that query into the main query. How would I go about doing that?
Any ideas at all would be much appreciated. I'm doing a project for our church for free, and Access is all we have available to use. I've got too far now to turn back.
2nd Question is:
My ultimate goal, is to get this to work, and then to put the totals of each product in tProducts into it's own COLUMN on a report showing how much of something each customer ordered. There are only 4 to 8 products each month, so I hope to see something like:
Box of Food Special #1 Special #2 Special #3 George Jones 1 0 2 3 Dirk Smith 3 2 0 0 Tyler Jones 1 0 3 1
We need the sums to print in columns, not rows. I'm trying to get a loading manifest (for lack of better words) so the volunteers can see how much of each product the customer ordered. There will never (knock on wood) be more than 4 to 8 products ever.
Any ideas on how to do that?
Thanks for any help at all. I really appreciate it.
I have made a database and have made a query that takes the info from the tables and creates a report. I have a subtable in the form and have added another sub table. When I try to add the new subtable info into the query for the report, I get "ambigous outer joins"??? What the heck is that?????
Hi there. I have this query:SELECT [Range Plan 2006].[Parent Category] AS Expr1, [Range Plan 2006].Attribute AS [Sub category],[This Year All Prog].Category, [This Year All Prog].Product, [Description Master].Description, [This Year All Prog].SumOfQty AS [TY Cum Qty], [This Year All Prog].SumOfValue AS [TY Cum Value], [This Year All Prog].AvgOfMargin AS [TY Cum Margin],[Last Year All Prog].SumOfQty AS [LY Cum Qty], [Last Year All Prog].SumOfValue AS [LY Cum Value], [Last Year All Prog].AvgOfMargin AS [LY Cum Margin], [Last Year All Wk].SumOfQty AS [LY Wk Qty], [Last Year All Wk].SumOfValue AS [LY Wk Value], [Last Year All Wk].AvgOfMargin AS [LY Wk Margin], [This Year All Wk].SumOfQty AS [TY Wk Qty], [This Year All Wk].SumOfValue AS [TY Wk Value], [This Year All Wk].AvgOfMargin AS [TY Wk Margin], [Weekly Product Sales Analysis Stock].Qty AS [Total Stock], [Weekly Product Sales Analysis Stock].[Outstanding PO's], [Weekly Product Sales Analysis Stock].[Total Commitment] AS [Total Commit], IIf(IsNull([Product List - New].product),"",IIf(Year([Product List - New].[date entered])<Forms![Menu - Weekly Reports]!Year,"","NEW")) AS NEW, [Range Plan 2006].[Product Code] AS Expr2FROM [Range Plan 2006], ((((([This Year All Prog] LEFT JOIN [Last Year All Prog] ON [This Year All Prog].Product = [Last Year All Prog].Product) LEFT JOIN [Last Year All Wk] ON [This Year All Prog].Product = [Last Year All Wk].Product) LEFT JOIN [This Year All Wk] ON [This Year All Prog].Product = [This Year All Wk].Product) LEFT JOIN [Weekly Product Sales Analysis Stock] ON [This Year All Prog].Product = [Weekly Product Sales Analysis Stock].Product) LEFT JOIN [Description Master] ON [This Year All Prog].Product = [Description Master].Product) LEFT JOIN [Product List - New] ON [Description Master].Product = [Product List - New].Product;when I start it, access tells me that there are some ambiguous outer joins. I don't understand so much about outer joins, could you help me plz?Thanks.
My query references 2 related tables: one for persons (PERS) and one for telephone/fax numbers and email addresses (CONT, for Contacts). The relevant fields are:
The foreign key come_id refers to a table for contact methods (COME), either "Phone (Home)", "Phone (Work)", "Mobile", "Fax" or "Email".
Now i want to list all persons with their home phone number and email address, also if they don't have one. It seems to be impossible to get it ... I will explain what happens. Lets start simple: first list all persons with their home phone number (come_id = 1): SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone FROM PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id WHERE (((IIf(IsNull([come_id]),1,[come_id]))=1)); This works fine. The IIf expression is necessary since we are dealing with an outer join: not all persons have a home phone number. If we would simply put "WHERE come_id = 1" then the query produces only the persons that have a home phone number.
But now i also want to see the email address (come_id = 5): SELECT PERS.pers_forename, PERS.pers_surname, CONT.cont_number AS Phone, CONT_1.cont_number AS Email FROM CONT AS CONT_1 RIGHT JOIN (PERS LEFT JOIN CONT ON PERS.pers_id = CONT.pers_id) ON CONT_1.pers_id = PERS.pers_id WHERE (((IIf(IsNull([cont].[come_id]),1,[cont].[come_id]))=1) AND ((IIf(IsNull([cont_1].[come_id]),5,[cont_1].[come_id]))=5)); It seems perfectly logical: i added a second alias CONT_1 for the email address. Since this is also optional we have a second outer join, and the WHERE condition should also use an IIf expression. The result is not correct though: the resulting recordset shows only the persons that have both a home phone number and an email addres or neither! I have a lot of experience with SQL and queries, but i know i am not infallible. Nevertheless i am quite convinced that i should get all the persons: those that have a home phone number or an email address, or both or neither ... I hope that someone of you can explain this.
I have three tables: Employees, Gender, Diversity. Both the Gender and Diversity tables are one to many relationships with the Employees Table. I am trying to run a query that will output a count of all diversities and genders. For example:
I am trying to get all counts, even if the combination of diversity/gender is not in the employees table. I am going to use that information in a Crosstab query.
What I thought would work was do a Left Join For Diversity and Employee such as:
SELECT Diversity.[Diversity Description], Count(Employee.ID) AS CountOfID FROM Diversity LEFT JOIN Employee ON Diversity.ID = Employee.Diversity GROUP BY Diversity.[Diversity Description];
Then, do a Left Join for Gender and Employee such as:
SELECT Gender.[Gender Description], Count(Employee.ID) AS CountOfID FROM Gender LEFT JOIN Employee ON Gender.[Gender ID] = Employee.Gender GROUP BY Gender.[Gender Description];
And then do a Union. But that doesn't work.
Any thoughts or comments would be much appreciated!
I have this query in design view and in an asp page and it works fine:
SELECT dbo_feedback.*, dbo_origin.originName, dbo_product.prodname, dbo_category.catName FROM dbo_product INNER JOIN (dbo_origin INNER JOIN ((dbo_feedback INNER JOIN (dbo_category INNER JOIN links_cat ON dbo_category.catID = links_cat.CatID) ON dbo_feedback.id = links_cat.FeedbackID) INNER JOIN links_product ON dbo_feedback.id = links_product.FeedbackID) ON dbo_origin.originID = dbo_feedback.origin) ON dbo_product.prodID = links_product.ProductID;
BUT, I want to return all feedback entries, even if they have no matching Product or Category. :confused: When I change the inner joins to outer joins I get a syntax error in the browser window. I changed the join type in the relationship diagram in Access and tried to recreate this in query designer, but Access says the statement cannot be executed because of ambiguous outer joins.
FYI, one feedback can have many products, many categories, and only one origin. I have the joins correct and enforced.
I am trying to execute the SQL below (tested in other programs, works fine) but access is giving me the "join expression not supported" error. How can this query be used in MS Access? I have tried breaking some of the joins clauses into separate queries, but I can't get it to work and it is making things much more complicated. Also note that moving the join conditions to the where clause could impact performance (this is a very large DB) Could I execute this via code somehow? Thanks.
SELECT lp.loan_id, lp.days_delinquent, lp.current_balance, pc.product_name FROM mtgwl.loan_payment lp inner join mtgwl.deal_loan_relation dlr on lp.loan_id=dlr.loan_id and lp.time_out='9999-12-31-23.59.59.0' and lp.as_of_date='2006-04-30' and dlr.time_out='9999-12-31-23.59.59.0' and dlr.type='DEAL'
inner join mtgwl.deal_loan_relation pdr on dlr.deal_id=pdr.loan_id and pdr.time_out='9999-12-31-23.59.59.0' and pdr.type='PRODUCT'
inner join mtgwl.product_control pc on pc.product_id=pdr.deal_id and pc.time_out='9999-12-31-23.59.59.0' and pc.product_name='GSAMP 2003 AHL'
I am trying to execute the SQL below (tested in other programs, works fine) but access is giving me the "join expression not supported" error. How can this query be used in MS Access? I have tried breaking some of the joins clauses into separate queries, but I can't get it to work and it is making things much more complicated. Also note that moving the join conditions to the where clause could impact performance (this is a very large DB) Could I execute this via code somehow? Thanks.
SELECT lp.loan_id, lp.days_delinquent, lp.current_balance, pc.product_name FROM mtgwl.loan_payment lp inner join mtgwl.deal_loan_relation dlr on lp.loan_id=dlr.loan_id and lp.time_out='9999-12-31-23.59.59.0' and lp.as_of_date='2006-04-30' and dlr.time_out='9999-12-31-23.59.59.0' and dlr.type='DEAL'
inner join mtgwl.deal_loan_relation pdr on dlr.deal_id=pdr.loan_id and pdr.time_out='9999-12-31-23.59.59.0' and pdr.type='PRODUCT'
inner join mtgwl.product_control pc on pc.product_id=pdr.deal_id and pc.time_out='9999-12-31-23.59.59.0' and pc.product_name='GSAMP 2003 AHL'
I downloaded a sample DB that someone had posted to the site and I’m trying to modify it for my use. I thought I had everything correct but when I go to the form and try to enter any data into any field I get a popup box “The expression Before Insert you entered as the event property setting produced the following error: Ambiguous name detected: DocketNumber_BeforeUpdate. Would someone give this DB a look and tell me how to correct this problem. Thanks
I just restructured my DB and I was wondering if anyone can give me some advice on whether or not my joins/relations are correct. I left some joins/relations out because I wasn't sure what relation I should use.
I have a form and subform linked by the ID field (AutoNumber) on the Form and Record ID field (Number) on the subform. I have the field size on the Record ID field set to Long Integer.
When the form is opened, a message pops up "The LinkMasterFields property setting has produced this error: 'Ambiguous name detected: Add_client_info_Click' There was a macro set up with the name Add client info. However, that macro is not being used on either of these forms. I don't understand why this message is popping up.
Any ideas? I have checked each form, each form section and each field to make sure that macro didn't somehow get assigned, and I can't find it anywhere in relation to these 2 forms.
I've done a search of the forums, the results of which have more-or-less caused me to understand (roughly) what the problem might be and how to locate the code which is causing it. Sadly, I am a bit of a Visual Basic virgin, and I am reluctant to wade right in because if I screw up this database I'll have decimated my company's advertising and marketing strategy (mailouts!).
I was trying to make a command button on a switchboard (which I have done before, but not recently) -- and was taking a bit of a trial-and-error approach. I wanted the button to result in the previewing of a set of mailing labels (which the user would susbsequently print after reviewing). One of my first attempts resulted in the button automatically printing, so I "cut" it, and several close-but-no-cigar attempts follwed, all of which I used the "cut" command to dispose of. The last attempt I made resulted in the following message: "The expression On Open you entered as the event property setting produced the following error: Ambiguous name detected: Bulletin_Board_labels_Click. -The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. -There may have been an error evaluating the function, event, or macro."
I "cut" the button I had just created and thought that might fix it. It didn't. I still got the above message whenever I tried to open either this switchboard or the main switchboard. I tried closing down Access and restarting it. Same message, same problem.
Below is the code from the switchboard in question -- the last few entries are the relevant ones: anything to do with "bulletin board" is stuff I was working on. As far as I can tell there are currently no duplicate queries, reports, macros, etc, named "Bulletin Board Labels" (theres is one query named "Bulletin Board Query", and one report named "Bulletin Board Mailout labels", and that's it.)
The code for the switchboard follows...
Please help me resolve this problem?
:confused: Jennifer _________________________________________
Option Compare Database Option Explicit
Private Sub Command0_Click() On Error GoTo Err_Command0_Click
Dim stDocName As String
stDocName = "All Art Galleries" DoCmd.OpenReport stDocName, acPreview
Okay i have 3 tables I would like to join them all by a field called twp, but the problem is in some tables the field twp might contain more or less rows than table2 and table3 or vice versa. Is there a way to include all the rows from all tables in a query. Suppose if table2 has a column which table1 doesnt have, then it will just show table1 column as blank etc.. Iv tried just doing an outer join on all the tables but for some reason access doesnt allow it.
I'm trying to write a query that will get all the records from one table, and only certain records from others. (Access2000) I'm getting a "join expression not supported" error however, so my question is two fold.
1. Does Access2000 support an inner and outer join the in same query? 2. Does my query below just have a syntax error thats causing the problem?
Here is the SQL, i've posted a rough table schema below too. (please note i'm using ColdFusion to query the database, so the #tstiid# is a variable passed to the query)
SELECT ((iss.iid, pg.pgid, pg.pg_name FROM issues iss LEFT JOIN issuespages ip ON ip.iid = iss.iid) RIGHT JOIN pages pg ON pg.pgid = ip.pgid WHERE iss.iid = #tstiid#
Database layout:
Table 1, named: issues
iid | issuename | a ton of other fields ------------------------ 1 | April 2006 | 2 | May 2006 |
table 2: pages
pgid | pagename | pagecontent | etc ------------------------------------- 1 | Contact us | To contact us, use the following... 2 | News | Upcoming news...
I have been running a query to find out the name and addresses of some of our customers but some data keeps coming in to the results that i dont want to see.
The data is in the Name column so I have built a second table and put the names of the items i do not wish to see in the second table using a left outer join and selecting null or not null. This works great if i know all the combinations of the name such as smith i.e d smith david smith, sam smith dd smith etc.
I would like to enter Smith into the table and have my query return everything except with the word smith in.
I can code this into the query with not like *smith* but that means everytime i want to go into it i would have to enter the query not the front end
I have a qry I need for picking the info for sending a series of letters out. I finally got it to work and now Im wondering if there is a better more efficient way. The qry I call to generate my report calls on information from 10 other queries and 7 tables. Some of the other queries are needed for other sub-forms that rely on the data. Is this something I should change? or just be happy it works??? -Dan :D
I am working on a schema that might need outer joins for some of the queries and thereby reports I will need to produce. So, I wanted to get an idea from the pros about the following strategy:
How about leaving all the joins in the Relationships to begin with as Inner Join and change them to Outer Join as required (for a given Query. I believe this is possible)? What are the pros and cons of this approach.
Thanks for all help so far, this forum is great ;)
I have 3 tables: Order, Product and Names.
I want to list All orders, no matter if they hold a key to a product or not, and no matter if this product has been given a name (in the table name) or not.
To make it even more complicated, the customer can give upp to 16 namesuggestions, they are all numbered from 1-16. But If they are given a namesuggestion, i only want to present the first one (nr 1).
Relations: 1 order -> 0 or 1 product. 1 order -> 0 or up to 16 names, of which i only want to display nr 1.
Any suggestions? I am thinking of preparing the data in a temporary table, but would be glad if i did not need to do so....
I'm the dummy in question. I'm new to Access and have a fairly simple database that I'm using to find potential customers. I am targeting them by two criteria using two different queries (both queries are looking at different fields of the same table). I want to create a combined query that pulls ALL of the results from both of my target queries. Searching around this forum I see that I need to create an Outer Join which Access doesn't exactly do. I also see that there are ways to simulate this, but I can't seem to figure out those ways. If someone could please explain this to me in a very simple way, I would be very grateful. Thank you.
I have a table and a query. The first 4 fields of the table correspond to that of the query. The query does not have any other fields, but the table has 26 more fields. Is it possible to use SQL OUTER JOIN or UNION or whatever to append the data of the query to the table or do I have to go with recordsets of VBA?
I have query , which has got 2 outer joins. The query is:
SELECT A.Project_ID, A.Title, A.comm1 AS Comments, A.Partner AS PM, A.Staff_Assigned AS TL, A.Contact_Name AS FL, A.MD, A.Status, A.Project_Type, ISNULL(B.Delivered_Date, B.Delivery_Date) AS Start_Date, ISNULL(C.Delivered_Date, C.Delivery_Date) AS End_Date FROM dbo.PROJECT A LEFT OUTER JOIN dbo.PROJDATE B ON A.Project_ID = B.Project_ID AND B.Date_Type = "Start Date" LEFT OUTER JOIN dbo.PROJDATE C ON A.Project_ID = C.Project_ID AND C.Date_Type = "End Date"
Can anybody help me out the error with this. I get an error : at dbo.PROJDATE B ON A.Project_ID = B.Project_ID AND B.Date_Type = "Start Date" .
Do I need to use any parantheris or change anthing.
I have 2 tables (tblItemsQB and tblItemInfo) that have a 1 to 1 relationship linked by the "PartNumber" field. The tblItemsQB table contains a description field and 2 price fields (this table is downloaded from Quickbooks). The tblItemInfo table contains additional fields with information on the part that isn't contained in Quickbooks, like OrderType, Phase, SubSystem.
I import the items from QB into tblItemsQB and then do an outer join with tblItemInfo to link all the information for the part. If I have created a new part in Quickbooks, when I do the outer join (I include all records from tblItemsQB) I can see the new part but since the part number doesn't exist yet in tblItemInfo, I can't add the information to the fields in that table.
What is the easiest way to create the new PartNumbers (those that exist in tblItemsQb but not in tblItemInfo) in the tblItemInfo table so I can add the other information to it?
