Project Detail

Description

Hi we are looking for somebody to create a MS ACCESS databse.

What we want it for.

To import CDR (caller detail records) from our PBX to create invoices for clients, we are not looking for an automatic system, a manual system where we import the csv into ACCESS would be fine.
Our csv has a field where the data in that field is unique to each customer9this could be used as customer Account Number)

Each CSV has all different customers on as this is a general CDR produced by the system (please see attached CSV file) where VoIP_ID field is Customer Account No.

However we would need a few tables with relationships building between them
There is a field called result, the data in this field reads ANSWERED, NO REPLY, BUSY, NO ANSWER.
Only the fields with ANSWERED need to be imported.
Customer Accounts - Products(Rates)- Invoices-



There are 10 fields in each csv to import, however we only wish to use 4 fields VoIP_ID Destination Time Duration

In the Access DB fields the above fields would be

VoIP_ID = Customer Account No
Destination = Products
Time = Time & Date
Duration = Duration (quantity)


from the destination field we would have a number

e.g 78844443335 when refering to products we only need to look at the first number

so 7 would be mobile (one rate)
numbers beginning with 84 (8452269959) would be Non-Geographic we would need to look at the first two numbers 84
this is Non-geo local rate
87 (8708652358) would be Non-geographic National rate

So destination numbers would be products, as we have general rates we dont need to build a rates table as products but if it can be done that would be fine..

For product reference the 'destination number only needs to refer to the first 3 numbers in that field.

Destinations-Products

084 Non-geographic Local rate
087 Non Geographic National rate
01 Local rate 1
02 Local rate 2
03 Local rate 3
080 Freecall
07 Mobile rate1
447 Mobile rate2

The output for invoice should read the fields as (not the data enclosed in brackets)

Customer Acc No (VoIP_ID)
Destination (product)
Time & Date (time)
Cost ( made from product costs against destination numbers.

--------------------------------------
Hope this is not confusing,,, i have attached a test csv to look at with reference to all the above





Additional files submitted:
test-cdr-csv.xlsx