Comparing calls statistics (1 Viewer)

[powellk]

Hi,

I need to compare call data between our network carrier and the calls over the same period on our call logger.

Obviously, I can't provide too much information.

So our "network carrier" will provide the attached fields. And our call logging device will provide the attached "call detail"





Basically, I need to compare one against the other. My initial comparison would be the date/time, however, my network carrier only provides HH:MM and my call detail will provide HH:MM:SS, but this may not be the main compare field. The dialled digits will be the same. So I guess I'm looking for something like.

1. Compare Date/Time (with time variants)
2. If Date matches, and if time is within say 59 seconds.
3. AND the dialled digits match then these calls will be a match.

I'm pretty new on these forums, and also with Access, but happy to learn.

I realise there is no specific information, I may be able to provide some test data if requested. But I thought it would be good to get some ideas for the people that know best...

Many thanks in advance...

 

[powellk]

OK, So I suspect I'm not being specific enough.

Let me start again.

We have 2 tables
Table 1 contains what we are being invoiced for
Table 1

04/05/2011 15:09:59 4 08708896893 D UK NationalRat 0.0045 Special Services (nr)
04/05/2011 15:10:05 4 08708896893 D UK NationalRat 0.0045 Special Services (nr)
04/05/2011 15:10:30 4 08708896893 D UK NationalRat 0.0045 Special Services (nr)


And Table 2 are the calls we have made.
Table 2

04/05/2011 15:09:53 00:00:08 00:04 0.004 7200 08708896893 National NTS
04/05/2011 15:09:59 00:00:06 00:02 0.004 7200 08708896893 National NTS
04/05/2011 15:10:24 00:00:06 00:02 0.004 7200 08708896893 National NTS

Now the only common field between the 2 tables would be the "08708896893". Which I can compare no problem. However, I also want to ensure the date and time of the actual call can match up as best endoeavors. You can see the time difference between tables is around 6 seconds. So I need to run a compare within 6 seconds of the call time, and also the dialled digits match.

I'm generally fine running a comparison if all fields are the same but this one is slightly more difficult.

Can anyone help me with this?

Your help is much appreciated.

 

[Galaxiom]

The tables should be joined in a query on the phone number and a criteria added to determine if the the time is within six seconds. (This condition can also be expressed in a join but that would have to be entered as SQL since the Query Designer cannot make such a join.)

There are several way to compare the time. One is using the DateAdd function to calculate a time window by adding or subtracting six seconds from the time and comparing with the other field using the BETWEEN operator.

Another is to add or subtract 0.00007 to the date/time (this represents just over six seconds) and compare with the BETWEEN.

eg
somefield BETWEEN (anotherfield - 0.00007) AND (anotherfield + 0.00007)