How can I accomplish the equivalent of a lookup table?

[Vulfson]

Hello everyone.

Total n00b here, trying to accomplish what should be very easy.

I am running a query which looks through two different, yet related tables. In each table is an ID number for a company. I am trying to get the name for the related company (which is stored in a separate table) to show up on the query.

The query won't work, because "the SQL statement could not be executed because it contains ambiguous outer joins." I am assuming that it means that the company name/ID table is connected to both of the two first tables, and it is confused as to which one to connect to.

How do I fix this? The help message tells me to create a separate query first, then join it to a second query. I looked at the help message, and was a bit dumbfounded by the SQL there.

Can anyone dumb it down enough for me? Please?

 

[KenHigg]

Click on the line that connects the two tables and edit the relationship, setting it up as a one to many...

???
ken

 

[KenHigg]

Vulfson, I think I read your thread incorrectly. It sounds as though you are using the common fld name in an expression or some other reference and just need to fully qualify the name. Example: If you use something like the following to create a fld in a query:

Name: [fname] & " " & [lname]

You may want to try:

Name: [EmployeeTable]![fname] & " " & [EmployeeTable]![lname]

???
kh

 

[Vulfson]

Thanks for the replies. I've got a relatively limited version of the same query posted below, that gives me the same error. The real query also includes more details from each table, but no attributes related to any other table.

SELECT Action.[Action ID], Action.[Document Number], Company.CompanyName, Submittal.[Submittal ID], Submittal.Number, Company.CompanyName
FROM (Company RIGHT JOIN [Action] ON Company.CompanyID = Action.CompanyID) RIGHT JOIN Submittal ON (Submittal.[Submittal ID] = Action.[Submittal ID]) AND (Company.CompanyID = Submittal.CompanyID);

The goal here is a simple query that returns information from both the action and submittal tables. Information in each table is tied to a specific company. Both the action and submittal table are related to the company table to look up company information.

The query worked before, when all the company information was simply another attribute or two in each of the action and submittal tables. I've been tasked with breaking apart the company information into its own table, while maintaining functionality of the earlier model.

I really appreciate any help you guys are willing to give.

 

[Vulfson]

Thank you EVER so much. This solved the problem on the first try.

Thanks again!