# Calculating totals

G

#### Guest

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

D

#### 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])

G

#### Guest

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

DN

Duane Hookom said:
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

DN said:
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

D

#### 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

DN said:
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

DN

Duane Hookom said:
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

DN said:
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

G

#### Guest

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

Duane Hookom said:
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

DN said:
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

DN

Duane Hookom said:
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