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

Minggu, 30 Oktober 2011

RE: [MS_AccessPros] UNION query "Too Complex"

They are analysis of pages counted from a website. All from the same table.

Each query counts pages visited in a given folder.

Two examples:

SELECT qStatsYTD.StatsID, qStatsYTD.Page, qStatsYTD.Visitors
FROM qStatsYTD
WHERE (((qStatsYTD.Page) Like "*.au/clubs/atoc/Albury/*"));

SELECT qStatsYTD.StatsID, qStatsYTD.Page, qStatsYTD.Visitors
FROM qStatsYTD
WHERE (((qStatsYTD.Page) Like "*.au/clubs/atoc/AlburyHume/*"));

Robin

At 31/10/2011 09:27 AM, you wrote:

>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
> >
> >
> >
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>