Account Totals in a Query Sub-Form?

[beshawn]

I can't take it anymore! Ok here's the problem. I have a database with three tables. Vendors, Contracts and Payments. I need to keep track of payments made to each contract by the account number. I've set up a form for the contracts that includes a sub-form for the payments. I want to be able to pull the total payments for each account number out of the sub-form. I've tried using the Dsum function but it doesn't seem to work. I either get the first account number used or an error. Should I be setting up another table for the account numbers? I would really appreciate any suggestions!


Thanks!

 

[thornmastr]

i think there is a relationship missing from your tables, but withot seeing the data descriptions of your tables, I may be spouting nothing at all.

If it is possible to post your table data descriptions it would be very helpfull.

Robert Berman

 

[experienced]

Beshawn,

Assuming your sub-form is a continuous form and is linked to the main form by the account number, you should create an unbound control, TotalPayment, in the subform's Form Footer and enter

=Sum([Payment])

in the Control Source of TotalPayment.

 

[beshawn]

Ok, again there are only three tables, Vendors, Payments, Contracts. The relationship between Payments and Contracts is based on a one to many using a contractID field in both tables. Here is the actual Dsum that is at the bottom of my Payments subform:

=DSum([PaymentAmount],"Payments Subform",[AccountNumber]=[Forms]![Contracts1]![Account1])

When I try and get this information in a container on the Contract for it gives me an "#Error" message. Again, I might have five different account numbers listed on the Contract Form and I want to be able to pull the total payments for each account out of the Payments Subform on the Contract Form. It seems so simple but I can't get it to work.

Again, thanks for the help.