Access database design + special query

• Project ID:

  785325

• Project Type:

  Fixed

• Budget:

  $30-$250 USD
  

Project Description:

I run a VOIP long distance phone company. I have raw data for different costs to dial destinations from multiple different providers. I can put all the data in a single table and need a query that will select the best rate for each destination along with all other important details (provider id, country name, etc, etc).

Here is the biggest dillema with the query. Some providers will give a single rate for an entire country. For example, provider 1 is $0.02 for UK (any dial prefix 44 will be $0.02). Other providers may give thousands of different rates for that same country. For example provider 2 will say: 442205 will be $0.01, 44242 will be $0.05 and 44 is $0.025 (When a single provider gives a rate for 44 and has a rate for 442205 as well, the 44 rate will ONLY be used if a longer prefix does not exist from the SAME provider). I need the query to determine the cheapest price for each prefix. In the above examples, we would keep 442205 prefix (in the query result) as it is cheaper ($0.01) than the general 44 ($0.02) from provider 1. However 44242 would not be kept because provider 1 is $0.02 for every destination starting with 44.


I hope I was able to explain this job to the fullest. I am willing to provide additional examples and aid to anyone who feels they can do this.

It is good for this to be done is Microsoft Access so I can easily import updated rates when I get them and also will be able to export the query results in CSV so I can import them to my billing software.

Skills required:

Data Processing, Microsoft Access, SQL