Query too complex error

[kaledev]

I noticed a strange problem with a query I am trying to contruct. I have a Union query that runs perfectly fine...however if I made a second query that uses it, everything returns a "Query too complex error". So let me try to explain... The first query is below.

"Q01 Generate Data With Duplicates":

SELECT Format([dbo_OLDDATA].[Report Dt],"yyyy-mm-dd") as [Report Dt], [dbo_OLDDATA].[Report Tm], [dbo_OLDDATA].Controller, [dbo_OLDDATA].[Eq Init], [dbo_OLDDATA].[Equip Nr], [dbo_OLDDATA].[Dwell Start Dt], [dbo_OLDDATA].[Dwell Start Tm], [dbo_OLDDATA].[Dwell Start Event], [dbo_OLDDATA].[Dwell Days], [dbo_OLDDATA].Terminal, [dbo_OLDDATA].[Wb Origin], [dbo_OLDDATA].[Wb Destination], [dbo_OLDDATA].[Wb Route]
FROM [dbo_OLDDATA]
WHERE ((([dbo_OLDDATA].[Report Dt]) Between CDate([Forms]![Main]![txtReportStartDate]) And CDate([Forms]![Main]![txtReportEndDate]))) AND [Q01 Generate Data with Duplicates].[Controller] = [Controller];
UNION ALL SELECT [MISSED_DATES].[Missed date] AS [Report Dt], [dbo_OLDDATA].[Report Tm], [dbo_OLDDATA].Controller, [dbo_OLDDATA].[Eq Init], [dbo_OLDDATA].[Equip Nr], [dbo_OLDDATA].[Dwell Start Dt], [dbo_OLDDATA].[Dwell Start Tm], [dbo_OLDDATA].[Dwell Start Event], [dbo_OLDDATA].[Dwell Days], [dbo_OLDDATA].Terminal, [dbo_OLDDATA].[Wb Origin], [dbo_OLDDATA].[Wb Destination], [dbo_OLDDATA].[Wb Route]
FROM [MISSED_DATES] INNER JOIN [dbo_OLDDATA] ON [MISSED_DATES].[Duplicate From] = [dbo_OLDDATA].[Report Dt]
WHERE ((([MISSED_DATES].[Missed date]) Between CDate([Forms]![Main]![txtReportStartDate]) And CDate([Forms]![Main]![txtReportEndDate])));

The query above runs great....now if I create a second query as listed below. It returns the "too complex" error.

SELECT [Q01 Generate Data with Duplicates].[Report Dt]
FROM [Q01 Generate Data with Duplicates];

I have tried every field, and it returns the same error. The first SQL statement is pulling from a SQL Server DB, but like I said it works fine alone so I am not sure if that has anything to do with it. Any ideas? Thanks!

[Rawb]

http://support.microsoft.com/kb/125767

It looks like you're well under your limit of 16 Tables and 40 WHERE/HAVING clauses, so my guess is that you're running to the Stack Space issue.

Unfortunately, I don't know of any way to increase the stack space of an individual program in Windows. You can try increasing the amount of Virtual Memory used or closing some programs. . .

However, your best bet at this point is to try and rebuild a brand new Query that does what you want, instead of Querying off of your original Query

Does the following Query work for you? If so, I'd just use it:

SELECT Format([dbo_OLDDATA].[Report Dt],"yyyy-mm-dd") as [Report Dt]
FROM [dbo_OLDDATA]
WHERE ((([dbo_OLDDATA].[Report Dt]) Between CDate([Forms]![Main]![txtReportStartDate]) And CDate([Forms]![Main]![txtReportEndDate]))) AND [Q01 Generate Data with Duplicates].[Controller] = [Controller];
UNION ALL SELECT [MISSED_DATES].[Missed date] AS [Report Dt], [dbo_OLDDATA].[Report Tm], [dbo_OLDDATA].Controller, [dbo_OLDDATA].[Eq Init], [dbo_OLDDATA].[Equip Nr], [dbo_OLDDATA].[Dwell Start Dt], [dbo_OLDDATA].[Dwell Start Tm], [dbo_OLDDATA].[Dwell Start Event], [dbo_OLDDATA].[Dwell Days], [dbo_OLDDATA].Terminal, [dbo_OLDDATA].[Wb Origin], [dbo_OLDDATA].[Wb Destination], [dbo_OLDDATA].[Wb Route]
FROM [MISSED_DATES] INNER JOIN [dbo_OLDDATA] ON [MISSED_DATES].[Duplicate From] = [dbo_OLDDATA].[Report Dt]
WHERE ((([MISSED_DATES].[Missed date]) Between CDate([Forms]![Main]![txtReportStartDate]) And CDate([Forms]![Main]![txtReportEndDate])));

[NTC]

I'd experiment; not sure which straw is breaking the camel's back....

Format([dbo_OLDDATA].[Report Dt],"yyyy-mm-dd") as [Report Dt]

might temp remove that if feasible just as a sanity check.....

the double use of [Report Dt] makes me a little uneasy, I might change to:
Format([dbo_OLDDATA].[Report Dt],"yyyy-mm-dd") as [Report FDt]

though that might cascade into alot of stuff and don't mean to send you on a wild goose chase....

In another approach; your 2nd query is filtering the results of the 1st....can you reverse that and first filter your record set before the union? not sure....

Going ugly; have you tried a 2nd query that is a simple MakeTable of the first query's record set (or Append into a premade empty local table)....then you have a local table to proceed with for more queries. This idea will make a sql jockey puke however... am only throwing out ideas - obviously you've done alot of work so far and just trying to help.

[kaledev]

I've tried a few different solutions and nothing has worked. The problem is that I have essentially 3 queries filtering off of the original. So I cannot imagine how I would combine them all into one. I wasn't actually trying to return 1 field as I posted, the actual query is more complicated and when it didn't work I said...ok how about just one field, can you even do that?...and the answer is no it cannot.

The whole query is below

SELECT [Q01 Generate Data with Duplicates].[Report Dt], [Q01 Generate Data with Duplicates].Terminal, Count([Q01 Generate Data with Duplicates].[Eq Init]) AS [Units With Outbound Billing]
FROM [Q01 Generate Data with Duplicates]
WHERE ((([Q01 Generate Data with Duplicates].[Wb Origin]) Is Not Null) AND ((([Q01 Generate Data with Duplicates].[Wb Destination])<>[Terminal]) Is Not Null))
GROUP BY [Q01 Generate Data with Duplicates].[Report Dt], [Q01 Generate Data with Duplicates].Terminal;

Now interestingly enough....I tried to break up this query and the following partial query worked.

"Pre-Q02":

SELECT *
FROM [Q01 Generate Data with Duplicates]
WHERE ((([Q01 Generate Data with Duplicates].[Wb Origin]) Is Not Null) AND ((([Q01 Generate Data with Duplicates].[Wb Destination])<>[Terminal]) Is Not Null));

I then tried to create a query to filter that one the rest of the way

SELECT [Pre-Q02].[Report Dt], [Pre-Q02].Terminal, Count([Pre-Q02].[Eq Init]) AS [Units With Outbound Billing]
FROM [Pre-Q02]
GROUP BY [Pre-Q02].[Report Dt], [Pre-Q02].Terminal;

and...."too complex". I have no clue what to do. The problems with these queries only began when I started running them off of a ODBC linked DB, they actually work fine in a previous version of the database with the table inside.

Using a Make Table would probably not even work since I am pulling from a DB with over 3 million records...or if it did work it would take a long time. The Format isn't the issue, I actually added that to see if it would fix the problem since my dates are in a different format in SQL Server.

I suppose it could be something with memory, but that would just seem strange...my virtual memory is twice my RAM.

[weekend00]

upload the table please.

[Rawb]

Well, after reading up some more, it seems that "Query too complex" means nothing more than "Hey, I ran into a problem with this thing, but I don't know what the problem is!"

This means that the problem could be anything in the Query.

However, I do have a question about your Query's WHERE clause. You have a condition in there stating:

(([Q01 Generate Data with Duplicates].[Wb Destination])<>[Terminal]) Is Not Null

Shouldn't that be something like the following instead:

([Q01 Generate Data with Duplicates].[Wb Destination]<>[Terminal]) AND 
([Terminal] Is Not Null)

[weekend00]

I think
(([Q01 Generate Data with Duplicates].[Wb Destination])<>[Terminal]) Is Not Null

is all right, just different expression of same logic result.

[weekend00]

in the #1 post, in query "Q01 Generate Data With Duplicates", why does the name of query show in the SQL text, what is [Q01 Generate Data with Duplicates].[Controller] and what is [Controller]:

AND [Q01 Generate Data with Duplicates].[Controller] = [Controller]; UNION ALL SELECT

[kaledev]

Actually that looks like a typo, ive gone through so many different versions of the same query I probably posted an old one.

(dbo_OLDDATA.Controller)=[SelController]

[Controller] was just a parameter to drill down the data, it would be difficult to explain but there are around 9 or 10 "Controllers".

Strangely enough, my last query:

SELECT [T_Pre-Q02].[Report Dt], [T_Pre-Q02].Terminal, Count([T_Pre-Q02].[Eq Init]) AS [Units with Outbound Billing]
FROM [T_Pre-Q02]
GROUP BY [T_Pre-Q02].[Report Dt], [T_Pre-Q02].Terminal;

Works if I first append "Pre-Q02" query to a table...then run the query against the table instead of the "Pre-Q02" query. This isn't a huge deal since i've gone from around 3million records to 200 or so by this time, but definitely not ideal. I don't get the exact results I expect but I'm guessing thats user error from changing these so much...

I can post a sample table if you think it will help, i'll just have to make it much smaller, and like i said its run from an ODBC connection which seems to have an effect on it.

[kaledev]

As an update I just gave up and put the data from the first query into a table and went from there. I went around in circles for days and nothing worked.

For some reason I can make the most complicated query in the world as long as it is a select all; if I even think about selecting less than all fields it freaks out on me and calls it too complex...so no clue how to solve this problem...