Fear Naught
Kevin
- Local time
- Today, 22:25
- 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.
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!!
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!!