ACCESS FEED: RE: [MS_AccessPros] UNION query "Too Complex"

Minggu, 30 Oktober 2011

RE: [MS_AccessPros] UNION query "Too Complex"

I wonder why there are 46 query/tables that are so similar they need to be union'd. Are they all similar queries from the same tables?

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: john@viescas.com
Date: Sun, 30 Oct 2011 22:26:24 +0100
Subject: RE: [MS_AccessPros] UNION query "Too Complex"

Robin-

You could try breaking it into three or four groups of UNION queries, then UNION
the UNIONs. If that fails, you'll have to break it into pieces and then use an
Append query to dump the results into a table that you can then feed to the
report.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Sunday, October 30, 2011 10:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] UNION query "Too Complex"

Thanks John,

I read the Microsoft Page on the subject.

That was unknown to me but I still get the error message.

The are 46 UNION statements.

Cheers,

Robin

At 30/10/2011 06:06 PM, you wrote:
>Robin-
>
>Yes, roughly 16 UNION statemetns. Try using UNION ALL to eliminate the
>unnecessary sort of the records.
>
>John Viescas, author
>Microsoft Office Access 2010 Inside Out
>Microsoft Office Access 2007 Inside Out
>Building Microsoft Access Applications
>Microsoft Office Access 2003 Inside Out
>SQL Queries for Mere Mortals
>http://www.viescas.com/
>(Paris, France)
>
>
>-----Original Message-----
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Sunday, October 30, 2011 1:22 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: [MS_AccessPros] UNION query "Too Complex"
>
>I am using Access 2007 to prepare a statistical report from visits to
>a web site.
>
>I have planned a report with counts of visitors per folder. The query
>that delivers the result is a UNION query and after I added the
>latest folder count an error message was generated that the query was
>"Too complex".
>
>Here is the first part of the SQL:
>
>SELECT "Bulletins" AS Visitors, TotalBulletins.SumOfVisitors
>FROM TotalBulletins;
>
>UNION SELECT "Conference Pages" AS Visitors, TotalConf.SumOfVisitors
>FROM TotalConf;
>
>UNION SELECT "Club Pages Combined" AS Visitors, TotalClub.SumOfVisitors
>FROM TotalClub;
>
>UNION SELECT "Clubs - Diamond Creek Pages" AS Visitors,
>TotalDiamondCreek.SumOfVisitors
>FROM TotalDiamondCreek;
>
>Is there an Access limit to how many I may use?
>
>Many thanks,
>
>Robin Chapple
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>