Query Too Complex on Joining Two Sub Queries Which Both Run Fine Individually

[CarlRostron]

I am receiving a Query too complex error.

I have two sub queries developed using the Query Wizard. In one Subquery I get 9 columns with 28 records, and the other 9 columns and 30 records. Here are the two queries:

Named: qryBestPerformingSpreads_Week9PricesBulls

SELECT qryBullishStocksWeek9.[Date Added], qryBullishStocksWeek9.[Week 9 Date], qryBullishStocksWeek9.SymbolName AS [Long], qryBullishStocksWeek9.SymbolID AS [Long ID], tblData.AdjClose AS [Long Week 9 Close], qryBullishStocksWeek9.LongSymbolPriceWhenAdded, qryBullishStocksWeek9.SymbolCode, qryBestPerformingSpreads_Week8PricesBulls.[Long Week 8 Close], qryBestPerformingSpreads_Week8PricesBulls.[Week 8 Date]
FROM (qryBullishStocksWeek9 INNER JOIN tblData ON (qryBullishStocksWeek9.SymbolID = tblData.SymbolID) AND (qryBullishStocksWeek9.[Week 9 Date] = tblData.ADate)) INNER JOIN qryBestPerformingSpreads_Week8PricesBulls ON qryBullishStocksWeek9.LongSymbolPriceWhenAdded = qryBestPerformingSpreads_Week8PricesBulls.LongSymbolPriceWhenAdded;

Named: qryBestPerformingSpreads_Week9PricesBears

SELECT qryBearishStocksWeek9.[Date Added], qryBearishStocksWeek9.[Week 9 Date], qryBearishStocksWeek9.SymbolName AS [Short], qryBearishStocksWeek9.SymbolID AS [Short ID], tblData.AdjClose AS [Short Week 9 Close], qryBestPerformingSpreads_Week8PricesBears.[Week 8 Date], qryBearishStocksWeek9.ShortSymbolPriceWhenAdded, qryBearishStocksWeek9.SymbolCode, qryBestPerformingSpreads_Week8PricesBears.[Short Week 8 Close] AS [Short Week 8 Close]
FROM (qryBearishStocksWeek9 INNER JOIN tblData ON (qryBearishStocksWeek9.SymbolID = tblData.SymbolID) AND (qryBearishStocksWeek9.[Week 9 Date] = tblData.ADate)) INNER JOIN qryBestPerformingSpreads_Week8PricesBears ON qryBearishStocksWeek9.ShortSymbolPriceWhenAdded = qryBestPerformingSpreads_Week8PricesBears.ShortSymbolPriceWhenAdded;

Note that these two queries refer to other Query result sets. I have run these two independently and both run fine individually, and return the results as expected.

They both contain a [Date Added] field in the output, and I now want to combine these two result sets joined on [Date Added].

I now join these two sub queries together in its own query but this is when I get the "Query too complex" error.

Considering that the two sub queries don't return that much of a large result set, and Access can Calculate these individually with no problem, I don't see why it cannot join these two result sets together.

SELECT qryBestPerformingSpreads_Week9PricesBears.SymbolCode AS [Short Ticker], qryBestPerformingSpreads_Week9PricesBulls.SymbolCode AS [Long Ticker], qryBestPerformingSpreads_Week9PricesBears.[Week 9 Date], qryBestPerformingSpreads_Week9PricesBears.Short, qryBestPerformingSpreads_Week9PricesBulls.Long, qryBestPerformingSpreads_Week9PricesBears.[Short Week 9 Close], qryBestPerformingSpreads_Week9PricesBulls.[Long Week 9 Close], Format((([Long Week 9 Close]/[Short Week 9 Close])-([Long Week 8 Close]/[Short Week 8 Close]))/([Long Week 8 Close]/[Short Week 8 Close])*100,"Fixed") AS [% Change], Format([Long Week 8 Close]/[Short Week 8 Close],"Fixed") AS [Start Ratio], Format([Long Week 9 Close]/[Short Week 9 Close],"Fixed") AS [End Ratio], qryBestPerformingSpreads_Week9PricesBears.[Short Week 8 Close], qryBestPerformingSpreads_Week9PricesBulls.[Long Week 8 Close], qryBestPerformingSpreads_Week9PricesBears.[Week 8 Date]
FROM qryBestPerformingSpreads_Week9PricesBears INNER JOIN qryBestPerformingSpreads_Week9PricesBulls ON qryBestPerformingSpreads_Week9PricesBears.[Date Added] = qryBestPerformingSpreads_Week9PricesBulls.[Date Added];

For the record, I have tried removing all the calculated expressions and just done a simple join on the two result sets and outputting a single attribute but the same error exists.

Please help (I can post anything you need to help me with this)

 

[CJ_London]

due to the long length of your query names, your code is very difficult to decipher. However if one query is referring to the other query this is almost certainly the root of your problem. A bit of a chicken and egg situation

I would look at rewriting your 'combined' query as one rather than trying to combine the two. Alternatively you could try saving each query in separate temporary tables and then combine those.