Running Totals/Billing/Access Subforms

[buzzboychicago]

Okay Complicated Design Question...

Lets say a customer signs up for a tek support allowance.

They buy $600.00 worth of calls. Different Types of calls cost different amounts.

Category A costs 20
Category B costs 30
Category C costs 40

However...if they exceed their allowance each additional call costs more

Category A goes from 20 to 45
Category B goes from 30 to 50
Category C goes from 40 to 65


(kind of like your mintues plan for a cellphone)

I need to build a database/form to keep track of this.

I am open to ideas. However, the way I began was having the record for the customer and the amount they have purchased as the main record. The subform consists of the various calls/call types.

However. I need to treat this like a bank would treat transactions. I need to keep a running total and check the total before I assign the price to a line item. But I wouldn't know where to begin with each line item or how to tell access which item to subtract from the total first, second, third, and then how to assign the prices. etc.

Does anyone have any suggestions/and or downloadable databases with something similar set up in it? I would greatly appreciate any help and my brain is locked as to how to proceed.

 

[PHV]

Do a keyword search in the Microsoft: Access Queries and JET SQL for running total select sum

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

buzzboychicago

There are several approaches to this. My preferred approach is to use a function. This way, I can call the function within a form, subform or even from a query.

Following function is for total time spent on a call. The function needs to be created in a module for general access rather than within a form where the scope would limit access to within the form...

Function FindTotalTime(lngID As Long) As Integer

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Sum(TimeSpent) as TotalTime FROM tblHistory " _
& "WHERE CallID = " & lngID

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
FindTotalTime = Nz(rst!TotalTime, 0)

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

End Function

Then to call the function, it is real simple...

From an unbound control field on a form...
=FindTotalTime([CallID])

From a query used to drive a report...
SELECT tblContact.ContactLN, Month([StartDate]) AS CallMonth, Year([StartDate]) AS CallYear, tblCall.MetricType, Count(tblCall.CallID) AS TotalCalls, Sum(FindTotalTime([CallID])) AS TotalTime
FROM tblContact INNER JOIN tblCall ON tblContact.ContactID = tblCall.SupportRepID
GROUP BY tblContact.ContactLN, Month([StartDate]), Year([StartDate]), tblCall.MetricType;

Richard