# Thread: PAYMENT FORM PROBLEM (Access 2000)

1. ## PAYMENT FORM PROBLEM (Access 2000)

<img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>I'm putting this problem up again. I got a few replies before, but I still can't get it to work.

I'm trying to do up a Payment Form that shows each line on an invoice. Whenever you go to enter a payment you apply amounts to the lines on the invoice. When you go to enter another payment, the lines on the invoice should show the amount of each line less the last payment(s). I need to do it this way because the outcome of this goes to print a form stating how much comes in to our office and how much stays at the school.

I'll give you an overview of my database. This is a database to record Student's school fees. I go into the main form and select a Student, I then press a button called Invoices and go in to enter their school fees. For example, I might pick Elementary from the drop-down list and enter \$49.00 and then on the next line I pick Local Levy from the drop-down list and enter \$10.00. I then press the Print Invoice button and it prints an invoice. I then close the Invoice form. Now lets say they come in and make a payment of \$20.00. I now go into that Student. Then I press the Payments button which goes into a Payment form. It lists the items on the invoice (Elementary Fee and Local Levy). I now enter the Payment information (date, amount, whether cheque or cash). Then I need to be able to go and apply the amount of the payment (\$20.00) to each item on the invoice. So if I choose, I apply \$10.00 to the Elementary Fee, and then \$10.00 to the Local Levy. I then print out a receipt.

Now lets say the Student came in and paid another \$15.00. I go into the Student's form and press the Payments button and enter another Payment for \$15.00. The items of the invoice should show on Payment screen the Elementary Fee less the first apply of \$10.00 which would now be \$39.00. And the Local Levy should now show zero. Then I would apply the \$15.00 to the Elementary Fee. Do you understand what I'm getting at?

I need to be able to do this because I will eventually run off a report that will list the amounts that they should submit to our office (we are the school jurisdiction head office--and have 17 schools under us). They submit the Elementary Fee to us, but the Local Levy stays at the school. So the payments they receive and put towards the Elementary Fee come to us and the amount that they put towards the Local Levy stays at the school.

Can anyone help me with this? I've been having such a hard time with it. Every time I try to work it over again I just end up going in circles. I don't know where to put the Payment Form. Should I have it coming off of the Invoice Table or the Invoice Details Table or the main Student Table? HELP!!!

2. ## Re: PAYMENT FORM PROBLEM (Access 2000)

Here's my thoughts -- I hope I understand your dilemma:

I'd suggest a separate "Payments" table that includes foreign links to the Student (who made the payment) and the Invoice (or Invoice detail?) it should be applied to. Also include in this table the info you mention (date, total amount, payment method). In addition, include the allocation between Elementary Fee and Local Levy (could these be percentages that add up to 100 rather than absolute amounts, or do you decide these on the fly depending on current balances?).

Once that's all defined, then a query could be built (may not be trivial; may require a few "cascading" queries) that would crunch all the payments against the linked invoices and would end up with balances remaining. Your invoice form could include a subform that lists the payments that have been made against each invoice.

Does this help any? It's rather sketchy, but maybe it'll give you some ideas.

3. ## Re: PAYMENT FORM PROBLEM (Access 2000)

Tom has the right idea with a payments table.
However, are there only 2 types of fees, Elementary and Levy. If there are more than the two types you will probably need an Payments Allocations table as well. This will make it more complex, but you should be able to see the total picture this way in relation to the invoices as well as the payments and their allocations.
Hope this helps.
Cheers,
Pat

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•