Comparing successive records in large access file - VB help

[JasonP]

I am using Access 2003 on Windows XP. I am looking to analyse web traffic files using this - I appreciate there are bespoke applications which will do the same task. Each month there are roughly 500,000 records. What I want to do is compare successive records, to calculate new fields at a visitor and visit level. Such as page number this visit, page number all visits by this visitor, time spent on current page, etc.

My table (shown below) only has 3 columns, although I've manually added the other 3 for illustration purposes in case I wasn't making myself clear.

ID        HitDate      HitTime     VisitorPageNo VisitNo VisitPageNo
10004-055052   03/02/2007   17:50:52   1             1       1
10011-172249   03/02/2007   17:22:48   1             1       1
10011-172249   03/02/2007   17:23:02   2             1       2
10011-172249   03/02/2007   19:23:28   3             2       1
10011-172249   03/02/2007   19:24:21   4             2       2
10019-093023   03/02/2007   09:30:23   1             1       1
10023-215744   03/02/2007   21:57:44   1             1       1
10023-215744   03/02/2007   21:57:49   2             1       2
10023-215744   03/02/2007   21:58:30   3             1       3
10033-202533   03/02/2007   19:25:32   1             1       1
10033-202533   03/02/2007   20:25:45   2             2       1
10033-202533   03/02/2007   20:26:00   3             2       2
10033-202533   03/02/2007   21:26:09   4             3       1
10033-202533   03/02/2007   22:31:40   5             4       1

I have written a series of queries (8 at the last count) which calculate most of the fields I require. I'm sure I could include a couple more to get the job completed. It runs reasonably quickly, but it just seems a long winded and inelegant way of solving the problem.

I'm sure the same result could be achieved in a simpler and more efficient way using VB. I have experience of Basic and other programming languages, but I'm a novice at VB. I've searched on recordsets and so on, but have not been able to find something similar I can adapt.

I've attempted to write some logical instructions that should solve the problem. I don't know if this is of any help!

dim x as integer
dim VisitPageNo, VisitNo, VisitPageNo, TimeOnPage as recordset
 
x = 1
VisitorPageNo(x) = 1
VisitNo(x) = 1
VisitPageNo(x) = 1
 
While not EOF
x = x+1
  If ID(x) = ID(x-1) then
    If HitDate&HitTime(x) < HitDate&HitTime(x-1) + 1 hour then
      TimeOnPage(x-1) = HitDate&HitTime(x) - HitDate&HitTime(x-1)
      VisitorPageNo(x) = VisitorPageNo(x-1) + 1
      VisitNo(x) = VisitNo(x-1)
      VisitPageNo(x) = VisitPageNo(x-1) + 1
    Else
      VisitorPageNo(x) = VisitorPageNo(x-1) + 1
      VisitNo(x) = VisitNo(x-1) + 1
      VisitPageNo(x) = 1
    End
  Else
    VisitorPageNo(x) = 1
    VisitNo(x) = 1
    VisitPageNo(x) = 1
  End if
Wend

Assistance in solving this problem would be greatly appreciated. Ideally I'd like a new query which adds the additional fields to the existing ones.

Thanks

[ADezii]

I am using Access 2003 on Windows XP. I am looking to analyse web traffic files using this - I appreciate there are bespoke applications which will do the same task. Each month there are roughly 500,000 records. What I want to do is compare successive records, to calculate new fields at a visitor and visit level. Such as page number this visit, page number all visits by this visitor, time spent on current page, etc.

My table (shown below) only has 3 columns, although I've manually added the other 3 for illustration purposes in case I wasn't making myself clear.

ID Hit Date HitTime VisitorPageNo Visit No VisitPageNo
10004-055052 03/02/2007 17:50:52 1 1 1
10011-172249 03/02/2007 17:22:48 1 1 1
10011-172249 03/02/2007 17:23:02 2 1 2
10011-172249 03/02/2007 19:23:28 3 2 1
10011-172249 03/02/2007 19:24:21 4 2 2
10019-093023 03/02/2007 09:30:23 1 1 1
10023-215744 03/02/2007 21:57:44 1 1 1
10023-215744 03/02/2007 21:57:49 2 1 2
10023-215744 03/02/2007 21:58:30 3 1 3
10033-202533 03/02/2007 19:25:32 1 1 1
10033-202533 03/02/2007 20:25:45 2 2 1
10033-202533 03/02/2007 20:26:00 3 2 2
10033-202533 03/02/2007 21:26:09 4 3 1
10033-202533 03/02/2007 22:31:40 5 4 1

I have written a series of queries (8 at the last count) which calculate most of the fields I require. I'm sure I could include a couple more to get the job completed. It runs reasonably quickly, but it just seems a long winded and inelegant way of solving the problem.

I'm sure the same result could be achieved in a simpler and more efficient way using VB. I have experience of Basic and other programming languages, but I'm a novice at VB. I've searched on recordsets and so on, but have not been able to find something similar I can adapt.

I've attempted to write some logical instructions that should solve the problem. I don't know if this is of any help!

dim x as integer
dim VisitPageNo, Visit No, VisitPageNo, Tomoenage as recordset

x = 1
VisitorPageNo(x) = 1
Visit(x) = 1
VisitPageNo(x) = 1

While not EOF
x = x+1
If ID(x) = ID(x-1) then
If Hit Date&HitTime(x) < HitDate&HitTime(x-1) + 1 hour then
Tomoenage(x-1) = Hit Date&HitTime(x) - HitDate&HitTime(x-1)
VisitorPageNo(x) = VisitorPageNo(x-1) + 1
Visit(x) = Visit(x-1)
VisitPageNo(x) = VisitPageNo(x-1) + 1
Else
VisitorPageNo(x) = VisitorPageNo(x-1) + 1
Visit(x) = Visit(x-1) + 1
VisitPageNo(x) = 1
End
Else
VisitorPageNo(x) = 1
Visit(x) = 1
VisitPageNo(x) = 1
End if
Wend

Assistance in solving this problem would be greatly appreciated. Ideally I'd like a new query which adds the additional fields to the existing ones.

Thanks

I was kind of pressed for time, but I think I solved 1/3 of your problem (VisitorPageNo) - the rest you probably can deduce from the code.

1. Create a Query, for now Query1.

2. Add [ID], [HitDate], and [HitTime] to the Query Grid.

3. The [ID] Field must be set to Ascending or Descending Sort (critical).

4. Create a Calculated Field called Visitor Page No.

5. This Calculated Field will call the fCalculateVisitorPageNo([ID]) Function and pass it the value of the [ID] Field as a Parameter. The Calculated Field will look like this:

Visitor Page No: fCalculateVisitorPageNo([ID])

The actual Function code is listed below:

Public Function fCalculateVisitorPageNo(WebID As String) As Integer
Dim intTotalNoOfPagesVisited As Integer
Static intPageNoVisited As Integer
 
intTotalNoOfPagesVisited = DCount("*", "Table1", "[ID] = '" & WebID & "'")
 
If intTotalNoOfPagesVisited = 1 Then
  'Reset the Page#
  intPageNoVisited = 0
  fCalculateVisitorPageNo = 1   'Only 1 Page
Else    '> 1 Page visited
  intPageNoVisited = intPageNoVisited + 1
  If intPageNoVisited >= intTotalNoOfPagesVisited Then   'Last Page visited
    fCalculateVisitorPageNo = intTotalNoOfPagesVisited
    intPageNoVisited = 0
  Else
    fCalculateVisitorPageNo = intPageNoVisited
  End If
End If
End Function

Typical Output would be:

ID                  Visitor Page No
10004-055052          1
10011-172249          1
10011-172249          2
10011-172249          3
10011-172249          4
10019-093023          1
10023-215744          1
10023-215744          2
10023-215744          3
10033-202533          1
10033-202533          2
10033-202533          3
10033-202533          4
10033-202533          5
10033-202539          1
10033-202539          2
10033-202539          3

The SQL of it would be:

SELECT Table1.ID, fCalculateVisitorPageNo([ID]) AS [Visitor Page No]
FROM Table1
ORDER BY Table1.ID DESC;

It probably can be done from a purely SQL point of view but I am not as proficient as some of the other Moderators/Experts in SQL. Maybe they'll provide a neater solution.

[MMcCarthy]

Try this ...

Create a new table (tblStats) to hold all fields:

Private Function updatetblStats()
Dim VisitorPageNo, PageNo, VisitPageNo as Integers
Dim tmpID As Long ' assumed datatype of the ID field
Dim tmpHitDate As Date ' assumed datatype of the HitDate field
Dim tmpHitTime As Date ' assumed datatype of the HitTime field
Dim tmpHitHour As Integer
Dim db as Database
Dim rs1 as Recordset
Dim rs2 as Recordset
 
  Set db = CurrentDB
  Set rs1 = db.OpenRecordset("SELECT ID, HitDate, HitTime, " & _
                    "Hour([HitTime]) As HitHour " & _
                    "FROM TableName " & _
                    "ORDER BY ID, HitDate, HitTime")
  Set rs1 = db.OpenRecordset("tblStats")
 
  VisitorPageNo = 1
  PageNo = 1
  VisitPageNo = 1
  tmpID = rs1!ID
  tmpHitDate = rs1!HitDate
  tmpHitTime = rs1!HitTime
  tmpHitHour = rs1!HitHour
 
  rs1.MoveFirst
  Do Until rs1.EOF
 
    rs2.AddNew
    rs2!ID = tmpID
    rs2!HitDate = tmpHitDate
    rs2!HitTime = tmpHitTime
    rs2!VisitorPageNo = VisitorPageNo
    rs2!PageNo = PageNo
    rs2!VisitPageNo = VisitPageNo 
    rs2.Update
 
    rs1.MoveNext
 
    If rs1!ID = tmpID Then
      VisitorPageNo = VisitorPageNo + 1
      If rs1!HitDate = tmpHitDate And If rs1!HitHour = tmpHitHour Then
        PageNo = PageNo
        VisitPageNo = VisitPageNo + 1
      Else 
        PageNo = PageNo +1
        VisitPageNo = VisitPageNo
        tmpHitDate = rs1!HitDate
        tmpHitHour = rs1!HitHour
      End If
    Else
      VisitorPageNo = 1
      PageNo = 1
      VisitPageNo = 1
      tmpID = rs1!ID
      tmpHitDate = rs1!HitDate
      tmpHitTime = rs1!HitTime
      tmpHitHour = rs1!HitHour
    EndIf
  Loop
 
  rs1.close
  rs2.Close
  Set rs1=Nothing
  Set rs2=Nothing
  Set db=Nothing
 
End Sub
 

Mary

[JasonP]

Hi ADezii

Thanks very much for your reply. This was the first time I've posted a question on here and I was shocked that 2 people responded so quickly. As the 2nd reply answered all of my questions - and meant less work for me to do - I have used that one. I hope I didn't take up too much of your time.

Thanks again

[JasonP]

Hi Mary

Thank you very much for taking the time to answer my question. Your program works wonderfully. You have saved me many hours of fumbling my way around VB.

Thanks again

Jason

[MMcCarthy]

You're welcome Jason. I don't normally post code to this extent but it was a puzzle of logic and it was easier to write it than explain it. Do spend some time trying to follow what I did though for your own sake and I'll be happy to answer any questions you have.

Thank you as well for reponding to all experts. Some posters forget to thank the experts other than the one who posts the accepted answer.

Glad it's working for you.

Mary