@ZoeMaKayla
I am guessing you have the following then;
1) A file that contains PAYMENT information (amount, date, customer)
2) A file that contains SALE information (amount, date, customer)
---SALE may either be a single record OR an invoice/line item parent/child.
You will want to add one file; a junction file.
T_ApplyPayment
-ApplyPaymentID...Autonumber...Primary Key
-SaleID...Numeric...Foreign Key
-PaymentID...Numeric...Foreign Key
-Dt_Applied...DATE...Date payment is effective (User editable, SHOULD be date of payment, but management may allow credit as if before)
-Amt_Applied...Currency... How much of payment to apply.
Now here comes the fun part.
When you are looking at sales, you won't store the amount paid. You will total all matching records in T_ApplyPayment. Once you have applied payments equal to the sales amount you would update the record with a PAID date. When you are looking at payments you will do the same until the total amount in the payments is applied, then you will update it APPLIED date.
What this will let you do;
1) Show sales that are not fully paid off (No PAID date) and the amount open on that sale (SALE amount, less the total of all T_ApplyPayment for that SALE).
2) Show all payments that have not been applied, or applied in full (No APPLIED date) and the current balance (PAYMENT amount, less the total of all T_ApplyPament fro that PAYMENT)
This covers the following cases;
-Sale is made but partial payment received
-Sale is made with full payment
-Payment received that covers more than one sale
-Payment received that exceeds all open sales
For myself, I would have a form with customer information, and a sub-form with open payments. Open payments are simply ones that do not have a APPLIED date. In the subform I would show the amount of the payment, have a calculated field with the amount applied, and a calculated field with the amount remaining.
From the sub-form I would have a button that calls another form to apply payments. The called form would be based off of the SALES table.
When you apply a payment, this creates the T_ApplyPayment record. You will want to make sure (as you will know ahead of time) that the amount applied does not exceed EITHER the amount open on the sale or the amount unapplied on the payment. For most users the date applied is when they apply the payment OR the date the payment was received (depending on your business rules). Managers would be able to enter an earlier date; some times they will want to consider the payment when the customer told them they paid rather than when the payment cleared.
Please let me know if this description will work for your business model. I didn't have a chance to make a sample over the weekend, been busy planning for holiday travel.