running sum query 1

[techkenny1]

Hi,

I have a question about using running sum. In a query I have used this code in the field;
RunTot: Format(DSum("TDeposit","tblDebits","[Debits]<=" & [Debits] & ""),"$0,000.00")-nz(DSum("TDebit","tblDebits","[Debits]<=" & [Debits] & ""),"$0,000.00")

While this works without any problems, I find it a bit slow.
Is there any other way of generating a running sum which would be quicker?

Many thanks.

 

[TheAceMan1]

How are ya techkenny1 . . .

Perhaps faq701-5268

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[techkenny1]

Hi The Aceman1
Many thanks for your reply.
I have set up the module. DAO3.6 is installed in the ref.

Now I am just not sure what to do.
I have set up the query as follows;
"RunSum: CCur(qryRunSum("SumID",[SumID],"curSum"))"
RunSum: CCur(qryRunSum("TDeposit",[Debits],"curSum"))
where 'TDeposit' is a currency field and 'debits' is the primary key of the table(tblDebits). Is this right?

When I open the query I get this Debug issue;
Microsoft Jet Database engine cannot find the input table or query. Make sure it exists and that its name is spelled correctly.
I have checked it all and it all seems ok

many thanks

 

[RoyVidar]

Though running sums are a drag on resources regardless of how they're created, usage of any of the domain aggregates are considered among the "worst", and I think you might be adding a bit by forcing implicit coercion between text and numbers per each row. You do this by first calculating one sum, which is formatted to a string, then you subtract the result of the second sum, which is converted to a string if it's Null.

One tiny bit faster/better, would be to only format the end result

RunTot: Format(DSum("TDeposit","tblDebits","[Debits]<=" & [Debits])-nz(DSum("TDebit","tblDebits","[Debits]<=" & [Debits]),0),"$0,000.00")

Or leave all the formatting to the report where you display this, and only perform the

RunTot: DSum("TDeposit","tblDebits","[Debits]<=" & [Debits])-nz(DSum("TDebit","tblDebits","[Debits]<=" & [Debits]),0)

The above is typed without any kind of testing

But then, there are other ways of doing running sums, for instance, but this would be more a query/SQL question (forum701 )

[tt]SELECT t.Debits, t.TDeposit, t.TDebit,
(SELECT Sum(s.TDeposit - NZ(s.TDebit,0))
FROM tblDebits s
WHERE s.Debits <= t.Debits) AS RunTot
FROM tblDebits t[/tt]

Roy-Vidar

 

[TheAceMan1]

techkenny1 . . .

Although your arguements were not setup properly, looking back at the function I see I forgot to make an update which inserts an additional arguement for the query name. So replace the function with the following:

[blue]Public Function qryRunSum([purple][b]qryName[/b][/purple] As String, idName As String, idValue, sumField As String)
   [green]'* qryName  - Name of the query calling this function
   '* idName   - UniqueFieldName ... usually the primarykey
   '* idValue  - The value of UniqueFieldName in the line above.
   '* sumField - The name of the field to runsum[/green]
   Dim db As DAO.Database, rst As DAO.Recordset, subSum
   
   Set db = CurrentDb()
   Set rst = db.OpenRecordset([purple][b]qryName[/b][/purple], dbOpenDynaset)
      
   ' Find the current record via proper syntax for Data Type.
   Select Case rst.Fields(idName).Type
      Case dbLong, dbInteger, dbCurrency, _
           dbSingle, dbDouble, dbByte 'Numeric Data Type
         rst.FindFirst "[" & idName & "] = " & idValue
      Case dbText 'Text Data Type
         rst.FindFirst "[" & idName & "] = '" & idValue & "'"
      Case dbDate 'Date Data Type
         rst.FindFirst "[" & idName & "] = #" & idValue & "#"
      Case Else
         rst.MovePrevious 'Move to BOF
   End Select
   
   ' Running Sum (subTotal) for each record occurs here.
   Do Until rst.BOF
      subSum = subSum + Nz(rst(sumField), 0)
      rst.MovePrevious
   Loop
   
   qryRunSum = subSum
   
   Set rst = Nothing
   Set db = Nothing
      
End Function[/blue]

The update (added arguement) is hilited in [purple]purple[/purple]. Also the function should return the proper datatype so the numeric conversion in the query is not needed. As as example consider I have a query named [blue]BankRunTotal[/blue]. The uniquefieldname I'll use is [blue]BankID[/blue], and the field I want to runsum in named [blue]CurBal[/blue]. The proper syntax in the custom field of the query would be:

[blue]Balance: qryRunSum("BankRunTotal","BankID",[BankID],"CurBal")[/blue]

So give it a shot and let me know. (So Sorry for the mess!).

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

RunTot: Format(DSum("Nz(TDeposit,0)-Nz(TDebit,0))","tblDebits","Debits<=" & [Debits]),"$0,000.00")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[techkenny1]

HI The Aceman1
Yes its really fast. Many thanks. Just one more question
In the form I have set the Balance field to currency. But the form will only show the balance as 12345 where I would like it to be $12345.

Many thanks.

 

[TheAceMan1]

techkenny1 . . .

In the custom field use the the currency conversion:

[blue]Balance: [purple][b]CCur([/b][/purple]qryRunSum("BankRunTotal","BankID",[BankID],"CurBal")[purple][b])[/b][/purple][/blue]

Special Note: After you enter [blue]CCur()[/blue] don't run the query from design view. Save & close the query, then open (allows optimization). After this the query is all yours.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]