Total line makes query too complex

[kratz]

So I have created my functions to calculate the expected deduction per
deduction per agent number per account number. The problem I am having now is
that I need to sum up the deductions per agent number per account number.
(Each agent may have multiple deductions in the account.) I was working on
this DSum, but am getting a not defined error on DeductConcate:

Public Function ExpectedDeductionsSum() As Variant

ExpectedDeductionsSum = DSum("[ExpectedDeductionsToDate]",
"[qryExpectedDeductions]", "[DeductConcate] = '" & [DeductConcate] & "'")

End Function


The final function that combines the others:

Public Function ExpectedDeductions(CurrentLookup As String,
FinalStartMonthLookup As String, FinalNoMonths As String, FinalMoDeduction As
Currency, FinalTotalDeduction As Currency) As Currency

'Determine Expected Deductions

If ((CurrentLookup - FinalStartMonthLookup) + 1) <= 0 Then
ExpectedDeductions = 0
Else
If ((CurrentLookup - FinalStartMonthLookup) + 1) > 0 And ((CurrentLookup
- FinalStartMonthLookup) + 1) < FinalNoMonths Then
ExpectedDeductions = ((CurrentLookup - FinalStartMonthLookup) + 1) *
FinalMoDeduction
Else
ExpectedDeductions = FinalTotalDeduction
End If

End If

End Function


CurrentLookup is a DLookup entered into the expression


Public Function FinalStartMonthLookup(FinalStartMonth As String) As String

'Determine the Start Month Lookup from the Months table

FinalStartMonthLookup = "SELECT [Months.MonthIndex] " & _
"FROM [Months] " & _
"WHERE [Months.Month] = '" & FinalStartMonth & "' " & _
"ORDER BY [Months.MonthIndex];"

End Function


Public Function FinalNoMonths(ONoMonths As Variant, UNoMonths As Variant,
UpdatedMonth As Boolean) As Variant

'Determine Final Number of Months for each deduction

If UpdatedMonth = True Then
FinalNoMonths = UNoMonths
Else
FinalNoMonths = ONoMonths

End If

End Function


Public Function FinalMoDeduction(OMoDeduction As Variant, UMoDeduction As
Variant, UpdatedMonth As Boolean) As Variant

If UpdatedMonth = True Then
FinalMoDeduction = UMoDeduction
Else
FinalMoDeduction = OMoDeduction
End If
End Function


Public Function FinalTotalDeduction(OTotalDeduction As Variant,
UTotalDeduction As Variant, UpdatedMonth As Boolean) As Variant

If UpdatedMonth = True Then
FinalTotalDeduction = UTotalDeduction
Else
FinalTotalDeduction = OTotalDeduction
End If


End Function


I have tried to create a query and use the Total line to Group By the agent
number / account number and Sum the deduction amount, but it is too complex.
Any other suggestions?

Thanks