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

Robin-

If the characters just before the "name" you want to extract are always
different, then my technique won't work. I was assuming that "au/clubs/atoc/"
was always the identifying string just ahead of the club name per your one query
example. It's clear now that you are custom building each query depending on
the name you are looking for! Follow Duane's advice.

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: Monday, October 31, 2011 11:32 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] UNION query "Too Complex"

John,

Thanks for the suggestion. As you know I am not 'guru'. Before I
start on the project please look at a sample of the imported data
because I see the function counts characters and the number of
characters in front of the preamble differs vastly. I cannot change this:

StatsYTD
Page Hits
The Rotary Foundation (http://www.rotary9790.org.au/4a/comm/trf.asp) 695
http://www.rotary9790.org.au/dbs/db8/members/membersbyclubsrch.asp 1375
Rotary Club of Diamond Creek Home Page
(http://www.rotary9790.org.au/clubs/dtok/diamondcreek/) 697
http://www.rotary9790.org.au/dbs/members/ 1132
http://www.rotary9790.org.au/clubs/atoc/coburg/market.asp 646

Regards,

Robin

At 31/10/2011 05:07 PM, you wrote:
>Or just write a function:
>
>Public Function FindGroupName(strURL As String) As String
>Dim intI As Integer, strWork As String
>
> ' Find the start of the search string:
> intI = InStr(strURL, ".au/clubs/atoc/")
> ' Strip off the front end
> strWork = Mid(strURL, intI + 15)
> ' find the ending "/"
> intI = InStr(strWork, "/")
> ' Return the name
> FindGroupName = Left(strWork, intI - 1)
>End Function
>
>Now write your query:
>
>SELECT FindGroupName(qStatsYTD.Page) As ClubName, Sum(qStatsYTD.Visitors)
>FROM qStatsYTD
>GROUP BY FindGroupName(qStatsYTD.Page)
>
>
>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 Duane Hookom
>Sent: Monday, October 31, 2011 12:48 AM
>To: Access Professionals Yahoo Group
>Subject: RE: [MS_AccessPros] UNION query "Too Complex"
>
>
>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
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

------------------------------------

Yahoo! Groups Links