DSum for running totals of groups. (1 Viewer)

[wrlgrain]

I forgot to mention that I added a column to TransmasTotalsCornQ2 and the other queries TransmasTotalsBeansQ2 and TransmasTotalsWheatQ2 that will show the commodity for each record in case it has to be evaluated in the Select Case statement.

 

[arnelgp]

you can make the function more generic, so it can accommodate the
three queries:

Option Compare Database
Option Explicit

Public Function fnRunTotal(QueryName As String, FieldNameToSum As String, FiscalMonth As Long, CropYear As Long) As Double

    Dim rs As DAO.Recordset
    
    With CurrentDb.OpenRecordset( _
        "select [" & FieldNameToSum & "] as expr1, [fiscalmonth] from [" & QueryName & "] " & _
        "where [cropyr] = " & CropYear & " order by [cropyr], [fiscalmonth];")
        
        If Not (.BOF And .EOF) Then
            .FindFirst "[fiscalmonth] = " & FiscalMonth
            
            If Not .NoMatch Then
                fnRunTotal = fnRunTotal + !expr1
                .MovePrevious
                While Not .BOF
                    fnRunTotal = fnRunTotal + !expr1
                    .MovePrevious
                Wend
            End If
        End If
    End With

End Function

now all we need to do is edit the running total column of the query and change it to:

RunTotal: fnRunTotal("TransmasTotalsCornQ2","Sum Of Totbush",[FiscalMonth],[CropYr])

the first parameter is the queryName you wish to process.
the second param is the field you want to total.
the last two are same as before.

 

[wrlgrain]

Works Great!!! Thanks again.