Retrieving data about most frequently visited page
Hi!
I have a database (MS Access) filled with data about web pages that were visited. How can I get the data about what page was most frequently visited - if possible without checking the number of visits on each page and than comparing those numbers.
I'm a trifle lost. Your access database contains a record of hits logged on various .html pages, or your access database is functioning as a content repository with rows containing the html for individual 'pages' which is pulled in by some ASP?
And you want to know which number out of a group of numbers is highest, without comparing those numbers?? Umm, that's going to be awfully tricky.
PageName Date Time
================================
page1.asp ... ...
home.htm ... ...
back.htm ... ...
page1.asp ... ...
In this example page named "page1.asp" was visited most frequenty. I was just wandering if there is a function I coud use to get the most frequent vaue in specific column.
Just a thought:
do you realy need a record for each hit? can't you get that from a logfile and analyse it with some log-analyse-tool?
I always use one page-table where i store the pages title, description, keywords, css, creationtime, last udatetime and the number of hits. If a page is requested, i select the title etc to build the html-header and increment the countervariable. Then it's easy to instantly get the hits/page.
the select group by might take up some resources if you have some trafic over time (10 pages x 100 visitors a day x 100 days = 100k records ...)
It could of course be analysed with log-analyser, but my boss doesn't want to do it that way - especialy because traffic analysis must include per-registered-user analysis.
Besides, the MDB file will be always downloaded to a local machine for analysis.
if i were your boss, i would open access, go to querys, open new, hit querywizard and then choos crossab-wizard. You can there define what you need in the column-headers (pages?) and what you need in the rows (users) and what agregation you want in the fields (hits) and presto !
it's a jet-sql specific query that is used (something like transform + group by select + pivot). you could run through the wizard and afterwards look at the generated sql and use that inside your ASP-page (or simply use the access query as such.
Originally posted by M@rco So what's the problem with Roelf's post? It's the textbook answer to your question!
It may be the perfect sollution for un unnecesary problem.
If you would frequently run this sort of query online, with a 'group by page' or ' group by page, user' on a intensely used site, then it would cause an unnescecary performance drop. Not the query as such, but the tablelocking which would prevent new inserts from being processed (aka requested pages to be fully processed and responded to).
If you run it offline, then there are better alternatives like generating the crosstab, which decimates you code to proces the returned recordset.
It's also reinventing the wheel and making your server doing double work since the logfiles can give you the same info (you only need to keep a session-table with 1 record with the IP number - UserID per session) Using the logs will be safer + will save you an insert in the page-table for each pagerequest.
I suppose that the more experienced you are, the more sollutions you know to each problem, but the less problems you have to solve because you've learned to effectively use all build in features.
I do not agree with the "reinventing the whell..." - why would I (or our customers for whom the solution is intended) use (buy) specialized software for analyzing web logs is all we need for our purpose is to retrieve the name of most frequenty used page.
Especially becuase specialized software cannot operate with the retrieved value and use it in the functions our information already has implemented...
Before you post, read our: 
