Partial match relationship help

[twinturbo]

Hi All, I am working on a database which reads call records from a pabx and splits them with associated costs to separate people.

I have one table callrecords which holds all the records, the only thing I need from there is the duration and destination number. To determine the charge for the call I need to lookup the call rate from my rates table which defines the prefix for the number and the rate to charge for the prefix.

My problem is that I want to setup a relationship to do this lookup of the call rate, but, different countries have different prefixes, for example US is 1, but Finland is 358, Germany/Dusseldorf is 49211, how on earth can I match the call rate records in my rates table with prefixes from the callrecords table when the number of characters varies from prefix to prefix ?

Any suggestions would be greatly appreciated, I have been looking for some answers for this for some time. I have considered a calculated field for the relationship, but I will still have the problem of never really knowing the prefix which has been used for each call record.

Regards

Paul

[comment]

how on earth can I match the call rate records in my rates table with prefixes from the callrecords table when the number of characters varies from prefix to prefix ?

Can YOU tell, by looking at the number, what the prefix is? If so, how?

[twinturbo]

Well the only way yo tell is by looking at the actual phone number, for example a number for the US might be 1875557849 so the prefix is 1, but another number might be 6489024573, but in this case the first 2 numbers might be for the generic country code (I think NZ), but there could also be another match which might be 6489 for a NZ mobile (all fictitious of course -) so you see I can't actually know whether the prefix is 1 digit or 6, but it can be anything in between I know that.

Regards

Paul

Edited September 18, 2010 by Guest

[comment]

Well, then you should start by looking up the country/area by matching the first 6 digits of the destination number to the codes in the Rates table. If that fails, try the first 5 digits, and so on.

You can do all this at once, by "exploding" the destination number and sorting the related rates by their lengths. This will lookup the longest matching code.

[twinturbo]

This was the only way I could think of also, although I couldn't really get my head around how to do it, considering I will have thousands of call records per month to manage I was concerned about efficiency.

If I could be sure how many numbers always followed the area code it would certainly be easier, but I'm not.

Regards

Paul

[comment]

If you use a custom function or a repeating calculation field (or even hard-coded calc with 6 Left() statements) to give you:

1

12

123

1234

12345

123456

out of "123456789...", I believe it won't be much slower than a regular lookup. And I don't see that you have too many choices anyway.

[twinturbo]

Thanks for your help, I'll have a go at getting that working and see how it goes.

Regards

Paul