Sale + Commission Query + Subform
Sep 11, 2005
sales target table =
allows collection of employee sales figures in order to work out commission. Fields included are target, date, sales achived and commission allocation %. This info will be entered via a query below.
i need to create a query that allows entry of employees sales targets, sales achived, commission % and calculates the amount of commission due.
I will have an employee form where I want a subform to allow entry & calculations of employee commission.
How do I do all this? Please help
View Replies
ADVERTISEMENT
Nov 16, 2005
I have a question I need help for, below is a very strict detailed explination of the current scenario I am in and I need assistance for it:
1) I have a percentage of Commission I need to calculate, the percentage of commission constitudes of the following information:
a) Date,
b) Customer Name,
c) Company,
d) Name,
e) 5% New,
f) 3% Ext,
g) Rep,
h) Doble Ref (0.025, 0.015),
i) Total Amount, and
j) Total of Commission.
If a person enters the same customer name more than ones, it will give that person's name a Doble ref (0.015) of commission, if on the other hand that customer is new it will give that person a commision of 0.05% new. I hope this is not intending to confuse you, it just gives the general idea of what the percentage of commission is intended to be.
To come back to the point, I have a form created under Access that holds all the names of the person's reference by in that case "Name" letter (d) described above. If I select for example a person name "Fahed" and I click on the button calculate new commission I want to be able to draw all the commissions that Fahed have done and then use the form above to do the approriate calculation.
The above is a general problem, below is the scenario:
I get a form called New Client, the form New Client is a parent form and underneath that form is a sub form which holds estimates, invoices and so on. Ones again I am giving you the picture of what the form looks like, I cannot paste the pictures here because they would take time and would not be clear. The new Client has a text field called Reference By. That text field is where I need to get for the Percentage of Commission form to be entered under the field Name, letter D (described above). The total of amount is extracted from the subform Invoice, total, of the parent form called New Client, and there is a check mark in the new client called "Already existed" that if I click it, that information is then send on a separate form called "PercentageOfCommission" that will determine if it calculates the percentag of 0.05 or 0.025 depending if it is Calculate Commission or calculate existing commission.
I want it to be able then to do the needed calculation which I already have the approriate formula for and then display in a report what is shown above.
I don't know how to get information from different forms, and I don't know how to display for example ALL FAHEDS with information but are related only to FAHED.
Ones again, let me give you an example:
Date Name Info
15-Feb 05 Fahed Stonyx
15-Mar 05 Fahed Commodore
...
18-Sep 05 Ahmed Apple IIc
I want to display only one name Fahed and when I click for example display summary to show ALLLL the name that holds Fahed that have different Info. Any help would be greatly appreciate it.
If you feel what is posted here is TOO confusing and trying to explain it on the messageboard might take time and you wish to assist a poor soul in need, then you can contact me at devastating_battle@hotmail.com so we can talk live in MSN to be able to describe the matter much clearly. Please don't ignore me and leave me hanging in the air dry, thanks in advance.
View 1 Replies
View Related
Nov 3, 2004
Hi all. I am looking for a formula to calculate a commission, based on the total sales price. The commission is a sliding scale. Ex. if sale price is 200 or less, commission = 30%; if sale price is 201-500, commission = 25% on 201-500 & 30% of 1st 200; if sale price is 501-1000, commission = 20% on 501-1000, 25% of 201-500 & 30% of 1st 200; if sale price is 1001+, commission = 15% on 1001+, 20% on 501-1000, 25% on 201-500, & 30% on 1st 200. Does that make sense? In other words, if sale price is 300, I want commission to be 30% of 200 + 25% of 100, which = 85. Is there a way to write this in one formula?
View 4 Replies
View Related
Feb 27, 2012
I'm creating an EPOS system in Access 2010, I know how to lay out the UI (roughly!), but I don't know how to set out programming it. How would I do this?
View 1 Replies
View Related
Nov 2, 2014
I have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#31/07/2014#"),0)
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#" & [forms!salesm!sdate] & "#"),0)
But it is not working
Sdate is date and it is on main form
View 6 Replies
View Related
Sep 16, 2014
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
View 7 Replies
View Related
Apr 25, 2005
I'm relatively new to visual basic, and not sure how to do this one, was wondering if anyone can help.
If you can take a look at this screenshot
http://homepage.ntlworld.com/glenn_cassidy/ole0.bmp
Basically, what I'm looking to do is, when someone clicks on either command button, then the unbound subform will display a datasheet of the table in which the information is looked.
I have a table and ran 2 query's on the table.. One for showing all records with the department "Admin" and one to show all records with the department "Collections".
So when clicking on the command buttons, it shows these query's in the subform.
Was wondering what code to put behind the buttons to do this. Or even if there is any easier way to do what I'm looking for.. I will have about 10 departments, and each button will run a query, is this the best way to do this. ?
View 2 Replies
View Related
May 19, 2005
Is there anyway of clicking a button on the form to view the subform instead of it being there all the time .
View 1 Replies
View Related
Jun 14, 2005
Hi
I have a MainScreen Form in my Database which links to a subform giving a note of the Updates on the account. I have set this up as a Single Form with Navigation Buttons to move through these. All of this is fine except whenever you open an account it always shows the first update for the account.
Is there any way to change this so that the Navigation starts on the last account rather than the first. I know I could filter this but then you could not just flick back to look at previous updates.
Any ideas?
JC3
View 3 Replies
View Related
Nov 8, 2005
Dear List,
I've made a subform which is based on a query. One of the fields from this "Many" table is "Product Type", and the query asks to list all the product types except "Delivery" (in other words, 'not "Delivery"'). I don't want to see the Delivery items in this list.
The trouble is, the subform no longer has the blank row with the asterisk which allows you to add a new record into it.
I really need this subform to take new records.
What should I do?
Mrs Miggins.
View 14 Replies
View Related
Nov 22, 2005
I need to have a subform query embedded in my main form that when the user selects an item from a drop down box it will display the query results in the sub form. I have my query set up to work off of this drop down box I just can't get it to update on the subform when selected. Any ideas?
View 13 Replies
View Related
Aug 9, 2006
I am making a form that will be used to enter info, then spit out an employee's pay for the time period. On the form are some goals or qualifications (I am assuming I will have to make queries for those but thats another story) that, if reached, bonus' will be added to final pay. The form will look up records for a specific date or time period for an individual and determine the amount of the bonus. I was wondering how to get a form to show the records in a subform like view. In other words, I want the user to be able to select say, phone calls made on the 10th of last month, then on the right hand side that record would show up just so he could see. (this is more beneficial when there is a date range but for ease sake I am going with one date right now) I am thinking it is a subform/query but am not sure and would like some help b4 I start wasting time on the wrong thing.
View 6 Replies
View Related
Nov 29, 2006
I have a frmCustomer as a main form with a subformOrders as a sub form.
I now want to add another subform that is based on a query I build. I only want that subform to refresh (update totals) when data is entered in the subformOrders fields. I am trying to use the TimeInterval, I created a macro that just Requery. But soon as I click in the subFormOrders field, It starts to do the refresh.
Any Idea's
View 2 Replies
View Related
May 3, 2007
Another one I'm struggling with.
I have a mainform and a subform. What I want to achive is :
click on a lable (lab1) in the mainform and set the recordsource ( query1 ) in the subform to display relevant data.
Click on a lable ( lab2) in the mainform and set the recordsouce (query2) having different data than query1 in the subform.
Hope this make sense.
How would I do this.
View 2 Replies
View Related
Mar 28, 2005
Hi all :D
Background:
I have a few tables, (products, Customers, Backorders, NewCustOrders)
And 2 main forms using a couple of subforms. One of the main forms has 2 tabs; Tab 1 shows current products list, Tab 2 shows back order list.
The other Main Form has 3 Tabs; tab 1 has the customer details, (that part is fine), Tab 2 has a couple of fields used from the first tab and a subform which is ment to be the customers order., Tab 3 is ment to show customers order history.
On tab 2, new order subform
I have 6 fields, ProdName, ProdSize, UsebyDate, ProdQuantity, Prod-R-Price, TotalCost.
Prodname and prodsize are combobox's
Totalcost is calculated from prod-r-price*ProdQuantity.
Main problem:
1)
I am trying to get the prodName to list each product Once only, not once for each entry in the table. How do i filter the combobox so it will only show one product name, even if there are multiple entries in the products table.
2)
Trying to filter the prodsize to only show available sizes of selected product - (From first question). not every size in the table as it currently shows.
This is driving me Nuts, i have looked for the last 4 months in over 60 books, allover the web, and asked anybody who has had anything to do with access. NO ONE KNOWS THE ANSWER!!!!!!!!!! :eek: :eek:
Thanks
Peter
View 1 Replies
View Related
Apr 27, 2005
Hi Folks
Attached is an example of what I'm looking to do.. I'm new to VB code and not great on queries either.
I'm looking to simply choose the department from the dropdown combo box on the form and the subform above it to update with showing all the results containing the department from the selection.
Not sure on the best way to do this, either via a query or from the table directly, or even some way to use the drop down to filter the table being displayed on the subform.
Anyone got any ideas. Thanks
View 2 Replies
View Related
Feb 4, 2006
Hello,
I wonder if anyone can help me I am using a select query as a subform, but it wont let me make any additions or changes. I have checked the properties of the subform and allow edits and allow additions are set to true.
Any ideas? Thanks in advance.
Regards
Nathan
View 4 Replies
View Related
Mar 2, 2006
Hi everyone
I've seen a number of threads here, as well as elsewhere, which suggest that it's good practice to use a query to provide data to a subform, rather than placing data directly from the table.
I can understand the need for this if a special function of the query is needed, such as selecting certain records, performing calculations with some fields, reordering the information etc., but what if the data is to be transferred without any such manipulation? Is it still better practice to put the data through a query (that simply takes all fields from the table and doesn't modify anything) or take it directly from the table to the subform?
My "gut" feeling is that it wastes time (albeit a fraction of a second!) to have an extra step between the table and subform and, I suppose, another step where something could go wrong.
Is my logic correct, or is it ALWAYS good practice to use a query to supply data to a subform. If the latter, I will change my practice and use more queries.
Thanks for your responses.
View 2 Replies
View Related
Oct 1, 2006
I'm a beginner of MSAccess programming,...hi anybody here who can help me or suggest regarding on my project where in my subform display as datasheetview act as detail record display wherein when I enter a number in my main form it searches in the record of detail table, if the number is found in detail table, it displays the all records related to that number in subform, and I want that I can edit or that records being displayed in subform...in other words my subform works as grid ..
Thanks
View 1 Replies
View Related
Oct 26, 2006
After a scary recovery I have a new problem: Intially I changed the 'Data Entry' property on a subform, in a multi-tab form :eek: My tabs all dissappeared. (My Bad!:o )
Now that I have recovered I get a message when I close the form, asking do I want to save the changes to the design of the query.
This only seems to happen when I have read a second or third etc record that displays a different set of records in the subform.
This is a HR mdb with leave records in the subform.
Any help would be appreciated.
PS I thought I had posted this 2 hrs earlier, but can't seem to find it. Hoping this isn't a second posting ...:confused:
.... added an image of error
View 1 Replies
View Related
Sep 11, 2013
Working with the attached mdb.
I am trying to search inside the subform.
First you should search for a "user", e g: a
On the second textbox, for a serial number: m1
Using a second query, I would like to search the input from the secondbox (Like I did with the first textbox).
Problem is that at the properties of the subform1 I was able to assign only one Source.
How can I add a second one? So it runs the second Query too.
View 8 Replies
View Related
May 9, 2005
This is a new post for this same question. The database that I am working on is set up in a very complicated way to me. The database tracks grant applications and awards. Right now there is a section in the database where one can enter a new record (Well this record is imported from an excel file filled in by the person who wants to update a current application or award for a grant) So when the amount changes on a grant a new record is created but the old information needs to be kept for future references. So this information is keyed into a subform on the same form as the new record. This is quite a bit of information, so I want to know if a button can be created to export this single record to the archive file set up on the same form (same format--different color fields- subform) Basically, I would also like to create a pop up question to say "Do you want to archive this record" the answer would be yes and the record would go to the archive subform (which is our archive query) I hope this makes sense. Thanks!
View 1 Replies
View Related
Sep 4, 2005
Hi,
Is there a simple way to add optional criteria in a query for a subform? Basically, what I'm trying to do is have a number of comboboxes in the parent form that indicate what records are shown in the subform (these records could then be added and/or deleted). I would like to be able to have a combobox such as "Month" that would filter the data shown in the subform to that month. If no month is selected, then the subform would show all months.
Any help would be much appreciated.
View 2 Replies
View Related
May 2, 2006
I have a subform that is populated by query results. In the query i have a field called Discussion that is just a free form field where the user can input any information. On my subform the field is not big enough to show all of the data if it gets large. Is there a way to add a scroll bar to this field on my qery so it will appear on the subform so the user can scroll down if necessary to see more of the text in this field?
View 2 Replies
View Related
Dec 20, 2007
How do you pass a primary key to an append query from a subform?
Example:
Main form Table Subscriber (subscriber-id primary key)
Linked (subscriber-id (main form) = ot-subscriber-id (subform))
One to many
Sub form - Other residents (ot-othres-id primary key)
From sub form append history record using primary key by query
With in query criteria [forms]![other residents subform]![ot-othres-id]
Does not work The primary key isnt being passed which shows in the subform. Pop up box appears.
View 12 Replies
View Related
Jun 21, 2005
I have this form where there's mention of CompanyID
In a subform, I have the information about all the contacts of this Company. In order to see full detail information for a particular contact (of that particular Company) I have a Combobox with a query.
At least: that's the whole idea. Unfortunately it doesn't work in Access:
SELECT Contact.ContactID, Contact.Name
FROM Contact
WHERE ((Contact.CompanyID)=(Me!frmCompany![ContactID]));
Can any help me? I need to do more of the sorts of queries... and I can't figure it out how to use values from forms and parent-forms.
Any help is appreciated,
Jazz
View 1 Replies
View Related
