Website Database

[Guest]

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!

 

[Tom Ellison]

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

 

[Guest]

Thanks so much for the help!

My two relevant tables are:

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!).

 

[Tom Ellison]

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

 

[Guest]

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.

 

[Tom Ellison]

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

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.