Re: Percent on sum total

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

"Al Campagna" wrote:

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."

"acss" <joekru98@xxxxxxxxxxx> wrote in message
news:B266841C-5EF3-4283-AB72-1A89D8B2E5EA@xxxxxxxxxxxxxxxx
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
"Al Campagna" wrote:

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."


"acss" <joekru98@xxxxxxxxxxx> wrote in message
news:03BE6C24-E74B-45FC-8B6C-FFB75986A588@xxxxxxxxxxxxxxxx
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

"Al Campagna" wrote:

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."

"acss" <joekru98@xxxxxxxxxxx> wrote in message
news:514A1C1A-0E10-4405-9FFE-2FEA01154E72@xxxxxxxxxxxxxxxx
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?

"Al Campagna" wrote:

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."


"acss" <joekru98@xxxxxxxxxxx> wrote in message
news:DC739C3B-2447-4CF6-90B0-43E435FC586A@xxxxxxxxxxxxxxxx
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?

"Al Campagna" wrote:

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" <joekru98@xxxxxxxxxxx> wrote in message
news:4A6E1A65-6ECE-4965-81A5-B3DD4535BE7B@xxxxxxxxxxxxxxxx
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%















.

Relevant Pages

  • Re: Percent on sum total
    ... The problem is that the report will be run from data that already exists ... formula to some how filter one country from the other on the report. ... Then just multiply InvAmt by that value to get what you requested... ... then subtract that value from InvAmt.) ...
    (microsoft.public.access.reports)
  • Re: Percent on sum total
    ... The problem is that the report will be run from data that already exists ... formula to some how filter one country from the other on the report. ... then subtract that value from InvAmt.) ... Now you're beginning to see why doing a Rate calculation for each ...
    (microsoft.public.access.reports)
  • Re: Hey TOD! [was Re: Hey Kulczycki!]
    ... via out of country blogs anyway - most especially through the Captain's Quarters blog but others picked it up. ... What Gomery says, in his report when he hands it down, is gonna make or break our government. ... When's the last time you tuned in to a Canuck TV channel or radio station carrying Canuck news? ... My dad believed in the American system - despite the fact he fought for another country in WWII -- he believed in the *President*, whoever it was, whatever party, at whatever time. ...
    (misc.writing)
  • Re: Percent on sum total
    ... the report section (ex. ... I'll assume that the Peru data prints in the Detail section of the ... If Country = "Peru" Then ... In whatever table you capture the InvAmt, ...
    (microsoft.public.access.reports)
  • Re: OT - Must Read
    ... are you aware of the Butler report? ... The British Government has learned that Saddam Hussein recently sought ... > my country by anyone or it's citizens. ... I choose my country and it's citizens over ...
    (rec.outdoors.rv-travel)