Allocating payments to line items

[Pat Hartman]

as it seems that the issue people in this thread have had is not my method for overcoming the challenge, but the fact it exists in the first place.

Not sure how you got that impression. I don't see where anyone said you shouldn't implement the user's business rules. I did tell you not to use a domain function. A left join is always better than using domain functions in queries or code loops. And since you are not updating the recordset you are reading, it doesn't matter that it aggregates the payments applied to each item. You have a payment amount - check, cash, CC. You have billed items and you have previous partial/full payments allocated to billed items. This is three tables. the allocated table is a junction table that connects the billed item with a payment. The query joins these two recordsets and selects billed items that have unpaid amounts. Then you take the new payment and create allocation records and insert them which creates the match between a specific payment to a specific billed item. THAT is the "solution" that lets you have the ability to prorate refunds correctly. In your schema, you have the allocation going to the invoice but it needs to go to the detail. That is the flaw in the schema.

The users get to make the business rules. We get to implement them. But, it is our job to implement them using sound practices and the ability to audit financial transactions is pretty high up there on the sound practices list. As it happens, creating the junction table kills two birds with one stone. It provides the data you need to correctly calculate the refund AND it provides the data needed to do a proper audit of how the payment was distributed among the outstanding items.