If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Website Database
I'm using a web database to track users in Access.
I have one large table where I store the times and details of which page was visted and by whom, etc. I also want to be able to aggregate data based on time either by a super sql statement or by some other mechanism that stores the aggregated data into another table. Let me demonstrate what I mean User 1 visits ten pages on day 1, which my db records. User 2 visits 8 times on day 1 which my db records. Day two: Visitors 2,3,4 and 5 visit. I query the database and I want to see that on day 1, I had 2 visitors and on day 2 I had 4 visitors. Now, my database current stores the visits as the requests come in and then I have to perform a hardcore sql statement to work out who is unique and who is visting pages within the same session. I hope you're still with me! How can I now either create a new table to record unique hits per day or how do I perform a superdooper sql statement to the same effect? Thanks very much if you got this far and understand what I mean - and can help! |
#2
|
|||
|
|||
Website Database
Dear nospam:
I believe the general answer woud be to create an aggregate query (also called totals query or GROUP BY) that is GROUPed BY user and date, and use: HAVING COUNT(*) = 1 If you would post more details of your table, I could craft the entire statement. I might be something like: SELECT User, AccessDate FROM YourTable GROUP BY User, AccessDate HAVING COUNT(*) = 1 Tom Ellison wrote in message ... I'm using a web database to track users in Access. I have one large table where I store the times and details of which page was visted and by whom, etc. I also want to be able to aggregate data based on time either by a super sql statement or by some other mechanism that stores the aggregated data into another table. Let me demonstrate what I mean User 1 visits ten pages on day 1, which my db records. User 2 visits 8 times on day 1 which my db records. Day two: Visitors 2,3,4 and 5 visit. I query the database and I want to see that on day 1, I had 2 visitors and on day 2 I had 4 visitors. Now, my database current stores the visits as the requests come in and then I have to perform a hardcore sql statement to work out who is unique and who is visting pages within the same session. I hope you're still with me! How can I now either create a new table to record unique hits per day or how do I perform a superdooper sql statement to the same effect? Thanks very much if you got this far and understand what I mean - and can help! |
#3
|
|||
|
|||
Website Database
Thanks so much for the help!
My two relevant tables a Table Users[UserId, RemoteAddr, RemoteHost, Start, End, Browser, X-Forward] Table Requests[RequestID, UserID, Time, URL, Method] It would be nice as this information will be accessed quite frequently if the aggregated info could be automatically inserted into a new table at the end of the 24 hour period - can Access do this? (I'm not very clued up on advanced features such as this in Access!). |
#4
|
|||
|
|||
Website Database
Dear nospam:
Applying the information you provided to my earlier query: SELECT UserId FROM Requests WHERE DateValue([Time]) = Date() GROUP BY UserId HAVING COUNT(*) = 1 You may substitute an actual date for the Date() function. I don't think you need the Users table for this, unless you want to see some other column from that table. If so, a simple inner join should suffice. If you want an automated insertion, I recommend you create a database application that does this as it's startup, then terminates. You can then just schedule it. Tom Ellison wrote in message ... Thanks so much for the help! My two relevant tables a Table Users[UserId, RemoteAddr, RemoteHost, Start, End, Browser, X-Forward] Table Requests[RequestID, UserID, Time, URL, Method] It would be nice as this information will be accessed quite frequently if the aggregated info could be automatically inserted into a new table at the end of the 24 hour period - can Access do this? (I'm not very clued up on advanced features such as this in Access!). |
#5
|
|||
|
|||
Website Database
Applying the information you provided to my earlier query:
SELECT UserId FROM Requests WHERE DateValue([Time]) = Date() GROUP BY UserId HAVING COUNT(*) = 1 You may substitute an actual date for the Date() function. Thanks for the help Tom - unfortunately applying this within my asp code to generate a print out of the latest unique users per day doesn't work in the way I thought it might : ( But I am very tired. I'll try it tomorrow now. Thanks again. |
#6
|
|||
|
|||
Website Database
Dear nospam:
Best I can tell, this is the first time you have mentioned ASP. Generally speaking, the built in and user defined functions of Access are not accessible when not working from within Access (this refers to Jet only). It is rare for me, anyway, to write any application that doesn't need such functions. So, I'll say something that may be controversial. Use MSDE instead of Jet. MSDE cannot reference any Access user functions either, but you can at least write equivalent function for it. It already has a rich set of functions built into it. You can use MSDE to reference your Jet tables, or you can put the data into MSDE itself. This is not a simple solution. You'll have to learn the MSDE functions. You may even choose to move all the tables to MSDE. In the long run, I don't think you're in a tenable position for application development. It is most unfortunate to get very far into a project without knowing this. It's easier to make decisions about an application's platform before launching the coding. There's also the time and effort spent learning the platform, only to learn you must learn another. Very sorry! Tom Ellison wrote in message ... Applying the information you provided to my earlier query: SELECT UserId FROM Requests WHERE DateValue([Time]) = Date() GROUP BY UserId HAVING COUNT(*) = 1 You may substitute an actual date for the Date() function. Thanks for the help Tom - unfortunately applying this within my asp code to generate a print out of the latest unique users per day doesn't work in the way I thought it might : ( But I am very tired. I'll try it tomorrow now. Thanks again. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help! Database mysteriously deleting | Mark | General Discussion | 10 | October 20th, 2009 04:03 PM |
Database - Insert not working | LaRue05 | General Discussion | 3 | August 29th, 2005 11:44 PM |
Best way to "re-do" security FAQ on database now in use? | D.B. | General Discussion | 1 | July 19th, 2005 03:14 AM |
Archiving A Database | PC User | General Discussion | 2 | November 2nd, 2004 11:16 PM |