Calculating totals

[DN]

I have a report with 3 fields
Amount Paid
Fees
Owes or Refund

The Owes or Refund field holds the value of the Amount Paid subtracted from
the Fees. If it calculates an overpayment, the amount is surrounded by
parens. If there is a balance due, then there are no parens.

I can sum the Owes or Refund column but this gives me the difference between
the overpayment and the balance due.

The overpayment amount is $177. The balances due are $200. The calculation
of =Sum([AmountOwed]) gives me an amount of $23.

What I would like to have are 2 different calculations:
1 that provides me with the total of balances due
1 that provides me with the total of overpayments (refunds)

Is this possible?
Thanks,
DN

 

[Duane Hookom]

You could try expressions like:
=Sum([Amount Paid] - [Fees])
=Sum(Abs([Amount Paid] > [Fees]) * [Amount Paid] - [Fees])
=Sum(Abs([Amount Paid] < [Fees]) * [Amount Paid] - [Fees])

 

[DN]

Thanks for your quick reply. I tried your suggestions, but I am not getting
the desired results.

=Sum([Amount Paid] - [Fees])
-23

=Sum(Abs([Amount Paid] > [Fees]) * [Amount Paid] - [Fees])
($173.00)

=Sum(Abs([Amount Paid] < [Fees]) * [Amount Paid] - [Fees])
($648.00)

Do you have any other suggestions? Here are the actual fields and amounts.
Example:

TotalPaid (This sums 2 fields [PaidCash]+[PaidCheck])
Person1 $625
Person2 $125
Person3 $25
Person4 $0
Person5 $0
Person6 $0

SumofFees (This sums all applicable fields of fees)
Person1 $448
Person2 $140
Person3 $30
Person4 $60
Person5 $60
Person6 $60

AmountOwed (This is [TotalPaid]-[SumofFees])
Person1 ($177.00) We owe them $177.00
Person2 $15
Person3 $5
Person4 $60
Person5 $60
Person6 $60

I want to total all amounts like Person1.
Where we would owe them. $177
Then I want to total amounts like Person2, 3, 4, 5 & 6. Those who owe us.
$200

Thanks for your help,
DN

You could try expressions like:
=Sum([Amount Paid] - [Fees])
=Sum(Abs([Amount Paid] > [Fees]) * [Amount Paid] - [Fees])
=Sum(Abs([Amount Paid] < [Fees]) * [Amount Paid] - [Fees])

--
Duane Hookom
MS Access MVP

I have a report with 3 fields
Amount Paid
Fees
Owes or Refund

The Owes or Refund field holds the value of the Amount Paid subtracted
from
the Fees. If it calculates an overpayment, the amount is surrounded by
parens. If there is a balance due, then there are no parens.

I can sum the Owes or Refund column but this gives me the difference
between
the overpayment and the balance due.

The overpayment amount is $177. The balances due are $200. The
calculation
of =Sum([AmountOwed]) gives me an amount of $23.

What I would like to have are 2 different calculations:
1 that provides me with the total of balances due
1 that provides me with the total of overpayments (refunds)

Is this possible?
Thanks,
DN

 

[Duane Hookom]

You should have fixed my errors ;-). Assuming you want to sum the OverPaids
and UnderPaids, try the following:
=Sum(Abs([Amount Paid] > [Fees]) * ([Amount Paid] - [Fees]))
=Sum(Abs([Amount Paid] < [Fees]) * ([Amount Paid] - [Fees]))
--
Duane Hookom
MS Access MVP

Thanks for your quick reply. I tried your suggestions, but I am not
getting
the desired results.

=Sum([Amount Paid] - [Fees])
-23

=Sum(Abs([Amount Paid] > [Fees]) * [Amount Paid] - [Fees])
($173.00)

=Sum(Abs([Amount Paid] < [Fees]) * [Amount Paid] - [Fees])
($648.00)

Do you have any other suggestions? Here are the actual fields and
amounts.
Example:

TotalPaid (This sums 2 fields [PaidCash]+[PaidCheck])
Person1 $625
Person2 $125
Person3 $25
Person4 $0
Person5 $0
Person6 $0

SumofFees (This sums all applicable fields of fees)
Person1 $448
Person2 $140
Person3 $30
Person4 $60
Person5 $60
Person6 $60

AmountOwed (This is [TotalPaid]-[SumofFees])
Person1 ($177.00) We owe them $177.00
Person2 $15
Person3 $5
Person4 $60
Person5 $60
Person6 $60

I want to total all amounts like Person1.
Where we would owe them. $177
Then I want to total amounts like Person2, 3, 4, 5 & 6. Those who owe us.
$200

Thanks for your help,
DN

You could try expressions like:
=Sum([Amount Paid] - [Fees])
=Sum(Abs([Amount Paid] > [Fees]) * [Amount Paid] - [Fees])
=Sum(Abs([Amount Paid] < [Fees]) * [Amount Paid] - [Fees])

--
Duane Hookom
MS Access MVP

I have a report with 3 fields
Amount Paid
Fees
Owes or Refund

The Owes or Refund field holds the value of the Amount Paid subtracted
from
the Fees. If it calculates an overpayment, the amount is surrounded by
parens. If there is a balance due, then there are no parens.

I can sum the Owes or Refund column but this gives me the difference
between
the overpayment and the balance due.

The overpayment amount is $177. The balances due are $200. The
calculation
of =Sum([AmountOwed]) gives me an amount of $23.

What I would like to have are 2 different calculations:
1 that provides me with the total of balances due
1 that provides me with the total of overpayments (refunds)

Is this possible?
Thanks,
DN

 

[DN]

Sorry, just not paying attention, I guess. Beautiful, works like a champ.
Thanks a bunch!!!
DN

You should have fixed my errors ;-). Assuming you want to sum the OverPaids
and UnderPaids, try the following:
=Sum(Abs([Amount Paid] > [Fees]) * ([Amount Paid] - [Fees]))
=Sum(Abs([Amount Paid] < [Fees]) * ([Amount Paid] - [Fees]))
--
Duane Hookom
MS Access MVP

Thanks for your quick reply. I tried your suggestions, but I am not
getting
the desired results.

=Sum([Amount Paid] - [Fees])
-23

=Sum(Abs([Amount Paid] > [Fees]) * [Amount Paid] - [Fees])
($173.00)

=Sum(Abs([Amount Paid] < [Fees]) * [Amount Paid] - [Fees])
($648.00)

Do you have any other suggestions? Here are the actual fields and
amounts.
Example:

TotalPaid (This sums 2 fields [PaidCash]+[PaidCheck])
Person1 $625
Person2 $125
Person3 $25
Person4 $0
Person5 $0
Person6 $0

SumofFees (This sums all applicable fields of fees)
Person1 $448
Person2 $140
Person3 $30
Person4 $60
Person5 $60
Person6 $60

AmountOwed (This is [TotalPaid]-[SumofFees])
Person1 ($177.00) We owe them $177.00
Person2 $15
Person3 $5
Person4 $60
Person5 $60
Person6 $60

I want to total all amounts like Person1.
Where we would owe them. $177
Then I want to total amounts like Person2, 3, 4, 5 & 6. Those who owe us.
$200

Thanks for your help,
DN

You could try expressions like:
=Sum([Amount Paid] - [Fees])
=Sum(Abs([Amount Paid] > [Fees]) * [Amount Paid] - [Fees])
=Sum(Abs([Amount Paid] < [Fees]) * [Amount Paid] - [Fees])

--
Duane Hookom
MS Access MVP

I have a report with 3 fields
Amount Paid
Fees
Owes or Refund

The Owes or Refund field holds the value of the Amount Paid subtracted
from
the Fees. If it calculates an overpayment, the amount is surrounded by
parens. If there is a balance due, then there are no parens.

I can sum the Owes or Refund column but this gives me the difference
between
the overpayment and the balance due.

The overpayment amount is $177. The balances due are $200. The
calculation
of =Sum([AmountOwed]) gives me an amount of $23.

What I would like to have are 2 different calculations:
1 that provides me with the total of balances due
1 that provides me with the total of overpayments (refunds)

Is this possible?
Thanks,
DN