The Knotty subject of Running Totals in a Form (1 Viewer)

Fear Naught

Kevin
Local time
Today, 15:21
Joined
Mar 2, 2006
Messages
229
Applying running totals to a form or query seems to be a difficult area for Access.

I am creating a database to show the profit margin and subsequent bonus and commissions for the sales manager. Because of the complex commission rules (commission multiplier changes based on the percentage of order value against target). So the commission on an order today may be at a different multiplier than another order tomorrow because it takes the sales manager over a certain threshold. Phew hope that makes sense.

So I have to show the running total of order value against each order for that sales manager in order to calculate the percentage towards target. I can do this fairly easily using the function I have written (see below). however the performance of the query that shows this running total is abysmal.

The query in the function is a Union query with data taken from 4 linked spreadsheet worksheets. The union query runs nice and quickly.

The parameters sent to the function are the record ID and the name of the sales manager.

Code:
Public Function RunningTotal(ID As Integer, AC As String) As Currency

Dim intID As Integer
Dim intGM As Double
Dim strAC As String
Dim strSQL As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

intID = ID
strAC = AC
intGM = 0

strSQL = "select id, gm, account from qryunion_all_business_unit_orders " & _
            "where id <=" & intID & " and account = '" & strAC & "';"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'rs.MoveLast
If rs.RecordCount > 0 Then
    rs.MoveFirst
    While Not rs.EOF
        intGM = intGM + rs!gm
        rs.MoveNext
    Wend
    
End If
rs.Close
RunningTotal = intGM

End Function

Is there anyway of speeding this up? As it is the system is unworkable with less than one months data. As the Financial Year progresses it will get much worse I assume.

And yes I know there is no error checking in the function!! :rolleyes:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:21
Joined
Aug 30, 2003
Messages
36,118
For starters, let JET do the work:

strSQL = "select Sum(gm) AS HowMuch from qryunion_all_business_unit_orders " & _
"where id <=" & intID & " and account = '" & strAC & "';"

RunningTotal = rs!HowMuch
 

spikepl

Eledittingent Beliped
Local time
Today, 16:21
Joined
Nov 3, 2010
Messages
6,144
And put index on "account"
 

spikepl

Eledittingent Beliped
Local time
Today, 16:21
Joined
Nov 3, 2010
Messages
6,144
Ah I missed that point.

But the result of your UNION query could be used to update a temporary indexed table, which then could be the source for your cumulative-sum calculation query. Whether this is worthwhile depends of course on what needs to be done how often snd other potential constraints due to the workflow.

Besides, I would also consider storing the cumulative totals.This of course depends on whether a total is permanent, or some retroactive changes might appear, as may be the case since you have external data.
 

spikepl

Eledittingent Beliped
Local time
Today, 16:21
Joined
Nov 3, 2010
Messages
6,144
One other option I have seen around is to have a calculated field with the cumulative total (by means of a function called directly in the query, having a Static internal variable to tally up the total so far). This has the advantage of not recalculating the lot, but updating each new record with the sum so far + the record itself, instead of summing up from the beginning. Whether or not this is better than you current solution again depends on what is done and how often.

I think I have no more ideas.
 

vbaInet

AWF VIP
Local time
Today, 15:21
Joined
Jan 22, 2010
Messages
26,374
Cummulative totals are better handled in reports since it's done automatically. Your user can view the report for reference. They will need to reopen the report for it to reflect changes made whilst the report was open.

spikepl's suggestion of performing the running sum in the control source (and storing the sums in a variable) is a good idea. Remember to reset the variable and Requery the form in the AfterUpdate event of the control. Declare the variable Public within the form's module.
 

Fear Naught

Kevin
Local time
Today, 15:21
Joined
Mar 2, 2006
Messages
229
Many thanks for everyone's help. I have now managed to speed up the system by have a temp table that is indexed and changing the underlying query. The running sum works nicely now.

However.... (isn't that always the case) I am now trying to vcalculate the commissions payable based on a multiplier. As an example

0-25% of target pays based on 2% of margin of order
26-50% of target pays based on 3% of marging of order
etc all the way up to 176 and above % of taget.

The multipliers values specific to the sales manager are on the main form. I am writing a function to display the commission payable against each order on the subform but am getting a type mismatch error. It seem that in the code below the reference to the controls on the main form are being allocated to the variable dblMultiplier on the text of in the case statement.

What am I doing wrong?

Code:
ublic Function CommPayment(gm As Double, percent As Double)

Dim dblGM As Double
Dim dblPercent As Double
Dim dblMultiplier As Variant

dblGM = gm
dblPercent = percent

Select Case dblPercent
    Case Is > 175
        dblMultiplier = "& forms!frmindividualdata_months.176+% &"
    Case Is > 150
        dblMultiplier = "& forms!frmindividualdata_months.151-175% &"
    Case Is > 125
        dblMultiplier = "& forms!frmindividualdata_months.126-150% &"
    Case Is > 100
        dblMultiplier = "& forms!frmindividualdata_months.100-126% &"
    Case Is > 50
        dblMultiplier = "& forms!frmindividualdata_months.50-100% &"
    Case Is > 25
        dblMultiplier = "& forms!frmindividualdata_months.26-50% &"
    Case Is > 0
        dblMultiplier = "& forms!frmindividualdata_months.0-50% &"
End Select
    
  CommPayment = gm * dblMultiplier

End Function
 

Fear Naught

Kevin
Local time
Today, 15:21
Joined
Mar 2, 2006
Messages
229
Ignore that posting - I have fixed it. A simple error that I really should not have made. Revised cose below.

Code:
Public Function CommPayment(gm As Double, percent As Double)

Dim dblGM As Double
Dim dblPercent As Double
Dim dblMultiplier As Variant

dblGM = gm
dblPercent = percent

Select Case dblPercent
    Case Is > 175
        dblMultiplier = [Forms]![frmindividualdata_months].[176+%]
    Case Is > 150
        dblMultiplier = [Forms]![frmindividualdata_months].[151-175%]
    Case Is > 125
        dblMultiplier = [Forms]![frmindividualdata_months].[126-150%]
    Case Is > 100
        dblMultiplier = [Forms]![frmindividualdata_months].[100-126%]
    Case Is > 50
        dblMultiplier = [Forms]![frmindividualdata_months].[50-100%]
    Case Is > 25
        dblMultiplier = [Forms]![frmindividualdata_months].[26-50%]
    Case Is > 0
        dblMultiplier = [Forms]![frmindividualdata_months].[0-50%]
End Select
    
  CommPayment = gm * (dblMultiplier / 100)

End Function
 

Users who are viewing this thread

Top Bottom