Time difference help needed!

[Monopot]

Hi all,

I've just started a new job and one of the things they have got me doing involves getting some extra reports out of some access databases they have for the phone system. I've managed OK so far but I'm stuck on regarding phone diverts.

Basic jist is, when reception is unmanned they put the phone on divert and when they come back they take it off again. Simple and this info is recorded in the log file which is pulled into access each night.

The problem is all of the information is kept in a single table which I run a query on to get the info to look like below. The info is exactly as it is in the table, just that I have limited it to the reception user and where the func is like *54* or *55*:-

Date Time User Func
03/05/2005 11:40:00 Reception *54*200#
03/05/2005 11:47:00 Reception *55*200#
04/05/2005 11:35:00 Reception *55*200#
04/05/2005 11:40:00 Reception *54*200#
04/05/2005 11:59:00 Reception *55*200#
05/05/2005 09:57:00 Reception *54*200#
05/05/2005 10:08:00 Reception *55*200#
05/05/2005 12:10:00 Reception *54*200#
05/05/2005 12:17:00 Reception *55*200#
05/05/2005 12:19:00 Reception *55*200#

The FUNC *54* means put on divert and *55* is off divert. What I need to figure out is the time between each of *54* and *55* and total them for all the data in the table (we have a table for each month). The problem I can see here is that occasionally the receptionist will put in the *55* first which doesn't mean anything so this bit would need to be ignored as only bits where its a *54* followed by a *55*.

Hope this is self explaintory and that someone is able to help. I have attached a CSV fle on the table so you can have a play with it if you can help.

Thanks in advance!

Regards,

Andy Roberts

 

[KenHigg]

Have you ever done any ADO?

 

[Monopot]

Have you ever done any ADO?

Nope sorry.

 

[KenHigg]

.
.
.
.
.
Dao?

 

[Monopot]

.
.
.
.
.
Dao?

Nope. Not done mega loads of vba type stuff in the past either. I can work my way through but I've not really had to do that must heavy stuff in the past so have always managed with basic queries etc.

 

[KenHigg]

ADO provides a means, with vba code, to step through a set of records, one record at a time look the values and edit flds, etc.

That's the only way I can see to do what you're needing to do... Of course you'll need to learn how and work through a couple ADO examples before what I would suggest would make any sense... And it's not really very hard once it clicks in your head what's happening....

 

[Monopot]

OK. What would you suggest I would need to do in this case then as what I'll do is grab a couple of ado examples and have a look how they work and try and work my way through thm, but having something to relate it to would help.

Thanks for your help so far.

 

[KenHigg]

1. Do a table to hold temp values for the report. Lets call just refer to this table as tbl2 and refer to the primary table as tbl2. Each record will hold the results, or time difference, between two records in the primary table.

2. Open both tables in ado.

3. Add a record in a tbl2

4. Save the first 54 value from tbl1 to a temp variable. Lets call it t54

5. Move to the next record in tbl1

6. Store the date difference between t54 an the 55 value in the current record from tbl1, into the new record in tbl2

7. Move to the next record in tbl1

8. Go back to step #3

There would be some other things happening, like cleaning up tbl2 after the report runs, etc...

Hope this makes a little sense(??)