One field auto-populated by the sum of the records of another field

[peterlee516]

Hi,

This is my first post. Thank you for reading it. And thank you in advance for your assistance.

I have an invoice table and a payments table. One invoice can have many payments (a deposit, final payment etc). The relationship is created.

I have a form with tabs that hold subforms. One of the tabs contains fields from the Invoice Table (i.e. Invoice Number, Invoice Amount, Total Paid). One other tab has a subform that contacts fields from the Payments table (i.e. Invoice Number, Check Number, Check Amount). I would like to auto-populate the Total Paid field in the Invoice Tab with the SUM of the Check Amount from the Payments tab BY Invoice Number.

This doesnt seem to difficult but I'm having trouble with it. Any assistance would be much appreciated.

Thank you!

 

[liddlem]

Hi Peterlee516
Not sure how your tabls/queries are set up but I presume that the following will point you in the right direction.

Create a text field on the form.
Set its datasource to
=Sum(YourInvNameQuery.InvAmnt)-Sum(YourCheckNameQuery.ChkAmnt)

 

[peterlee516]

Hi liddlem,

I understand what you are saying. Now I would like this new "Invoice Balance" field in a subform with mulltple invoices being displayed. How will this field tie back to that specific invoice record on the subform?

Thanks,

 

[liddlem]

Hi Peterlee516
I have presumed that your queries (YourInvNameQuery and YourCheckNameQuery) are getting the Invoice number from the InvNo field that is on your form.

So - Create 2 Queries
YourInvNameQuery has 2 fields - InvNo and InvAmount.
..InvNo criteria =forms!FormName!InvNo
YourCheckNameQuery has 2 fields - InvNo and CheckAmount.
..InvNo criteria =forms!FormName!InvNo