I've got what I think is a fairly standard order/invoice system in db I look after for a children's club. This club has a term based fee for membership, and each term an order is generated for each parent containing the fee for each of the children they have attending. In the db this populates a table ( or example tblfeespayable) with an orderid and a line for each child containing the fee for that individual. Another table(tblpaymentsmade) tracks the payments that have been made for each term and line item in tblfeespayable.
I've created the code to populate tblfeesmade where the full payment is made easily enough, however people being as they are occasionally someone will pay less than they should and handling this is where I would like some advice on how to go about allocating the payments.
Ideally what I would like to do is allocate it such that the first line item is written into tblpaymentsmade as fully paid and the remaining amount allocated to the second, so for example:
Bob's fee is £50
Alan's fee is £25.50
Their parent pays £75 which leaves 50p outstanding
Bob gets an entry for £50 in tblpaymentsmade
Alan gets an entry for £25 in tblpaymentsmade
I'm thinking that the way to do this is:
I've created the code to populate tblfeesmade where the full payment is made easily enough, however people being as they are occasionally someone will pay less than they should and handling this is where I would like some advice on how to go about allocating the payments.
Ideally what I would like to do is allocate it such that the first line item is written into tblpaymentsmade as fully paid and the remaining amount allocated to the second, so for example:
Bob's fee is £50
Alan's fee is £25.50
Their parent pays £75 which leaves 50p outstanding
Bob gets an entry for £50 in tblpaymentsmade
Alan gets an entry for £25 in tblpaymentsmade
I'm thinking that the way to do this is:
- create a recordset that selects out Bob and Alan's fees from tblfeespayable
- sets the amount_paid as a variable (I'll add a break here if amount_paid > total of feespayable)
- a dcount to check if there are any payments matching the payment reference in tblpaymentsmade if this returns 0 goto step 5 else
- a second recordset and a loop that checks whether a payment already exists in tblpaymentsmade for Bob and if there is compares it to the amount in tblfeespayable
- if the comparision is true then the loop skips to the Alan's record
- if the comparision is false then the difference between the two is subtracted from amount_paid and Bob's entry in tblpaymentsmade is updated
- if amount_paid = 0 the loop ends and we exit sub
- if not move to Alan's record and repeat the comparision or if we are EOF (i.e Alan has no record in tblpayments made) exit this loop
- A loop with the recordset created in step 1
- Compare amount_paid to Bob's fee in tblfeespayable
- subtract amount_paid from Bob's fee and insert it into tblpaymentsmade
- if amount_paid = 0 then end loop and exit sub else
- compare amount_paid to Alan's fee in tblfeespayable
- subtract amount_paid from Alan's fee and insert the result into tblpayments made
- End sub