Percent on sum total

[acss]

I have a country report which group two accounts and provides a subtotal for
each account per country. How do i create another field that would subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[Al Campagna]

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[acss]

Hello Al,

I am not clear on what you mean. Are you stating to create two fields ...one
for the percent calculation and another to reference this calculation. Also,
do i use expression builder to create the field?

Thank you

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have a country report which group two accounts and provides a subtotal
for
each account per country. How do i create another field that would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[acss]

I have the calculation on the report and it only works on individual records
per account. So if there is more than one record the calculation is
incorrect. Could this be a field in query not allowing for the calculation to
work on a group?

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have a country report which group two accounts and provides a subtotal
for
each account per country. How do i create another field that would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[Al Campagna]

acss,
Perhaps I misunderstood...
When you wrote...Did you mean that whatever the sum of InvAmt is, for a Country, subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have the calculation on the report and it only works on individual
records
per account. So if there is more than one record the calculation is
incorrect. Could this be a field in query not allowing for the calculation
to
work on a group?

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have a country report which group two accounts and provides a subtotal
for
each account per country. How do i create another field that would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[acss]

Thank you since your first suggestion in placing the expression in query
worked as well as creating a separate field for the percent. How would you
create the expression if we need to subtract 6 percent from the country china
and 3 percent from country brazil? Can this be done on the same report?

acss,
Perhaps I misunderstood...
When you wrote...Did you mean that whatever the sum of InvAmt is, for a Country, subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have the calculation on the report and it only works on individual
records
per account. So if there is more than one record the calculation is
incorrect. Could this be a field in query not allowing for the calculation
to
work on a group?

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."




I have a country report which group two accounts and provides a subtotal
for
each account per country. How do i create another field that would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[Al Campagna]

acss,
If you have different countries at different rates, you'll need to save
those values in your table.
Brazil might have a Rate of .97, China might have a Rate of .94
(If you express the Rate as .97 then it's just a straight
multiplication... if you use .03 then you multiply the InvAmt by .03, and
then subtract that value from InvAmt.)

Then...
= InvAmt * Rate
would yield the proper amount.

If that Rate may change for a country in the future, then you'll have
to capture that Rate value whenever you do an InvAmt transaction.

Now you're beginning to see why doing a Rate calculation for each line
item comes into play. Particularly in historical context.

Country Date InvAmt Rate Result
Brazil 1/1/07 100 .97 97
2/4/08 300 .97 291
1/1/09 200 .96 192
--------------------------------------
600 580
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you since your first suggestion in placing the expression in query
worked as well as creating a separate field for the percent. How would you
create the expression if we need to subtract 6 percent from the country
china
and 3 percent from country brazil? Can this be done on the same report?

acss,
Perhaps I misunderstood...
When you wrote...

subtract the subtotal by 3 percent?

Did you mean that whatever the sum of InvAmt is, for a Country,
subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have the calculation on the report and it only works on individual
records
per account. So if there is more than one record the calculation is
incorrect. Could this be a field in query not allowing for the
calculation
to
work on a group?

:

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."




I have a country report which group two accounts and provides a
subtotal
for
each account per country. How do i create another field that would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[acss]

Thank you and sorry for the double posting. Could you give me one example on
placing a rate for a country in a table since this is where the soulition
could be and i am not too familiar with this step?

Thanks Again

acss,
If you have different countries at different rates, you'll need to save
those values in your table.
Brazil might have a Rate of .97, China might have a Rate of .94
(If you express the Rate as .97 then it's just a straight
multiplication... if you use .03 then you multiply the InvAmt by .03, and
then subtract that value from InvAmt.)

Then...
= InvAmt * Rate
would yield the proper amount.

If that Rate may change for a country in the future, then you'll have
to capture that Rate value whenever you do an InvAmt transaction.

Now you're beginning to see why doing a Rate calculation for each line
item comes into play. Particularly in historical context.

Country Date InvAmt Rate Result
Brazil 1/1/07 100 .97 97
2/4/08 300 .97 291
1/1/09 200 .96 192
--------------------------------------
600 580
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you since your first suggestion in placing the expression in query
worked as well as creating a separate field for the percent. How would you
create the expression if we need to subtract 6 percent from the country
china
and 3 percent from country brazil? Can this be done on the same report?

acss,
Perhaps I misunderstood...
When you wrote...
subtract the subtotal by 3 percent?
Did you mean that whatever the sum of InvAmt is, for a Country,
subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I have the calculation on the report and it only works on individual
records
per account. So if there is more than one record the calculation is
incorrect. Could this be a field in query not allowing for the
calculation
to
work on a group?

:

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."




I have a country report which group two accounts and provides a
subtotal
for
each account per country. How do i create another field that would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[Al Campagna]

acss,
In whatever table you capture the InvAmt, add a new field called Rate or
Commission or whatever makes sense for you.
Let's use Rate.
This would be a numeric single field.
Now that the field has been added to your table, add it to the your data
entry form.

As we discussed, you can express the Rate as .97 (which represents 3%)
or .94 (which represents 6%) etc...
Then just multiply InvAmt by that value to get what you requested...--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you and sorry for the double posting. Could you give me one example
on
placing a rate for a country in a table since this is where the soulition
could be and i am not too familiar with this step?

Thanks Again

acss,
If you have different countries at different rates, you'll need to
save
those values in your table.
Brazil might have a Rate of .97, China might have a Rate of .94
(If you express the Rate as .97 then it's just a straight
multiplication... if you use .03 then you multiply the InvAmt by .03, and
then subtract that value from InvAmt.)

Then...
= InvAmt * Rate
would yield the proper amount.

If that Rate may change for a country in the future, then you'll
have
to capture that Rate value whenever you do an InvAmt transaction.

Now you're beginning to see why doing a Rate calculation for each
line
item comes into play. Particularly in historical context.

Country Date InvAmt Rate Result
Brazil 1/1/07 100 .97 97
2/4/08 300 .97 291
1/1/09 200 .96 192
--------------------------------------
600 580
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Thank you since your first suggestion in placing the expression in
query
worked as well as creating a separate field for the percent. How would
you
create the expression if we need to subtract 6 percent from the country
china
and 3 percent from country brazil? Can this be done on the same report?

:

acss,
Perhaps I misunderstood...
When you wrote...
subtract the subtotal by 3 percent?
Did you mean that whatever the sum of InvAmt is, for a Country,
subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I have the calculation on the report and it only works on individual
records
per account. So if there is more than one record the calculation is
incorrect. Could this be a field in query not allowing for the
calculation
to
work on a group?

:

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."




I have a country report which group two accounts and provides a
subtotal
for
each account per country. How do i create another field that
would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[acss]

Thank you Al.

The problem is that the report will be run from data that already exists
from the tables and it would be done monthly. All i really need is the
formula to some how filter one country from the other on the report. I
created a field in the report which does the filter to some degree:

=IIf(([VendorCtry] Like '*PERU*'),[sumofInvAmt]*0.05)---This displays for
PERU yet since i have a field for all groups with expression
=Sum([SumOfInvAmt])*0.03 so now PERU displays two fields one for *0.05 and
another in the groupings of all countries for *0.03. Is there a way to hide
the *0.03 field from PERU only?

However, the grouping

acss,
In whatever table you capture the InvAmt, add a new field called Rate or
Commission or whatever makes sense for you.
Let's use Rate.
This would be a numeric single field.
Now that the field has been added to your table, add it to the your data
entry form.

As we discussed, you can express the Rate as .97 (which represents 3%)
or .94 (which represents 6%) etc...
Then just multiply InvAmt by that value to get what you requested...--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you and sorry for the double posting. Could you give me one example
on
placing a rate for a country in a table since this is where the soulition
could be and i am not too familiar with this step?

Thanks Again

acss,
If you have different countries at different rates, you'll need to
save
those values in your table.
Brazil might have a Rate of .97, China might have a Rate of .94
(If you express the Rate as .97 then it's just a straight
multiplication... if you use .03 then you multiply the InvAmt by .03, and
then subtract that value from InvAmt.)

Then...
= InvAmt * Rate
would yield the proper amount.

If that Rate may change for a country in the future, then you'll
have
to capture that Rate value whenever you do an InvAmt transaction.

Now you're beginning to see why doing a Rate calculation for each
line
item comes into play. Particularly in historical context.

Country Date InvAmt Rate Result
Brazil 1/1/07 100 .97 97
2/4/08 300 .97 291
1/1/09 200 .96 192
--------------------------------------
600 580
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Thank you since your first suggestion in placing the expression in
query
worked as well as creating a separate field for the percent. How would
you
create the expression if we need to subtract 6 percent from the country
china
and 3 percent from country brazil? Can this be done on the same report?

:

acss,
Perhaps I misunderstood...
When you wrote...
subtract the subtotal by 3 percent?
Did you mean that whatever the sum of InvAmt is, for a Country,
subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I have the calculation on the report and it only works on individual
records
per account. So if there is more than one record the calculation is
incorrect. Could this be a field in query not allowing for the
calculation
to
work on a group?

:

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] = 6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] = 24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."




I have a country report which group two accounts and provides a
subtotal
for
each account per country. How do i create another field that
would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[Al Campagna]

acss,
In the report section where Peru prints, use the OnFormat event of that
section... with this code.
(Say the .03 calculation field is called Rate03)

If Country = "Peru" Then
Rate03.Visible = False
Else
Rate03.Visible = True
End If

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you Al.

The problem is that the report will be run from data that already exists
from the tables and it would be done monthly. All i really need is the
formula to some how filter one country from the other on the report. I
created a field in the report which does the filter to some degree:

=IIf(([VendorCtry] Like '*PERU*'),[sumofInvAmt]*0.05)---This displays for
PERU yet since i have a field for all groups with expression
=Sum([SumOfInvAmt])*0.03 so now PERU displays two fields one for *0.05 and
another in the groupings of all countries for *0.03. Is there a way to
hide
the *0.03 field from PERU only?

However, the grouping

acss,
In whatever table you capture the InvAmt, add a new field called Rate
or
Commission or whatever makes sense for you.
Let's use Rate.
This would be a numeric single field.
Now that the field has been added to your table, add it to the your
data
entry form.

As we discussed, you can express the Rate as .97 (which represents
3%)
or .94 (which represents 6%) etc...
Then just multiply InvAmt by that value to get what you requested...

subtract the subtotal by 3 percent?

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Thank you and sorry for the double posting. Could you give me one
example
on
placing a rate for a country in a table since this is where the
soulition
could be and i am not too familiar with this step?

Thanks Again

:

acss,
If you have different countries at different rates, you'll need
to
save
those values in your table.
Brazil might have a Rate of .97, China might have a Rate of .94
(If you express the Rate as .97 then it's just a straight
multiplication... if you use .03 then you multiply the InvAmt by .03,
and
then subtract that value from InvAmt.)

Then...
= InvAmt * Rate
would yield the proper amount.

If that Rate may change for a country in the future, then you'll
have
to capture that Rate value whenever you do an InvAmt transaction.

Now you're beginning to see why doing a Rate calculation for each
line
item comes into play. Particularly in historical context.

Country Date InvAmt Rate Result
Brazil 1/1/07 100 .97 97
2/4/08 300 .97 291
1/1/09 200 .96 192
--------------------------------------
600 580
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Thank you since your first suggestion in placing the expression in
query
worked as well as creating a separate field for the percent. How
would
you
create the expression if we need to subtract 6 percent from the
country
china
and 3 percent from country brazil? Can this be done on the same
report?

:

acss,
Perhaps I misunderstood...
When you wrote...
subtract the subtotal by 3 percent?
Did you mean that whatever the sum of InvAmt is, for a Country,
subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


I have the calculation on the report and it only works on
individual
records
per account. So if there is more than one record the calculation
is
incorrect. Could this be a field in query not allowing for the
calculation
to
work on a group?

:

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] =
6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] =
24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."




I have a country report which group two accounts and provides a
subtotal
for
each account per country. How do i create another field that
would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[acss]

Al,

I receive an error when running the report as if it is looking for a macro
to execute. I right clicked the bottom of report and onformat event placed
the code and then receive the error.

acss,
In the report section where Peru prints, use the OnFormat event of that
section... with this code.
(Say the .03 calculation field is called Rate03)

If Country = "Peru" Then
Rate03.Visible = False
Else
Rate03.Visible = True
End If

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you Al.

The problem is that the report will be run from data that already exists
from the tables and it would be done monthly. All i really need is the
formula to some how filter one country from the other on the report. I
created a field in the report which does the filter to some degree:

=IIf(([VendorCtry] Like '*PERU*'),[sumofInvAmt]*0.05)---This displays for
PERU yet since i have a field for all groups with expression
=Sum([SumOfInvAmt])*0.03 so now PERU displays two fields one for *0.05 and
another in the groupings of all countries for *0.03. Is there a way to
hide
the *0.03 field from PERU only?

However, the grouping

acss,
In whatever table you capture the InvAmt, add a new field called Rate
or
Commission or whatever makes sense for you.
Let's use Rate.
This would be a numeric single field.
Now that the field has been added to your table, add it to the your
data
entry form.

As we discussed, you can express the Rate as .97 (which represents
3%)
or .94 (which represents 6%) etc...
Then just multiply InvAmt by that value to get what you requested...
subtract the subtotal by 3 percent?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Thank you and sorry for the double posting. Could you give me one
example
on
placing a rate for a country in a table since this is where the
soulition
could be and i am not too familiar with this step?

Thanks Again

:

acss,
If you have different countries at different rates, you'll need
to
save
those values in your table.
Brazil might have a Rate of .97, China might have a Rate of .94
(If you express the Rate as .97 then it's just a straight
multiplication... if you use .03 then you multiply the InvAmt by .03,
and
then subtract that value from InvAmt.)

Then...
= InvAmt * Rate
would yield the proper amount.

If that Rate may change for a country in the future, then you'll
have
to capture that Rate value whenever you do an InvAmt transaction.

Now you're beginning to see why doing a Rate calculation for each
line
item comes into play. Particularly in historical context.

Country Date InvAmt Rate Result
Brazil 1/1/07 100 .97 97
2/4/08 300 .97 291
1/1/09 200 .96 192
--------------------------------------
600 580
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Thank you since your first suggestion in placing the expression in
query
worked as well as creating a separate field for the percent. How
would
you
create the expression if we need to subtract 6 percent from the
country
china
and 3 percent from country brazil? Can this be done on the same
report?

:

acss,
Perhaps I misunderstood...
When you wrote...
subtract the subtotal by 3 percent?
Did you mean that whatever the sum of InvAmt is, for a Country,
subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


I have the calculation on the report and it only works on
individual
records
per account. So if there is more than one record the calculation
is
incorrect. Could this be a field in query not allowing for the
calculation
to
work on a group?

:

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)] =
6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] =
24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."




I have a country report which group two accounts and provides a
subtotal
for
each account per country. How do i create another field that
would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%

 

[Al Campagna]

acss,
Copy and Paste your code exactly as you have it, to your reply.Don't have any idea what that means... the instructions were to select
the report section (ex. the Detail section) where Country = Peru would
appear on your report

I'll assume that the Peru data prints in the Detail section of the
report. You use whatever section it's in.

Go to the Properties for the Detail section, and find it's OnFormat
event.
Using the arrow to the right of the OnFormat text box, and select [Event
Procedure]
Now click the 3 dots (...) to the right of the OnFormat text box.
You'll now be in the module behind the form, located in the OnFormat
code section...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

End Sub

Place the code that I gave you in that event...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Country = "Peru" Then
Rate03.Visible = False
Else
Rate03.Visible = True
End If
End Sub

When "Peru" comes to print, it's Rate03 control will be hidden.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Al,

I receive an error when running the report as if it is looking for a macro
to execute. I right clicked the bottom of report and onformat event placed
the code and then receive the error.

acss,
In the report section where Peru prints, use the OnFormat event of
that
section... with this code.
(Say the .03 calculation field is called Rate03)

If Country = "Peru" Then
Rate03.Visible = False
Else
Rate03.Visible = True
End If

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Thank you Al.

The problem is that the report will be run from data that already
exists
from the tables and it would be done monthly. All i really need is the
formula to some how filter one country from the other on the report. I
created a field in the report which does the filter to some degree:

=IIf(([VendorCtry] Like '*PERU*'),[sumofInvAmt]*0.05)---This displays
for
PERU yet since i have a field for all groups with expression
=Sum([SumOfInvAmt])*0.03 so now PERU displays two fields one for *0.05
and
another in the groupings of all countries for *0.03. Is there a way to
hide
the *0.03 field from PERU only?

However, the grouping
:

acss,
In whatever table you capture the InvAmt, add a new field called
Rate
or
Commission or whatever makes sense for you.
Let's use Rate.
This would be a numeric single field.
Now that the field has been added to your table, add it to the
your
data
entry form.

As we discussed, you can express the Rate as .97 (which represents
3%)
or .94 (which represents 6%) etc...
Then just multiply InvAmt by that value to get what you
requested...
subtract the subtotal by 3 percent?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Thank you and sorry for the double posting. Could you give me one
example
on
placing a rate for a country in a table since this is where the
soulition
could be and i am not too familiar with this step?

Thanks Again

:

acss,
If you have different countries at different rates, you'll
need
to
save
those values in your table.
Brazil might have a Rate of .97, China might have a Rate of
.94
(If you express the Rate as .97 then it's just a straight
multiplication... if you use .03 then you multiply the InvAmt by
.03,
and
then subtract that value from InvAmt.)

Then...
= InvAmt * Rate
would yield the proper amount.

If that Rate may change for a country in the future, then
you'll
have
to capture that Rate value whenever you do an InvAmt transaction.

Now you're beginning to see why doing a Rate calculation for
each
line
item comes into play. Particularly in historical context.

Country Date InvAmt Rate Result
Brazil 1/1/07 100 .97 97
2/4/08 300 .97 291
1/1/09 200 .96 192
--------------------------------------
600 580
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

Thank you since your first suggestion in placing the expression
in
query
worked as well as creating a separate field for the percent. How
would
you
create the expression if we need to subtract 6 percent from the
country
china
and 3 percent from country brazil? Can this be done on the same
report?

:

acss,
Perhaps I misunderstood...
When you wrote...
subtract the subtotal by 3 percent?
Did you mean that whatever the sum of InvAmt is, for a
Country,
subtract
3% from that value.
If so, then this formula would do that...

=Sum(InvAmt) * .97

200 * .97 = 194 (Brazil)

800 * .97 = 776 (china)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


I have the calculation on the report and it only works on
individual
records
per account. So if there is more than one record the
calculation
is
incorrect. Could this be a field in query not allowing for the
calculation
to
work on a group?

:

acss,
Add a calculated field to your query.
3Pct : InvAmt * .03
Add [3Pct] to your report to the right of your InvAmt
(can be hidden)
InvAmt 3Pct
brazil 25001 $100 3.00
25001 $100 3.00
200 =[Sum(InvAmt) - Sum(3Pct)]
=
6.00
china 25002 $400 12.00
25002 $400 12.00
$800 =[Sum(InvAmt) - Sum(3Pct)] =
24.00

This calculation will work in any group or report footer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day
in
your
life."




I have a country report which group two accounts and
provides a
subtotal
for
each account per country. How do i create another field
that
would
subtract
the subtotal by 3 percent? For example:

brazil 25001 $100
25001 $100
[SumOfInvAmt $200] subtract ths amt by 3%
china 25002 $400
25002 $400
[ SumOfInvAmt $800] subtract this amt by 3%