Sum field shows sum of all records, not just related records

[HLCruz via AccessMonster.com]

My apologies if this answer is already out there ... I looked, but couldn't
find what I needed:

I have a form which is based on a query of tblCamperInformation and
tblCurrentRegistration. I have three tabs, one for the tblCamperInformation,
on for tblCurrentRegistration and a third which has a subform for tblPayments.
tblPayments is comprised of payment information for each camper. I have
primary keys in each table, CamperID, RegistrationID and PaymentID.

What I would like is to have the balance of camp due to show on my tab of
tblCurrentRegistration, however, when I create a field to contain the sum of
each campers' payments I get the sum of ALL payments in that table.

I'm using this calculation in the control source - =Sum([Payment]). In my
subform on the third tab, this calculation returns only the sum of that
camper's payments.

Can I accomplish this by what I have set up? Part of me is wondering if I
should have created a subform in tab two for tblCurrentRegistration
information ... I didn't because I don't like the way subforms look ...

I hope I have explained this well enough, please let me know if I need to
provide more information.

Thank You to anyone who is willing to help!!!

 

[Access101]

Basing a field on a Totals query that shows only the records you want, would
then of course, total just those records. The result could go in that totals
box.