473,944 Members | 25,397 Online

# Using Access for crunching Phone bills

Trying to get my head round building a DB to analyse phone bills.

On the surface its fairly simple - duration in seconds x cost per minute/60.

The problem arises with working out what time of day a call was made because
calls are charged at different rates at different times. The problem with
this is working out when a call crosses time bands & part of it needs to be
rated at one rate & part at another & then the two halves added together.

Specifically there are three time bands & so three possible rates for any
call to a given destination

8am to 6pm weekdays is a Daytime rate
6pm to 8am is Evening rate
& Midnight on Friday night to Midnight on Sunday night is weekend rate

Within this 6pm on a Friday to Midnight would be rated as evening as would
Midnight on Sunday to 8am on Monday morning.

As an example, a call that started at 17:50 on a weekday and ended at 18:10
would have 10 minutes rated at Daytime and 10 minutes rated at evening.

In an extreme example (and they do happen for dial up data etc - I've got a
bill on hand at the moment with many calls over 5 hours long & one as long
as 47 hours (!)) a call that started at 17:30 on a Friday and ended at 00:30
on Saturday morning would have 30 minutes rated as Daytime, 6 hours rated as
Evening and 30 minutes rated as Weekend.

Generally I've got the start date and time of each call and the call
duration in seconds & so from that point I need to work out how many seconds
of each call fall into each time band.

Anyone have any ideas as to how I could use Access to figure this out using
queries? In many cases with 100,000 plus calls on a bill Excel etc is not an
option.

rgds

Iain
Nov 13 '05 #1
5 1923
What I would do is decompose the problem. First, make a copy of the new call
data to process into a new table. Next run a query that determines any call
that starts in one time period, and continues it into another, and splits it
into 2 records, one for the minutes in the starting period, and one starting
at the beginning of the subsequrnt period, having the remaining minutes.

Running this query again on the same data will split any of the continuations
that need to be split again, so if you run it enough times, the data will be
fully decomposed. To do this in code, just the .Execute method of the DAO
Database object to run the query, and check the .RecordsAffecte d count after
each run. When .RecordsAffecte d is zero, that means there were no more
records to split.

At this point, no single record contains minutes at more than one rate, so you
can simply check the starting date/time, to get the rate for all the minutes
in a record.

On Fri, 23 Jul 2004 17:09:37 GMT, "Iain Miller" <do***@spam.m e> wrote:
Trying to get my head round building a DB to analyse phone bills.

On the surface its fairly simple - duration in seconds x cost per minute/60.

The problem arises with working out what time of day a call was made because
calls are charged at different rates at different times. The problem with
this is working out when a call crosses time bands & part of it needs to be
rated at one rate & part at another & then the two halves added together.

Specifically there are three time bands & so three possible rates for any
call to a given destination

8am to 6pm weekdays is a Daytime rate
6pm to 8am is Evening rate
& Midnight on Friday night to Midnight on Sunday night is weekend rate

Within this 6pm on a Friday to Midnight would be rated as evening as would
Midnight on Sunday to 8am on Monday morning.

As an example, a call that started at 17:50 on a weekday and ended at 18:10
would have 10 minutes rated at Daytime and 10 minutes rated at evening.

In an extreme example (and they do happen for dial up data etc - I've got a
bill on hand at the moment with many calls over 5 hours long & one as long
as 47 hours (!)) a call that started at 17:30 on a Friday and ended at 00:30
on Saturday morning would have 30 minutes rated as Daytime, 6 hours rated as
Evening and 30 minutes rated as Weekend.

Generally I've got the start date and time of each call and the call
duration in seconds & so from that point I need to work out how many seconds
of each call fall into each time band.

Anyone have any ideas as to how I could use Access to figure this out using
queries? In many cases with 100,000 plus calls on a bill Excel etc is not an
option.

rgds

Iain

Nov 13 '05 #2
Have you tried getting your phone bill electronically from your
provider?

I built a database to analyse a phone companies electronic bill to match
the calls up to a call centre log so they knew which calls to bill which
client..... fortunately the bill came with rates/etc done for me:)

"Iain Miller" <do***@spam.m e> wrote in message
news:lD******** *******@newsfe2-gui.ntli.net
Trying to get my head round building a DB to analyse phone bills.

On the surface its fairly simple - duration in seconds x cost per
minute/60.

The problem arises with working out what time of day a call was made
because calls are charged at different rates at different times. The
problem with this is working out when a call crosses time bands &
part of it needs to be rated at one rate & part at another & then the
two halves added together.

Specifically there are three time bands & so three possible rates for
any call to a given destination

8am to 6pm weekdays is a Daytime rate
6pm to 8am is Evening rate
& Midnight on Friday night to Midnight on Sunday night is weekend rate

Within this 6pm on a Friday to Midnight would be rated as evening as
would Midnight on Sunday to 8am on Monday morning.

As an example, a call that started at 17:50 on a weekday and ended at
18:10 would have 10 minutes rated at Daytime and 10 minutes rated at
evening.

In an extreme example (and they do happen for dial up data etc - I've
got a bill on hand at the moment with many calls over 5 hours long &
one as long as 47 hours (!)) a call that started at 17:30 on a Friday
and ended at 00:30 on Saturday morning would have 30 minutes rated as
Daytime, 6 hours rated as Evening and 30 minutes rated as Weekend.

Generally I've got the start date and time of each call and the call
duration in seconds & so from that point I need to work out how many
seconds of each call fall into each time band.

Anyone have any ideas as to how I could use Access to figure this out
using queries? In many cases with 100,000 plus calls on a bill Excel
etc is not an option.

rgds

Iain

--
regards,

Bradley
Nov 13 '05 #3
Hi Iain,
Give this a go...

I have a table named "tblCallDuratio n" which just stores just my "StartTime"
and "EndTime" values.
A second table named "tblCallRat es" stores the "WkndRate", "EveRate", and
"DayRate" costs in a single record.

Then I have a form "frmCallDuratio n" which is bound to "tblCallDuratio n" so
that it displays the StartTime/EndTime for each call. There is also a
(pretty big) unbound textbox "txtCallSta ts" that is used to display the call
statistics.

Here is what the output looks like (using your example times):
*************** *************** ********
Record #1:
This call originated at: 7/23/2004 5:50:00 PM
and ended at: 7/23/2004 6:10:00 PM
The call length was: 20 minutes

Rate: DayTime
Minutes: 10 @ \$0.25 = \$2.50
---------------------

Rate: Evening
Minutes: 10 @ \$0.15 = \$1.50
---------------------

Record #2:
This call originated at: 7/23/2004 5:30:00 PM
and ended at: 7/24/2004 12:30:00 AM
The call length was: 420 minutes

Rate: DayTime
Minutes: 30 @ \$0.25 = \$7.50
---------------------

Rate: Evening
Minutes: 360 @ \$0.15 = \$54.00
---------------------

Rate: Weekend
Minutes: 30 @ \$0.10 = \$3.00
---------------------
*************** *************** ********
This is the code behind the form.
*************** *************** ****
Option Compare Database
Option Explicit

Dim varTime As Date
Dim varMins As Integer
Dim varRate As Currency
Dim VarCost As Currency
Dim strRate As String
Dim Msg As String
Dim CR As String
Dim varMyCheck

'--------------------------------------------------
Private Sub cmdGetCallStats _Click()
sGetTimesRates
End Sub

'--------------------------------------------------
Private Sub Form_Current()
sGetTimesRates
End Sub

'--------------------------------------------------
Public Sub sGetTimesRates( )

Dim dteStart As Date
Dim dteEnd As Date

Me.Dirty = False 'Save any changes

If Me.NewRecord Then
Me.txtCallStats = "" 'Clear the unbound TextBox
Exit Sub
End If

CR = vbCrLf

If Not IsDate(Me.Start Time) Or Not IsDate(Me.EndTi me) Then
Msg = ""
Msg = Msg & "Both a Start Time and End Time must be entered before
continuing!"
MsgBox (Msg)

Exit Sub
End If

dteStart = Me.StartTime
dteEnd = Me.EndTime
Msg = ""
Msg = Msg & "This call originated at: "
Msg = Msg & dteStart & CR
Msg = Msg & " and ended at: "
Msg = Msg & dteEnd & CR
Msg = Msg & " The call length was: "
Msg = Msg & DateDiff("n", dteStart, dteEnd)
Msg = Msg & " minutes" & CR & CR

varTime = dteStart 'Initialize to the start time of the call
varMins = 0
strRate = ""

Do While varTime < dteEnd
varMyCheck = WeekDay(varTime )
Select Case WeekDay(varTime )

Case 1 Or 7 'Sunday or Saturday -- Weekend Rate applies
Select Case strRate

Case "" 'This is the first minute of the call.
strRate = "Weekend"
varRate = DLookup("WkndRa te", "tblCallRat es")
varMins = 1
Case "Weekend"
'Do Nothing, except increment the minute counter
varMins = varMins + 1
Case Else 'This means that we have just entered the first
minute at this rate
'and it's time to tally up the charges for the
previous rate
sTallyRate
strRate = "Weekend"
varRate = DLookup("WkndRa te", "tblCallRat es")
varMins = 1
End Select

Case Else ' Monday to Friday
varMyCheck = Hour(varTime)

Select Case Hour(varTime)
Case 0 To 7, 18 To 23 '"Evening" hours
Select Case strRate
Case "" 'This is the first minute of the call.
strRate = "Evening"
varRate = DLookup("EveRat e", "tblCallRat es")
varMins = 1
Case "Evening"
'Do Nothing, except increment the minute counter
varMins = varMins + 1
Case Else 'This means that we have just entered the
first minute at this rate
'and it's time to tally up the charges for
the previous rate
sTallyRate
strRate = "Evening"
varRate = DLookup("EveRat e", "tblCallRat es")
varMins = 1
End Select

Case 8 To 17
Select Case strRate
Case "" 'This is the first minute of the call.
strRate = "DayTime"
varRate = DLookup("DayRat e", "tblCallRat es")
varMins = 1
Case "DayTime"
'Do Nothing, except increment the minute counter
varMins = varMins + 1
Case Else 'This means that we have just entered the
first minute at this rate
'and it's time to tally up the charges for
the previous rate
sTallyRate
strRate = "DayTime"
varRate = DLookup("DayRat e", "tblCallRat es")
varMins = 1
End Select
End Select
End Select

varTime = DateAdd("n", 1, varTime)

Loop

sTallyRate
Me.txtCallStats = Msg

End Sub

'--------------------------------------------------
Public Sub sTallyRate()
VarCost = 0
VarCost = (varMins * varRate)
CR = vbCrLf
Msg = Msg & "Rate: "
Msg = Msg & strRate & CR
Msg = Msg & "Minutes: "
Msg = Msg & varMins
Msg = Msg & " @ "
Msg = Msg & Format(varRate, "Currency")
Msg = Msg & " = " & Format(VarCost, "Currency") & CR
Msg = Msg & "---------------------" & CR & CR
Debug.Print Msg
End Sub
*************** *************** ****

--
HTH,
Don
=============== ==============
E-Mail (if you must)
My*****@Telus.n et

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==

"Iain Miller" <do***@spam.m e> wrote in message
news:lD******** *******@newsfe2-gui.ntli.net...
Trying to get my head round building a DB to analyse phone bills.

On the surface its fairly simple - duration in seconds x cost per minute/60.
The problem arises with working out what time of day a call was made because calls are charged at different rates at different times. The problem with
this is working out when a call crosses time bands & part of it needs to be rated at one rate & part at another & then the two halves added together.

Specifically there are three time bands & so three possible rates for any
call to a given destination

8am to 6pm weekdays is a Daytime rate
6pm to 8am is Evening rate
& Midnight on Friday night to Midnight on Sunday night is weekend rate

Within this 6pm on a Friday to Midnight would be rated as evening as would
Midnight on Sunday to 8am on Monday morning.

As an example, a call that started at 17:50 on a weekday and ended at 18:10 would have 10 minutes rated at Daytime and 10 minutes rated at evening.

In an extreme example (and they do happen for dial up data etc - I've got a bill on hand at the moment with many calls over 5 hours long & one as long
as 47 hours (!)) a call that started at 17:30 on a Friday and ended at 00:30 on Saturday morning would have 30 minutes rated as Daytime, 6 hours rated as Evening and 30 minutes rated as Weekend.

Generally I've got the start date and time of each call and the call
duration in seconds & so from that point I need to work out how many seconds of each call fall into each time band.

Anyone have any ideas as to how I could use Access to figure this out using queries? In many cases with 100,000 plus calls on a bill Excel etc is not an option.

rgds

Iain

Nov 13 '05 #4

"Don Leverton" <le************ ****@telusplane t.net> wrote in message
news:uCGMc.4942 6\$2i3.12856@clg rps12...
Hi Iain,
Give this a go...

<SNIP much useful stuff>

Don,

thanks very much for your help

rgds

Iain
Nov 13 '05 #5

"Bradle)y" <br***********@ comcenTHIS.com. au> wrote in message
news:41******** @news.melbourne .pipenetworks.c om...
Have you tried getting your phone bill electronically from your
provider?
Yep the bills I am looking at are in electronic format...
I built a database to analyse a phone companies electronic bill to match
the calls up to a call centre log so they knew which calls to bill which
client..... fortunately the bill came with rates/etc done for me:)

Yes, they all do that more or less

What I am trying to do is to compare bills using different carrier rates so
I need to take the start time & duration etc & then apply another set of
rates to end up with a comparison of what the final bills would be under 1,2
or more different carriers

thanks

Iain

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

### Similar topics

 4 4774 by: Skully Matjas | last post by: I am using the following code (created by the wizard) to allow to bring my form to a particular entery. But when I edit the entery (ex: put new information into a blank cell), it puts that record onto the bottom of the list (even though it keeps its record number). Also, There are certin names that i click on the list, and it will not bring it up, rather it brings to the first record (no matter how many times i try going to that... 5 2236 by: Gene | last post by: What can I do if I want to get the result using the sql command? for example, the select command is "select Name from Employee where StaffID=10" How to get the "Name"??? dim Name as string and then..... what should I do? 0 1112 by: Mitch | last post by: Hi everyone, apologies if this is not the right group for this posting, please feel free to send me elsewhere. I currently have a web service that receives very simple data (a comma delimited string). This is working nicely. The problem I am having is that the headers that encapsulate the data are so much larger than the data itself. Our clients will be sending data from remote areas using mobile phone type modems (not my area of... 24 14460 by: cassetti | last post by: Here's the issue: I have roughly 20 MS excel spreadsheets, each row contains a record. These records were hand entered by people in call centers. The problem is, there can and are duplicate phone numbers, and emails and addresses even person names. I need to sift through all this data (roughly 300,000+ records and use fuzzy logic to break it down, so that i have only unique records. 16 7224 by: Claudio Grondi | last post by: I have a 250 Gbyte file (occupies the whole hard drive space) and want to change only eight bytes in this file at a given offset of appr. 200 Gbyte (all other data in that file should remain unchanged). How can I do that in Python? Claudio Grondi 1 2304 by: nirmalsingh | last post by: hai all, i am using asp.net-C#. i want to know that is there is any way to control EPABX? using c#. if so kindly any provide me a sample code to do that. EPABX stands for Electronic Private Automatic Branch Exchange. it is used to control the telephone , intercom such us locking the telephone connection of a particular room if some one vacates a room in a hotel,to generate bills for number of calls made in that phone of a room , open the... 7 4089 by: Stephenoja | last post by: Hello Guys, I have a challenge here and would really appreciate some help. I have customer bills that are all done in excel with a template and stored in directories by month order. At the end of the month or periodically, I need to query and print a report giving customer name, account number, date of service and cost for a particular service/item for all customers who received such a service/item (at times up to 300 customer bills in... 1 1777 by: eemorris | last post by: Hi, I'm trying to use an asp page to send data from a basic HTML form to an Access database. I'm using GoDaddy hosting (which apparently many people have issues with when it comes to this) and here are my files: form_ac.asp: <% ' Declaring variables Dim firstname, lastname, company, phone, email, data_source, con, SQLINSERT ' A Function to check if some field entered by user is empty Function ChkString(string) 0 9973 by: Hystou | last post by: Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,... 1 11310 by: Hystou | last post by: Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,... 0 10677 by: tracyyun | last post by: Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some... 0 9870 by: agi2029 | last post by: Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea... 0 6093 by: TSSRALBI | last post by: Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls... 0 6315 by: adsilva | last post by: A Windows Forms form does not have the event Unload, like VB6. What one acts like? 1 4924 by: 6302768590 | last post by: Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system 2 4519 by: muto222 | last post by: How can i add a mobile payment intergratation into php mysql website. 3 3520 by: bsmnconsultancy | last post by: In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.