Calculating multiple amount fields to produce total

[Hawaii80]

Little confused here.

 

I have a table called Reserves Credits/Debits, with fields as such:

 

1. Property, (drop down list) related to Properties table to tie into correct rental property.

2. Description

3. Credit

4. Debit

 

Then I have a Disbursements table also related to Properties table with fields as such:

 

1. Description

2. Amount

3. Description2

4. Amount2

5. Description3

6. Amount3

7. Total Amount, (of above)

There are more fields on this table, however these 7 are really the only pertinent. Reason there are multiple description and amount fields is that there are sometimes multiple charges per transaction.

 

Now, on the Properties table I have a Total Reserve field as a calculation which in essence is supposed to take both the Reserve Credit/Debit amount(s) and Disbursement amount(s) and show end total.

 

By first glance the total appeared to be correct but once a "2nd" or more credit or debit was entered under Reserve Credit/Debit table the total isn't changing.

 

I have the Reserve total field being calculated as:

 

Reserves CreditsDebits::Credit Amount - Reserves CreditsDebits::Debit Amount - Disbursements::Total Reserve Expenses

 

Calulation is unstored, result is number and repetition 1.

 

Can't figure out why it's not working!!

[Hawaii80]

Anyone?

 

Does my explanation not make sense or no one is sure.

[Weetbicks]

Use this:

 

Sum ( Reserves CreditsDebits::Credit Amount ) - Sum ( Reserves CreditsDebits:Debit Amount ) - Disbursements::Total Reserve Expenses

 

 

Sum calculates the sum of a field for ALL records thru a relationship, wheas if you reference the field directly as you did, it will only ever calculate for the first CreditDebits record (which is why when there is a 2nd record it wasnt included in the total).

[Hawaii80]

Okay Weetbix, first I want to say you are awesome! Has anyone ever told you that?

 

Secondly, thank you very much this is the 2nd time you've helped me and in both situations explained what the problem was and how to resolve it. Your suggestion completely fixed my problem and I see now why it didn't work. I guess if there was only 1 record then makes sense to do it the way I was, but multiple records I need to ensure those fields are individually calculating using a Sum function in order to get the end result I needed. Thanks again!

[Weetbicks]

No prob, always glad to help