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
