Error 3296: Join expression not supported in MS Access Query with multiple JOIN

Question

I'm using Access 2003 and doing a make table query.

The idea is, I have multiple tables containing all kinds of info on a list of funds, and I am trying to combined them. All tables have FundID as one of the fields, and this is the field we use to identify each individual fund. There's a table for a field MER, which are updated from time to time. So we have to apply the MER values for its applicable periods. Here's my SQL code:

SELECT 
[Fund Mapping].FundID,
[Fund Mapping].FundName,
[MarketValues].DateLookup,
[MarketValues].[SumOfCurrentAmt]/100 AS [MV By Fund],
[Fund Prices @ Month End].Price,

IIf([MarketValues].DateLookup<"200908",[MER (08 to Jul 09)].MER,
  IIf([MarketValues].DateLookup<"200911",[MER (Aug 09 to Oct 09)].MER,
    IIf([MarketValues].DateLookup<"201008",[MER (Nov 09 to Jul 10)].MER,
      IIf([MarketValues].DateLookup<"201106",[MER (Aug 10 to May 11)].MER,[MER (Jun 11 to present)].MER)
    )
  )
) AS MER

INTO [Fund Data]
FROM [Fund Mapping], 

[MER (08 to Jul 09)] RIGHT JOIN 
  ([MER (Aug 09 to Oct 09)] RIGHT JOIN 
    ([MER (Nov 09 to Jul 10)] RIGHT JOIN 
      ([MER (Aug 10 to May 11)] RIGHT JOIN
        ([MER (Jun 11 to present)] RIGHT JOIN 
          ([MarketValues] INNER JOIN [Fund Prices @ Month End] 
            ON ([MarketValues].DateLookup = [Fund Prices @ Month End].DateLookup)
            AND ([MarketValues].FundID = [Fund Prices @ Month End].[Fund ID])) 
        ON [MER (Jun 11 to present)].FundID = [MarketValues].FundID)
      ON [MER (Aug 10 to May 11)].FundID = [MarketValues].FundID) 
    ON [MER (Nov 09 to Jul 10)].FundID = [MarketValues].FundID) 
  ON [MER (Aug 09 to Oct 09)].FundID = [MarketValues].FundID) 
ON [MER (08 to Jul 09)].FundID = [MarketValues].FundID

GROUP BY 
[Fund Mapping].FundID, 
[Fund Mapping].FundName, 
[MarketValues ].DateLookup, 
[SumOfCurrentAmt]/100, 
[Fund Prices @ Month End].Price, 

IIf([MarketValues].DateLookup<"200908",[MER (08 to Jul 09)].MER,
  IIf([MarketValues].DateLookup<"200911",[MER (Aug 09 to Oct 09)].MER,
    IIf([MarketValues].DateLookup<"201008",[MER (Nov 09 to Jul 10)].MER,
      IIf([MarketValues].DateLookup<"201106",[MER (Aug 10 to May 11)].MER,[MER (Jun 11 to present)].MER)
    )
  )
)

ORDER BY 
[Fund Mapping].FundID, 
[MarketValues].DateLookup;

DateLookup are strings with the format of YYYYMM. The reason why I am using RIGHT JOIN instead of INNER JOIN is because there can be new funds added, so the earlier MER tables will not have all the FundIDs. The "MarketValues INNER JOIN Fund Prices @ Month End" table should be the base of this whole thing - it has all the funds, and nothing should be dropped from this.

When I tried to save the query, it gave me an error message saying JOIN expression not supported. I don't know what's wrong or how I can fix it.

Help please? Thanks in advance!

========== UPDATE #1

I manually added each of the join properties and the entire query from scratch in design view. It gave me pretty much the same code as above, but allowed me to save (the code above gives me an error whenever I try to save).

But when I tried to run the query, it gives me an error message that says: "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL Statement."

Is there no other way around this?

========== UPDATE #2

So I digged a bit further, and was able to resolve the problem and have the query run properly.

Turns out all I had to do was to JOIN [Fund Mapping] table together with all the other tables! Ie, all the tables I am selecting from are joined together.

Just thought I should give an update in case anyone else encounters the same problem.

Thank you for helping out!

Answer 1

See Update #2 for solution to this problem.

Answer 2

I think the problem is mixing 'old style' cross join with infix join style e.g. consider this simplified example that generates the same error:

SELECT *
 FROM T1, T2 RIGHT JOIN T3 ON T2.c = T3.c;

One simple fix to the above is to perform the RIGHT JOIN in another scope e.g. in a derived:

SELECT * 
  FROM T1, (
            SELECT T2.*, T3.c2
              FROM T2 RIGHT JOIN T3 ON T2.c = T3.c
           ) AS DT1;