Is there an "offset" function in Access like in Excel or something similar I can do?

[Gina Maylone]

I have a list of customers' invoices. From time to time they pay an odd amount which an overpayment is applied to another balance, then perhaps they get another invoice after that. Anyway, the total due is correct, but the line item is not...which will drive people crazy believe it or not. I've attached an image for reference. In this case I want to figure out how I can removed the $20.62 and the -$20.62.

Thank you in advance, I hope everyone is enjoying their summer!

[kd2017]

I understand your problem but I'm not sure I understand your question.

Are the payments recorded in the invoice table?
Maybe it would be better to have a separate payments table with a payment details child table. Each payment detail is associated with one invoice and has a the dollar amount applied to that invoice. An invoice has many payments. A payment could have many invoices linked through payment detail. Are you in position where you can even modify the table designs?

[Gina Maylone]

I understand your problem but I'm not sure I understand your question.

Are the payments recorded in the invoice table?
Maybe it would be better to have a separate payments table with a payment details child table. Each payment detail is associated with one invoice and has a the dollar amount applied to that invoice. An invoice has many payments. A payment could have many invoices linked through payment detail. Are you in position where you can even modify the table designs?

The payments are recorded in an invoice table (debits and credits) and in a payment table for balancing. Yes, I can make design changes to the tables. Thanks for taking the time :-)

[kd2017]

I'm not an accountant, book keeper, or professional access developer... Just so we're clear. Hopefully a pro will step in here and let both OP and I know whether what I'm suggesting below is a good idea or not.

The model I suggested in post #2 is in fact very similar to a invoice & payment model I'm using in an application I'm building now. Here is a simplified image of the table relationship I'm talking about:


An [invoice] can have many [Invoice_Payments], the payments of an invoice equals the SUM of it's related [Invoice_Payments].[PaymentAmount] records.

A single [Payment] from a customer is broken up into smaller [Invoice_Payments]. [Payments].[AuditAmount] = Sum of it's related [Invoice_Payments].[PaymentAmount], this is for data entry accuracy purposes. These business rules would be enforced in your form's code.

From your post #3 it seems to me like all you would need to add to make this work is the Invoice_Payment junction table.

This approach will obviously require a little more work on building a user interface.

[CJ_London]

I am an accountant and your principle is fine.

There are a number of ways this can be done depending on the nature of the business - for example payments may be assigned to invoice lines rather than the invoice header. Or overpayments may be left unassigned (to an invoice) - e.g. and when there aren't any more invoices? If this can happen, the relationship between invoices and invoice payments cannot be enforced - alternatively you cannot have a rule which says the sum of the invoice payment amounts must equal the audit amount (which is a bad idea). Also in the UK (don't know about elsewhere) companies can elect for a simpler VAT payment system where VAT is not accounted for and due until the payment is made - so VAT accounting rules would need to be applied.

You would also normally have another join (not necessarily a relationship) at a higher level between payee and customer to ensure you do not assign a payment to the wrong customer.

It can get complicated and normally these things are handled within an accounting package so make sure you know exactly what is required.