Hi there
I have an audit table which sequentially logs all activity against a
device. A read operation is made up of a batch of (usually) three audit
records thusly:
Record 1 - Open
Record 2 - Read position xxx
Record 3 - Close
Similiarly a write operation is made up a batch of three audit records:
Record 1 - Open
Record 2 - Write position xxx
Record 3 - Close
There an be up to two reads/writes in the same audit batch but only
count as 1 for reporting purposes.
There are a number of other operation types which generate the same or
a similiar audit record batch group
Sometimes however the process will cancel before read or write so that
I have two batch records
Record 1 - Open
Record 2 - Close
These are the ones I am really interested in tracking in a summary
report i.e. for any date how many cancels were there. Nothing directly
indicates a cancellation, it can only be implied because there is no
audit record between the open and close audit records. Therefore I plan
to add a new field to the audit table and set its value to what kind of
batch all records belonging to the batch are i.e Read-Open
Read, Read-Close, Cancel-Open, Cancel-Close etc. That way if want a
count of all cancelled operation I just get a count of the Cancel-Open
records (see sample data below)
So what I want to do is a add a new column to the table which indicates
for the audit batch which operation type it is (for summary reports).
So some code to rip through the recordset and look at each line - no
problem. However the problem is that looking at currentrecord is no
enough to determine what type of batch it is e.g in a Cancel operation
the only way is start at the start and if currentrec.operation="open"
and currentrec+1.operation = "close" then it is a cancel batch etc etc.
1. Can does be done with a query (more of an academic question really
since I plan to write a sub to do this each night)
2. There are a lot of records each night - so I want to write an
efficient routine - what is the smartest way to code this?
If anybody has made it this far thanks a lot for your time and thanks a
lot in advance for any help that anyone can offer
Barry
Actual Sample Data (LogID is an autonumber so the records can be
sequenced in the order the events occurred) )
NEWFIELD however is an the field I wish to add with examples of the
values I wish to insert.
LogDate LogTime Client Operation
Node LogID NEWFIELD
23-Feb-06 12:13:09 $00774 OpenSession A05
33308 READ-OPEN
23-Feb-06 12:15:11 $00774 READ.:002307,Pos.:235 A05 33309 READ
23-Feb-06 12:15:16 $00774 Close Session A05 33310
READ-CLOSE
23-Feb-06 09:20:30 $01350 OpenSession A05 33304
READ-OPEN
23-Feb-06 09:20:52 $01350 READ:002409,Pos.:85 A05 33305 READ
23-Feb-06 09:21:11 $01350 READ:002472,Pos.:151 A05 33306 READ
23-Feb-06 09:21:26 $01350 Close Session A05 33307
READ-CLOSE
23-Feb-06 16:16:48 $00566 OpenSession A05 33317
WRITE-OPEN
23-Feb-06 16:17:10 $00566 WRITE:002146,Pos.:81 A05 33318 WRITE
23-Feb-06 16:17:26 $00566 Close Session A05 33319
WRITE CLOSE
23-Feb-06 18:16:22 $00558 OpenSession A05 33329
CANCEL-OPEN
23-Feb-06 18:22:34 $00558 Close Session A05 33330
CANCEL-CLOSE
23-Feb-06 09:20:30 $01350 OpenSession A05 33304
23-Feb-06 09:20:52 $01350 Return:002409,Pos.:85 A05 33305
23-Feb-06 09:21:11 $01350 Return:002472,Pos.:151 A05 33306
23-Feb-06 09:21:26 $01350 Close Session A05 33307
