434,660 Members | 1,939 Online
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

# Invoices/receipts Update for Students

 P: 6 Hi Bytes Community, 1)I have a table(student Registration) related to table(Invoice -many side). 2) The Student Registration table is related to table(Student Payments - many side). 3) Receipt details are captured in a table (Student Payments) I.e junction table. 4) Payments table is related to Student Payments table(many side) Question: After issuing an invoice to a student with invoiceID say 1, you capture payments over a 1 month period;then issue the next invoice with say InvoiceID 7. How do you carry forward the closing balance on invoice 1 to invoice 7 to enable continuous invoicing in real time? Note that students fees are updated every month with Date Received, Amount Received, plus description. For example, a Student is issued with an invoice amount of 100 euros on 28 July 2017 and pays 80 euros;How do you carry the balance of 20 euros for invoice 1 to invoice 7 as an Opening Balance in a report? I hope this information will help. Thanks Jul 23 '17 #1
4 Replies

 Expert 100+ P: 1,430 The method I use is to hold a Balance field in the student table. This is calculated by adding the invoice total and subtracting the payment to the existing Balance. If the balance <>0, then that is shown as Balance Brought Forward on each invoice Phil Jul 23 '17 #2

 P: 6 Thanks Phil but it didn't quiet solve my problem. Let me give a more detailed example; If the invoice Total is 100 euros invoiced on 28th July,the student pays 80 euros, the Balance B/F is 20 euros. If Current invoice Charge is 100 euros, then Total Due is 20 plus 100 euros which is 120. Question: How do you display the total due in the next invoice due on 28th February 2017? Jul 24 '17 #3

 Expert 100+ P: 1,430 So let's set out a hypothetical situation Expand|Select|Wrap|Line Numbers Date    Invoice  Payment  Balance   B/F  Invoice  Total 28 May    100               100      0     100     100 1 June              80       20    (20) 28 June   100               120     20     100     120 1 July              80       40    (40) 28 July   100                       40     100     140 1 Aug               120      20    (20)   The balance is obviously the running total of the invoices minus the payments, and can be calculated rather than storing it, but I tend to store it so that whenever I look at a Student record, I can immediately see the balance without having to do the calculation Phil Jul 24 '17 #4

 P: 6 Thanks Phil for the logical explanation. Jul 24 '17 #5