Results 1 to 5 of 5
  1. #1
    servo75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    3

    I'm having a bizarre error in a query but ONLY when I outer join it

    Hello Access Colleagues and hope your Holidays are going well?



    I hope one of y'all can provide some insight here because I'm completely flummoxed. I've pasted two queries below (fields, etc. removed for simplification and privacy)
    The only difference is that the first (working0 one uses an inner join and the second (non-working) one uses a left join.

    SELECT *
    FROM Query1 INNER JOIN Query2 ON Query1.CustomerID = Query2.CustomerID
    WHERE Query2.WorkOrderID = [forms]![frmHiddenCriteria]![txtCustomerID]

    SELECT *
    FROM Query1 LEFT JOIN Query2 ON Query1.CustomerID = Query2.CustomerID
    WHERE (((Query2.WorkOrderID)=[forms]![frmHiddenCriteria]![txtCustomerID]))


    The first query works but doesn't give the desired result. The second query gives me an ODBC--Call error. I thought maybe the issue is that I'm using two queries to form a third query which I know is not good practice, but with this table design, doing this through tables only will require a LOT of tables and I'm going to get that dreaded "ambiguous outer join" error. I swear I've used two small queries to join to a larger query many times before and not had this happen.

    So the bottom line... Why would I be getting an ODBC call error or not only dependent on the type of join? This makes no sense to me.

    This is on an Access front end to a SQL Server database if it helps.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    I'm not sure without testing it, but with current syntax all field of both queries are displayed - Query1.CustomerID and Query2.CustomerID, which have same name CustomerID, included. This is generally a no-no, but maybe INNER JOIN is less sensitive about this.

    Try
    Code:
    
    
    Code:
    SELECT (the unique list of fields from q1 and/or q2, preceeded with alias - like q1.field1, q2.field2, etc.)
    FROM Query1 q1 LEFT JOIN Query2 q2 ON q1.CustomerID = q2.CustomerID
    WHERE (((q2.WorkOrderID)=[forms]![frmHiddenCriteria]![txtCustomerID]))
    


  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    why do you have this?

    WHERE (((Query2.WorkOrderID)=[forms]![frmHiddenCriteria]![txtCustomerID]))

    comparing a workOrderID to a customeriD?

  4. #4
    servo75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    3
    Quote Originally Posted by Ajax View Post
    why do you have this?

    WHERE (((Query2.WorkOrderID)=[forms]![frmHiddenCriteria]![txtCustomerID]))

    comparing a workOrderID to a customeriD?
    That is very interesting. To be honest, I removed some parts of the query for simplification, but if that was part of the original I'll take a look at it, thanks for noticing!

  5. #5
    servo75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    3
    Sorry folks, in my attempt to not paste such a long query in here, I may have oversimplified it:

    SELECT Q1.WorkOrderID, [Q2].CustomerName, [Q1].Address, Q1.WorkOrderStatus, Q1.Building, Q1.UnitNumber, Q2.Phone, Q2.Cell, Q2.Email, Q2.Notes, Q2.EntryPermission, Q1.DateRequested, Q1.Service
    FROM Q2 LEFT JOIN Q1 ON Q2.CustomerID = Q1.CustomerID
    WHERE (Q1.WorkOrderID) = [forms]![frmHiddenCriteria]![txtWorkOrderID]);

    Again, change "LEFT JOIN" to "INNER JOIN" and all is well. I thought it might be because I'm joining two queries (because a query won't have a primary key) but I don't think that's ever led to a problem before. I tried replacing the customer query with the customer table but then I had to include so many other tables that I got that dreaded "ambiguous outer join" message. I'm not aware of any specific issue with joining two queries, I swear I've done it many times before.

    @Ajax Good catch on the workorderid = customerid, but turns out that wasn't the issue. Darnit I thought that would be it

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ambiguous Outer Join Error
    By Topflite66 in forum Queries
    Replies: 11
    Last Post: 03-09-2018, 03:19 PM
  2. Help creating outer join query
    By jobrien4 in forum Queries
    Replies: 5
    Last Post: 09-12-2011, 01:56 PM
  3. Union query (or Inner/Outer join?)
    By LilMissAttack in forum Queries
    Replies: 4
    Last Post: 10-23-2010, 12:36 AM
  4. Ambiguous Outer Join Error
    By scruiks in forum Queries
    Replies: 3
    Last Post: 07-18-2010, 05:06 PM
  5. Dynamic Query Outer Join Problem
    By mjack003 in forum Queries
    Replies: 0
    Last Post: 07-21-2006, 01:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums