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

Senin, 31 Oktober 2011

RE: [MS_AccessPros] UNION query "Too Complex"

Robin,
I would create a table with fields like:

tblWebFolders
==================
FolderName text for values like au/clubs/atoc/albury...
FolderTitle text for values like "Albury"

Then create a query with qStatsYTD and tblWebFolders. Set the criteria under Page to:
Like "*" & Foldername & "*"

Add the FolderTitle to the query grid so each record from the qStatsYTD has a FolderTitle that you can group by and count.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: robinski@mymail.net.au
Date: Tue, 1 Nov 2011 09:37:01 +1100
Subject: RE: [MS_AccessPros] UNION query "Too Complex"

Duane,

Thanks for your help. As you know I am not a 'guru' and I have failed
to understand your suggections.

How do I "" store values of folder names like "au/clubs/atoc/Albury/"
with a title field in another table "" ?

When I understand that I will progress to the next step.

Regards,

Robin

At 31/10/2011 10:48 AM, you wrote:

>Robin,
>That's what I was afraid of. I'm not sure why you break the table
>apart and then attempt to put it back together again.
>
>You could just store values of folder names like
>"au/clubs/atoc/Albury/" with a title field in another table and
>build a totals query. Set the criteria under Page to
>Like "*" & [Your Folder Name Field] & "*"
>
>Group by [Your folder Title field].
>
>This solution uses one extra table of data that puts maintenance
>where it belongs (in your table, not in code or expressions). You
>can add, remove, or edit records as you modify your website pages.
>
>Don't work so hard. Life is too short.
>
>Duane Hookom
>MS Access MVP
>
>
>
>
>
>To: MS_Access_Professionals@yahoogroups.com
>From: robinski@mymail.net.au
>Date: Mon, 31 Oct 2011 09:58:45 +1100
>Subject: 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
> >
> >
> >
>
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>