ambiguous outer joins - query but runs?

[belovedcej]

There is a query that has been in use for over a year, runs fine.

Recently, I made a copy of the db to make some edits. I added a single field from a table already there, gave it a criteria, saved and ran it. Got the message "ambiguous outer joins." Of course, I can't open the SQL view to edit it directly because of the same message.

So I went back to the DB in implementation and ran it - sure enough, it works. I tried to open the SQL view, but it said "ambiguous outer joins."

This happened once before, very recently. I tried making all joins outer, since sometimes Access is pickier than TSQL. No go. Eventually, I simply recreated the query exactly as it was and saved it and deleted the old. It worked fine with no nasty messages.

I'm going to do the same thing here - but my question is, has anyone encountered this? Why would it change when it hasn't been touched? Is it a corruption problem? How can I prevent this from occuring again? (There are only 3 tables. Table 1 has outer joins to table 2. Table 3 has inner joins to Table 2.)

 

[jrbarnett]

You need to change the table 2 - table 3 link to an outer join to fix this.

Why?

All the inner joins need to be done as early as possible in the query, so a possible way of doing this is to invert the selection, eg:

T3 IJ T2 OJ T1

This is because the query optimiser in Access can run the query in one of many ways, and having an inner join after an outer join means that there may be rows that could be shown if executed that way or not.

John

 

[belovedcej]

Sorry I wasn't more clear. I tried that already and it made no difference - I still get the message about ambiguous outer joins.

It worked before - for a year - the way it is. What I'm wondering is why it would break now?

If no one knows, I'll just assume it's a corruption thing and yet another issue I have with Access.

 

[belovedcej]

In fact, I deleted table 3 - so now I just have table 2 pointing to table 1, with outer joins. I saved and tried to look at the SQL or run it. I still get ambiguous outer joins! But if I rebuild the query from scratch, I don't get the error.

 

[belovedcej]

Here we go - I started a new query, rebuilt it exactly the same way (joins and everything), it runs, and there are no errors. Here is the way it is joined.

SELECT 	field1, field2, field3
FROM Table1 
LEFT JOIN Query1 
	ON Table1.ID = Query1.ID 
	AND Table1.County = Query1.County 
INNER JOIN Table 2 
	ON Table1.ID = Table2.ID) 
	AND Table1.County = Table2.County)

What I want to know is, why, if a query like this works, would it at some point give an error when I try to look at the SQL? And, once it does that, why would the query continue to work unless I add additional fields?