Hello,
I am having trouble using the DB sum function.
Technically, I am trying to get a running sum of a customer payments (pmts) over a 46 month span; however, I am not necessarily concerned about the exact date. I am just concerned about the month of payment.
I have a table "TestOrderedCF" that has a list of payments, Customer ID number and Month of Payment (0-45). I have created a calculated Field called CFRef which is the Customer Number + (Month of Payment/(46)). I am envisioning creating a function that sums all the payments between the Customer ID number and the CF Ref number to get a running total. I have a field in the current query called [cfr] which is the reference for the customer month I of the row.
When I do this:
PMTrunningtotal: DSum("Monthpmt","TestOrderedCF","CFref<="& [cfr] )
I can get a running total of all the payments if I have it in ascending order; however it doesn't restart at each new customer.
PMTrunningtotal: DSum("Monthpmt","TestOrderedCF","CFref<="& [cfr] AND "CFref>="&[CustomerNumber])
When I do this, it just sums all the month payments (All customers).
Can someone please let me know where my syntax/logic is wrong? It is driving me nuts. Thanks!