UNION ALL query works fine - but "too complex" for a report's record source?

[qqq]

UNION ALL query works fine - but "too complex" for a report's record source?

Hi there,

have been using the following SQL as record source for a report while still in Access 2003. Since moving to Access 2010 the report won't accept the SQL as a record source anymore - even though the SQL works as a stand along query: "Query is too complex".

I figured out that Access doesn't like the "ALL" part. Omitting ALL eliminates the problem. But creates another one for me.

SELECT eurnet AS EurOut, 0 AS EurIn FROM tblaccount WHERE isout AND project=245
UNION ALL SELECT 0 AS EurOut, csng(nz(eurnet,0)) AS EurIn FROM tblaccount WHERE not isout AND project=245
UNION ALL SELECT csng(nz(flowout,0)), csng(nz(flowin,0)) FROM tblaccountmisc WHERE 1 AND project=245;

How come I can't use a UNION ALL query as record source? Thanks for any clues!

Marcus

Running Access 2010 on Windows 7, mdb front-end with a MySQL back-end connected via ODBC connector.

 

[Simon_MT]

Re: UNION ALL query works fine - but "too complex" for a report's record source?

Within tlbaccount EurOut is always Zero it is only when not isout EurIn is enumerated.

What is the relationship between tblaccountmisc and tblaccount

Simon

 

[qqq]

Re: UNION ALL query works fine - but "too complex" for a report's record source?

Simon, am not sure I'm getting your first question right.

tblaccount and tblaccountmis are similar in structure (some fields identical), however tblaccount is an export of our accounting system (overwritten once per week), while tblaccountmisc holds manually added records.

Does this help? I think the problem is about the UNION ALL bit...

 

[MSAccessRookie]

Re: UNION ALL query works fine - but "too complex" for a report's record source?

Simon, am not sure I'm getting your first question right.

tblaccount and tblaccountmis are similar in structure (some fields identical), however tblaccount is an export of our accounting system (overwritten once per week), while tblaccountmisc holds manually added records.

Does this help? I think the problem is about the UNION ALL bit...

The third section of your Query is different from the frist two. Make it the same and try again. I have rewritten your Query to emphasize the differences between the sections.

[COLOR=red][B]------------  Section 1  ------------[/B][/COLOR]
 
[COLOR=black][FONT=Verdana]SELECT eurnet AS EurOut, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   0 AS EurIn [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]FROM tblaccount [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]WHERE isout AND project=245 [/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black][COLOR=red][B]------------  Section 2  ------------[/B][/COLOR][/COLOR][/FONT][COLOR=black]
 
[/COLOR][COLOR=black][FONT=Verdana]UNION[/FONT][/COLOR][COLOR=black][FONT=Verdana] ALL SELECT 0 AS EurOut, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   csng(nz(eurnet,0)) AS EurIn [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]FROM tblaccount [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]WHERE not isout AND project=245 [/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black][COLOR=red][B]------------  Section 3  ------------[/B][/COLOR][/COLOR][/FONT][COLOR=black]
 
[/COLOR][COLOR=black][FONT=Verdana]UNION ALL SELECT csng(nz(flowout,0))[COLOR=red][B] AS EurOut[/B][/COLOR], [B][COLOR=red]{Missing}[/COLOR][/B][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   csng(nz(flowin,0))[COLOR=red][B] AS EurIn[/B][B][COLOR=red]{Missing}[/COLOR][/B][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]FROM tblaccountmisc [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]WHERE [COLOR=red][B]1[/B][/COLOR] AND project=245;  [B][COLOR=red]{This will get ALL records?}[/COLOR][/B][/FONT][/COLOR]