SQL Query Question

[MX1]

Here's a type of query I'm always having trouble with. There are 4 fields
in a web access log table.

User | Date | Time | Destination

I want to show all the instances of a user hitting a particular web site,
but I only want a single entry to represent that visit (not the 10 or 15
that represent every graphic that was obtained from the page as is typical
with imported web logs).

Here is a sample entry:

JackX 10/31/03 2:24:03 PM www.website.com/default.asp
JackX 10/31/03 2:24:03 PM www.website.com/images/image1.gif
JackX 10/31/03 2:24:03 PM www.website.com/images/image2.gif
JackX 10/31/03 2:27:05 PM www.website.com/default.asp
JackX 10/31/03 2:27:05 PM www.website.com/image1.gif

In my query, I'd like it to only pull the first and fifth record.
Essentially, pulling all sites with "*website.com*" and the computer name
"JackX" but I want it to only pull one entry from the 2:24:03 block and one
entry from the 2:27:05 block. How can I fitler a distinct row from the
time? Any help would be great! :)

[Phobos]

If it's just the *.gif files that are causing the trouble, filter them out
by only including *.asp (or *.htm) files:
SELECT User, Date, Time, Destination
FROM MyTable
WHERE Right(Destination, 4) = ".asp" OR Right(Destination, 4) = ".htm"
P
"MX1" <mx*@mx1.abc> wrote in message
news:Bg7rb.106710$ao4.321619@attbi_s51...

Here's a type of query I'm always having trouble with. There are 4 fields
in a web access log table.

User | Date | Time | Destination

I want to show all the instances of a user hitting a particular web site,
but I only want a single entry to represent that visit (not the 10 or 15
that represent every graphic that was obtained from the page as is typical
with imported web logs).

Here is a sample entry:

JackX 10/31/03 2:24:03 PM www.website.com/default.asp
JackX 10/31/03 2:24:03 PM www.website.com/images/image1.gif
JackX 10/31/03 2:24:03 PM www.website.com/images/image2.gif
JackX 10/31/03 2:27:05 PM www.website.com/default.asp
JackX 10/31/03 2:27:05 PM www.website.com/image1.gif

In my query, I'd like it to only pull the first and fifth record.
Essentially, pulling all sites with "*website.com*" and the computer name
"JackX" but I want it to only pull one entry from the 2:24:03 block and one entry from the 2:27:05 block. How can I fitler a distinct row from the
time? Any help would be great! :)

[Mike MacSween]

SELECT WebHits.User, WebHits.Time, First(WebHits.Date),
First(WebHits.Destination)
FROM WebHits
GROUP BY WebHits.User, WebHits.Time;

Assuming that you're not actually bothered which 'hit' you record. You don't
mention the date. You'd probably want to group on that too, although it is
fairly unlikely that someone would hit a particular web site at the same
time, to the second, on different days.

Yours, Mike MacSween

"MX1" <mx*@mx1.abc> wrote in message
news:Bg7rb.106710$ao4.321619@attbi_s51...

Here's a type of query I'm always having trouble with. There are 4 fields
in a web access log table.

User | Date | Time | Destination

I want to show all the instances of a user hitting a particular web site,
but I only want a single entry to represent that visit (not the 10 or 15
that represent every graphic that was obtained from the page as is typical
with imported web logs).

Here is a sample entry:

JackX 10/31/03 2:24:03 PM www.website.com/default.asp
JackX 10/31/03 2:24:03 PM www.website.com/images/image1.gif
JackX 10/31/03 2:24:03 PM www.website.com/images/image2.gif
JackX 10/31/03 2:27:05 PM www.website.com/default.asp
JackX 10/31/03 2:27:05 PM www.website.com/image1.gif

In my query, I'd like it to only pull the first and fifth record.
Essentially, pulling all sites with "*website.com*" and the computer name
"JackX" but I want it to only pull one entry from the 2:24:03 block and one entry from the 2:27:05 block. How can I fitler a distinct row from the
time? Any help would be great! :)

[John Mishefske]

MX1 wrote:

Here's a type of query I'm always having trouble with. There are 4 fields
in a web access log table.

User | Date | Time | Destination

I want to show all the instances of a user hitting a particular web site,
but I only want a single entry to represent that visit (not the 10 or 15
that represent every graphic that was obtained from the page as is typical
with imported web logs).

Here is a sample entry:

JackX 10/31/03 2:24:03 PM www.website.com/default.asp
JackX 10/31/03 2:24:03 PM www.website.com/images/image1.gif
JackX 10/31/03 2:24:03 PM www.website.com/images/image2.gif
JackX 10/31/03 2:27:05 PM www.website.com/default.asp
JackX 10/31/03 2:27:05 PM www.website.com/image1.gif

In my query, I'd like it to only pull the first and fifth record.
Essentially, pulling all sites with "*website.com*" and the computer name
"JackX" but I want it to only pull one entry from the 2:24:03 block and one
entry from the 2:27:05 block. How can I fitler a distinct row from the
time? Any help would be great! :)

Create a new field in your query to use just the hours and minutes from the 3rd field
and the Date:

AccessTime: Format([Date], "yyyymmdd")) & Format([Time, "hhnn")

and Group on it. This will group by the minute. It's not a perfect solution as the
loading of a page could overlap a minute boundary. You could also add the Left()
portion of the Destination field to limit to a particular site (host).

Left([Destination], InStr([Destination], "/") - 1)

--
'-------------------------------
' John Mishefske
'-------------------------------