Macro: Ageing of amounts

[BelindaN]

Hi

I work with statements that need to go out to members. I need to create a new database in Access where I can have a balance that after 30 days it moves to 30days balance and then after another 30 days moves to 60days balance and so forth. I need to print the statements directly from Accss so it needs to be right.

Also when we receive a payment the macro must look in 120day balance 1st to see if there is an amount in there, if not it must move on to 90day balance until it finds an amount where it can allocate the payment.

Looking forward to hearing your ideas.

Thanks
Belinda

 

[Legacy 16232]

Are you dealing with different bill dates? Or does everything age at the same time? If it all ages at the same time, you need a macro that

A - takes your second oldest age bucket(90?) and adds it to the oldest bucket(120?).
B - moves the 60 to 90, moves the 30 to the 60 and moves the current to the 30 and maybe adds a new amount to the current.

If there are different bill dates, you need to have the date in the query and then only age those that have gotten a new bill.

 

[Legacy 16232]

As far as the payments, you will probably have to add payment amount as a field and run a seperate query for each age bucket until the payment amount = 0

 

[SydneyGeek]

The first part -- aging -- is best tackled in a query.

Let's say you have a table called Billing, with fields BillDate and BillAmount

You could build a query called qryAging with these fields:

1. BillDate
2. BillAmount
3. 30Days:iif([BillDate]<=Date()+30,[BillAmount],0)
4. 60Days:Iif([BillDate] Between Date()+31 And Date()+60,[BillAmount],0)

The 90Days and 120Days fields are similar to 60Days -- just change the date increment.

Come to think of it, you'll also need a PaidAmount field and a BalanceOwing field. That way you get a snapshot in the same place.
PaidAmount is a field in the table. BalanceOwing is a claculated field in the query ([BillAmount]-[PaidAmount]).

If you go this way, you'll want to substitute [PaidAmount] for the [BillAmount] field in 3. and 4. above

To check the payments for allocation is a bit trickier. You'll need another query, I guess, but it may need to be done in code.
Essentially, if you receive a payment from ClientA, you need to filter the Billing table by that client and sort the BillDate in Ascending order.
Then you run down the rows, allocating the payment until you run out of money to allocate.

Might have to think about this later when I'm at a machine that has Access installed.