aged debtors

[kieron]

Please help. I have two tables - the fields are detailed below. I am trying
to create an oustanding balance (ie Net Application Amount - Internal Credit
Note - Amount Paid) which I can do BUT I want to age the outstanding balance
so that when I input a receipt it takes it off the oldest claim first. I then
want this to give me age of remaining balance so that I can do an aged
debtors report say <30 days, 30-60 days etc. There can be numerous claims and
receipts for each Contract No, the tables can be joined on Contract No also.

Table 1[Claims];
Claim No., Contract No, Application Date, Net Application Amount, Internal
Credit Note
Table 2 [Receipts];
ID, Contract No, Payment Date, Amount Paid

 

[Fred]

A couple of thoughts.

You are talking "debtors" but you have no table that lists them, nor fields
in those other tables showing which debtors those transactions relate to?

Your ideat of automatically applying a payment to the oldest outstanding
invoive is going to be complicated but is also contrary to normal business
practices where a concious decision is made (by the payer or receiver)
regarding which invoice to apply a payment to.

You don't define what your "claims" table is, but I infer that it seems to
be claim related transactions.

I think that you're going to have to combine your two listed tables into one
"tranasactions" table which has fields to identify which type of entry that
it is. You'd probably need a peopl/customer/debtor table, and to link those
two together.

Hope that helps a little on getting started.