0

I've attached a picture showing the tables that I'm using in my query. When I try to run the query, I keep getting an ambiguous outer join error from Access, but I can't see why. enter image description here

Here is an explanation of what I'm trying to do:

I have a form with a tab control that shows several offices and a combobox to select a course that is offered by my company. Once the user selects a course and an office tab, it will populate a subform that does the following:

The subform has the following controls:
chkCompleted - Checkbox for tblIndividualLearning.Completed
txtDate - textbox for tblIndividualLearning.DateCompleted
txtName - Textbox for the employee's name tblEmployee.LastName & ", " & tblEmployee.FirstName

The subform should show ALL employees at the selected office with the above fields.

Here is the current query I have that shows most of the information:

SELECT tblEmployee.EmpID, tblEmployee.[LastName] & ", " & [FirstName] AS EmpName, tblIndividualLearning.DateCompleted, tblIndividualLearning.Completed, tblOffices.OfficeID
FROM tblOffices INNER JOIN (tblEmployee LEFT JOIN tblIndividualLearning ON tblEmployee.EmpID = tblIndividualLearning.EmpID) ON tblOffices.ID = tblEmployee.Office
WHERE (((tblOffices.OfficeID)=8405));

In the picture above, I forgot to add tblOffices.ID which is the primary key for that table.

Currently, this query shows me all employees from a certain office, but it doesn't tie to the user selected course. I'm not sure how to fit that in.

Thanks again for the help!

Scott
  • 37
  • 5
  • Edit the question and show the query that is generating the error. – Gordon Linoff Mar 30 '18 at 02:06
  • Like I said, I'm not at work right now, so I don't have access to it. I'm hoping people can look at the table structure and give me some ideas. If not, it'll have to wait until Sunday. – Scott Mar 30 '18 at 02:10
  • ^The query is similar to [Mainform tab control passing value to subform variable for select case](https://stackoverflow.com/questions/49563352/mainform-tab-control-passing-value-to-subform-variable-for-select-case)? Then have websearch for your questions title. That should lead you to a ms site witere ambigous joins are explained. If you show the query, we can explain it. – ComputerVersteher Mar 30 '18 at 05:09
  • based on the picture, search for any references to to Catalogid or Empid that are not prefixed by a table name. – Alan Mar 30 '18 at 05:19
  • You probably have an _inner join_ deeper than an _outer join_. – Gustav Mar 30 '18 at 09:50
  • @ComputerVersteher, VERY similar, but i'm adding the combobox to search for a specific course, as well as location.(I actually sorted that other one out yesterday) I'll put the query up on Sunday. I know I'm asking a blind question without it, but I had to post it while it was fresh in my mind. – Scott Mar 30 '18 at 19:40
  • Are the Master/Child links of subform synced with main form? – June7 Mar 30 '18 at 20:37
  • No master/Child links. The actual subform is going to work fine now that I figured that business out. This is all SQL issues. – Scott Mar 31 '18 at 00:55
  • Updated the question with my current SQL statement. – Scott Apr 02 '18 at 01:36

1 Answers1

0

Solved it... With the above SQL statement and added an "is null" criteria. So easy when you look at it! I also deleted the original join properties and redid the relationships within the query.

Thanks for all of the help everyone!

Scott
  • 37
  • 5