Hi there,
I hope this is simple for someone. I always figure things out myself, but I thought it would be real nice to see if anyone else out there thought this was super simple and knew the answer. Here goes:
I am working on an Access report that details individual sales by month, then sums the total at the bottom (I have all this part done), and based on the sum, uses the appropriate percentage rate to calculate the commission. I hope I am making sense.
Here is the pay rate based on total sales.
0 – 499K = .03%
500k – 999K = .0325%
1M – 1.9M = .035%
2M – 2.9M = .0375%
3M+ = .04%
How do I write a query formula that looks at the total sales and based on the total sales number uses the correct rate to calculate the commission?
Hope this sounds fun for someone. Probably old as the hills.
Thank you and regards,
pollyanna
33  2572  NeoPa 32,579
Recognized Expert Moderator MVP
You have two options it seems to me : - Do a complicated IIf() structure (with only 5 levels it wouldn't be THAT complicated).
- Create a table with the structure in it and link to that table.
Do you need more details on how to do this? Is this simply an intellectual exercise?
If you want a fleshing out of either idea please respond with what you need.
MMcCarthy 14,534
Recognized Expert Moderator MVP
Something like ...
=Format(IIf(Sum ([SalesField])<500000, 0.03, IIf(Sum([SalesField])>=500000 and Sum(([SalesField])<1000000, 0.0325, IIf(Sum([SalesField])>=1000000 and Sum(([SalesField])<2000000, 0.035, IIf(Sum([SalesField])>=2000000 and Sum(([SalesField])<3000000, 0.075, IIf(Sum([SalesField])>=3000000, 0.04)))))/100, "##0.0## %")
You have two options it seems to me :- Do a complicated IIf() structure (with only 5 levels it wouldn't be THAT complicated).
- Create a table with the structure in it and link to that table.
Do you need more details on how to do this? Is this simply an intellectual exercise?
If you want a fleshing out of either idea please respond with what you need.
Greetings! Thank you.
I am inclined to pick the "complicate d IIf() structure (with only 5 levels it wouldn't be THAT complicated) option."
This is what I was tryin got do own my own, but I didn't know how to structure the levels.
Everything is an intellectual exercise. :) It is also a little road block in the sales report I am making.
Yes, please. I would like help.
Something like ...
=Format(IIf(Sum ([SalesField])<500000, 0.03, IIf(Sum([SalesField])>=500000 and Sum(([SalesField])<1000000, 0.0325, IIf(Sum([SalesField])>=1000000 and Sum(([SalesField])<2000000, 0.035, IIf(Sum([SalesField])>=2000000 and Sum(([SalesField])<3000000, 0.075, IIf(Sum([SalesField])>=3000000, 0.04)))))/100, "##0.0## %")
Thank you very much! This looks like what flashed through my mind, but I have no yet down coded the details.
In Access, were is the best place to put such a formula? I currentley intend to create it in a field in the report based query, and then just add it to the report design layout.
MMcCarthy 14,534
Recognized Expert Moderator MVP
Thank you very much! This looks like what flashed through my mind, but I have no yet down coded the details.
In Access, were is the best place to put such a formula? I currentley intend to create it in a field in the report based query, and then just add it to the report design layout.
I wouldn't do that, just calculate it dynamically on the report. This is designed to work in the control source of the textbox.
=Format(IIf(Sum ([Billing amnt])<500000, 0.03, IIf(Sum([Billing amnt])>=500000 and Sum(([Billing amnt])<1000000, 0.0325, IIf(Sum([Billing amnt])>=1000000 and Sum(([Billing amnt])<2000000, 0.035, IIf(Sum([Billing amnt])>=2000000 and Sum(([Billing amnt])<3000000, 0.075, IIf(Sum([Billing amnt])>=3000000, 0.04)))))/100, "##0.0## %")
Does there not need to be a calculation between the sum variables and and the relavant percentage variables? Something like
=Format([if(Sum([Billing amnt])<500000, then sum=[Billing amnt]*0.03
I wouldn't do that, just calculate it dynamically on the report. This is designed to work in the control source of the textbox.
Ok. How do I do that?
Ok. How do I do that?
I can see where to put the formula. When I right click and go to preference for the text box, I select the data tab and make my entry in the control souce box. That is where I put this formula I am wanting. Right?
MMcCarthy 14,534
Recognized Expert Moderator MVP
I can see where to put the formula. When I right click and go to preference for the text box, I select the data tab and make my entry in the control souce box. That is where I put this formula I am wanting. Right?
Thats right Pollyanna.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: AMDRIT |
last post by:
The stuff below seems to work, when I am applying the logic for suppression.
However, it doesn't work when suppressing row data. Any Ideas?
Basically, if there is only one row of data, I need to suppress a caption
and it corresponding data field.
Create a formula field
@RowCount Formula Field
|
by: Terry |
last post by:
I have a form which displays data from both the Student Details table
and the Exam Details table.
The Name (Text Box) control is from the Student Details table and the
Withdrawn (Check Box) control is from the Exam Details table.
I want create an expression, using Conditional Formatting, to change
the background colour of the Name (text Box)in the form when the
Withdrawn (Check Box) is True.
|
by: RR |
last post by:
I am wondering what tables and relationships I would have to have to keep
track of one or several employees commissions per transaction.
One tracsaction can have one or several employees work on it, and the
commissions for each employee depends on how many employees worked on it.
Thanks
|
by: Syd |
last post by:
The problem i have been asked to solve involves calculating commissions
for a multi level marketing company.
There are several products SAY A B C D and several Associates (or
sellers).
The first level of commission has been established. It involves 5
levels of commission. If # of Products sold is about "x" then
commission = level 1 for that product.... all the way up to level 5....
This occurs for EVERY product.
|
by: JC |
last post by:
I am trying to conditionally print a field on a report based on the
value of another field in the query that the report is based on.
In the Control Source for SerialB I have typed:
= IIF(nz(QtyB) > 0," ",SerialB)
On preview of the report I get #Error in that field every time QtyB is
NOT greater than 0.
If I change the code to read:
= IIF(nz(QtyB) > 0," ","9")
the blank shows up where it should and the 9 shows up where it should.
| | |
by: kapccoe |
last post by:
Hello
how can i adjust formula thru SQLServer?? for example --the computation Gross Salary less Deduction then the total will be for computing of witholdingTax..
thanks pipz..
|
by: patrickahutah |
last post by:
I need some help on computing this formula especially the best way to capture the data that is whether to use excel or ms access, then with the data compute the formula. The excercise is for bonus scheme calculation which is depended on various performance indicators.
Global Incentive (GI)
General Formula
GI = {(By-1) * (P/N)} + {X * (COMa-COMm) * (WRpa+UFWpa+CEpa+MRpa+TApa)
GI = Global Incentive that relate to the entire company
By-1...
|
by: Barry L. Bond |
last post by:
Greetings!
I just got a new Peet Brothers Ultimeter 2100 Weather Station. This
new one has a way to display the heat index, if you press the "dew point"
key twice.
Being aware of all the controversy surrounding the exact calculation
of the heat index, I would like my own software (which I programmed in the
1990's, when I got their Ultimeter 2000 weather station, but that one
didn't show heat index) to display it. Living in Florida,...
|
by: menkenk |
last post by:
Hello,
I have a query that I used to calculate the monthly, quarterly, semi-annual, annual commissions by agent. Below is the SQL view of this Query.
SELECT ., .Plan, .Placed, .Monthly, (-7.65)* AS , .Quarterly, (-22.53)* AS , ., (-44.2)* AS , .Annual, (-85)* AS , .CTP, (*) AS
FROM INNER JOIN ON .AgentID = .Agent
WHERE (((Format())=) AND ((Format(,"mm/yyyy")) Between And ));
The issue I have is that under the PLAN field, we have a UL...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
