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.
Hi, I have more 10 years in MS Access and SQL Server and realy guru in this.
I can help you with this query. Please send me example of your database.
Alex
Hi. I work with File Maker Pro which is more powerful and easier to work with than Access, nevertheless I can design a file in FMP that will send the info to Access for you to import. If you want a sample of it, let me know. And regarding your explanation of what you want to do, it is almost all clear but I would need more details. Best regards
Hi there,
I am a database professional with 12 + years experience in application development, database design and implementation and am very keen to deliver you the application within 3 days.
My favorite datbase environment is MS access very user friendlyand easy to write programm code.
I want really do this job.
Regards