By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,871 Members | 2,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,871 IT Pros & Developers. It's quick & easy.

Question: best way to solve this little problem?? (A bit long)

P: n/a
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

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
23-Feb-06 12:13:09 $00774 OpenSession A05
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

23-Feb-06 09:20:30 $01350 OpenSession A05 33304
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

23-Feb-06 16:16:48 $00566 OpenSession A05 33317
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

23-Feb-06 18:16:22 $00558 OpenSession A05 33329
23-Feb-06 18:22:34 $00558 Close Session A05 33330

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

Aug 14 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.