Access left outer join error 4104

Question

I have written a very simply query similar to many before but this does not work. It is based on 2 existing queries:

Query 1 - a list of all items of certain types

Query 2 - a list of when some of those items were checked

each item has a unique ID, and when I create a query to list which items have not been checked I get the error. I can load both queries into new one - link them using the unique ID and view a list of all items in both lists. but if I right click join properties and ask it to show all from query 1 and only those from query two i get:

the multi part identifier could not be bound #4104

there is lots of SQL guidance on typing the wrong identifiers, not connecting tot he right table etc - but this is access, basically drag and drop, and it works fine until I change the link to a left outer join.

Can anyone point me in the right direction of a solution. Thank you.

Answer 1

You still should be able to look at the source tables and at the SQL in your query and see if there is a problem in naming, or aliasing. If so, you can change your own SQL I would expect.

Answer 2

thanks. I have no control over the data as I am connecting to a third party SQL database for reporting so even if I find the issue I would not be able to fix it, short of copying tables and amending, Something has changed as far as I can tell though as previously these queries did not fail. Thanks for responding but I think this may be something I am simply stuck with and have to work around

Answer 3

On the surface, nothing jumps out. What else can we explore about the situation then?

Does the error message not specify which field is not be identified? I would expect it to report something like:

#4104

the multi part identifier Q1.MissingField could not be bound

or something like that. I.e. it should name the problem field. It often indicates a problem with an aliased table, for example.

Also, I'm thinking the error message looks more like it would come from SQL Server. That's where I've seen it at any rate.

Answer 4

Hi, thanks for coming back to me. I think the question I am really asking is has access been changed/update really. I have never had an issue with these query types before, and it works perfectly with a normal join. I also tested it by creating a table of the data for one of the queries instead and again the query worked perfectly.

multipart bound errors normally relate to issues with he joins or access tot he fields - but as this is drag and drop and I have edited nothing it make no sense to me that it can fail. Especially when the table works fine with the same data.

query one pull out all the items of a type

SELECT feature.id, feature.type

FROM feature

WHERE (((feature.type)='001'

query two finds any items on the lists to be checked

SELECT feature.id, inspection_route.type

FROM

(insp_route_feat INNER JOIN inspection_route

ON insp_route_feat.insp_route_code = inspection_route.insp_route_code)

INNER JOIN feature

ON (insp_route_feat.plot_number = feature.plot_number) AND (insp_route_feat.site_code = feature.site_code)

WHERE (((inspection_route.type)="12"));

the final query looks for anything in query 1 that is not in query 2 - or at least should

SELECT Q1.id, Q2.id, Q2.insp_route_code

FROM Q1 LEFT JOIN Q2 ON Q1.id = Q2.id;

If I run it with an inner join to find all matched records as below it runs fine and shows me each item and the route it is checked on

SELECT Q1.id, Q2.id, Q2.insp_route_code

FROM Q1 INNER JOIN Q2 ON Q1.id = Q2.id;

I have had to anonymise the sql due to the data but this is the SQL I am using - again I have not edited this - just used access to drop in the tables and right click the join to change the properties - hence why I find it so baffling.

thanks

Answer 5

We can't see the Access accdb, nor any of these queries, so it's hard to guess what is involved in them. Perhaps, though, you could SHOW us the SQL from both of the base queries, "Query 1" and "Query 2" as well as the intended third query. That way, someone might be able to analyze and offer suggestions.