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
>
>
>
Tidak ada komentar:
Posting Komentar