Running total- Backwards to 0

[Guest]

I have three tables with the following fields:

Table 1 Table 2 Table 3
Payer Name (primary) Payer Name Payer Name
Month/Year Month/Year
AR Balance Charges

I need to take the current month balance and back out the monthly charges
until I get to zero. Does anyone know how I can do that?

 

[Tom Ellison]

Dear Cortney:

I assume, then, that you know how to create an ascending running sum.

Subtract that sum from the balance value. You may need a subquery to get
that value. Or, you could build that into a low level query, then base the
final query on that.

Tom Ellison

 

[Guest]

I need to do some calculations before I get to this point. Is there a
function for the # of days in a month? I need to divide the monthly charges
by the number of days in that month.

Here is the calculation in excel:
=IF(W267<X269,W$1,IF(W268<=0, 0, IF((X269/W268)<0, 0,X269/W268)))

Where W267 is the charges for that month, W1 is the number of days in the
month, W268 is the monthly charges divided by the number of days in the
month, X269 is the AR Balance minus the charges for the next month. We are
working backwards from the current month until we get to zero.

Here is what the figures look like. The AR balance is 1,715,841
W X
1 31 30
2 Aug-05 Sep-05
267 617,901 624,607
268 19,932 20,820
269 473,333 1,091,234

I am still a little confused about the calcuation so putting it into access
has been difficult. Any help would be appreciated.

 

[Tom Ellison]

Dear Cortney:

Since you have the dates in your table, you can use DateDiff to find the
number of days between those dates. This assumes you know what the value is
for the year, as the length of February changes.

Tom Ellison