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
 
Ad

Advertisements

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

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

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

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
 
Ad

Advertisements

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top