Commissions/Receipts query

Hi Everyone,
I have a database that is storing commission structures (between these two 
dates, pay x % of revenue between y and z dollars to abc sales person), 
receipts from clients, client information, sales manager information, and 
sales division information.

Currently tables are linked as follows: 
sales division to client (linked by sales division id)
sales divisionn to sales manager (linked by sales division id)
client to commission (linked by client id)
client to receipts (linked by client id)
commission to sales manager (linked by client id)
** note that sales managers can move between divisions, but once a 
commission structure is set, that sales manager will be paid on that 
commission structure until the structure expires.

problem comes in when I am querying on the receipts... the receipt records 
are included in the query multiple times because of multiple commission 
records, even if the commission records aren't applicable.  I think I need to 
fix the joins somehow, but haven't been able to come up with anything.

Thanks!!!
0
Utf
12/12/2007 6:08:00 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
949 Views

Similar Articles

[PageSpeed] 10

add a restriction on the dates, something like:

WHERE receipts.dateOfSale >= commission.betweenThisDate  AND 
receipts.dateOfSale < commission.andThatDate



Hoping it may help,
Vanderghast, Access MVP


"BMoroneso" <BMoroneso@discussions.microsoft.com> wrote in message 
news:FD075333-FCAD-45D0-A925-BCBDB35BF726@microsoft.com...
> Hi Everyone,
> I have a database that is storing commission structures (between these two
> dates, pay x % of revenue between y and z dollars to abc sales person),
> receipts from clients, client information, sales manager information, and
> sales division information.
>
> Currently tables are linked as follows:
> sales division to client (linked by sales division id)
> sales divisionn to sales manager (linked by sales division id)
> client to commission (linked by client id)
> client to receipts (linked by client id)
> commission to sales manager (linked by client id)
> ** note that sales managers can move between divisions, but once a
> commission structure is set, that sales manager will be paid on that
> commission structure until the structure expires.
>
> problem comes in when I am querying on the receipts... the receipt records
> are included in the query multiple times because of multiple commission
> records, even if the commission records aren't applicable.  I think I need 
> to
> fix the joins somehow, but haven't been able to come up with anything.
>
> Thanks!!! 


0
Michel
12/12/2007 7:04:21 PM
Here's what I think I will do...
create another table with both the commission id and receipt id (effectively 
a many to many relationship between receipts and commissions.  then for each 
receipt I can assign which commission record(s) applies, and then also 
restrict the commission records based on the revenue receipts.
thanks!

"Michel Walsh" wrote:

> add a restriction on the dates, something like:
> 
> WHERE receipts.dateOfSale >= commission.betweenThisDate  AND 
> receipts.dateOfSale < commission.andThatDate
> 
> 
> 
> Hoping it may help,
> Vanderghast, Access MVP
> 
> 
> "BMoroneso" <BMoroneso@discussions.microsoft.com> wrote in message 
> news:FD075333-FCAD-45D0-A925-BCBDB35BF726@microsoft.com...
> > Hi Everyone,
> > I have a database that is storing commission structures (between these two
> > dates, pay x % of revenue between y and z dollars to abc sales person),
> > receipts from clients, client information, sales manager information, and
> > sales division information.
> >
> > Currently tables are linked as follows:
> > sales division to client (linked by sales division id)
> > sales divisionn to sales manager (linked by sales division id)
> > client to commission (linked by client id)
> > client to receipts (linked by client id)
> > commission to sales manager (linked by client id)
> > ** note that sales managers can move between divisions, but once a
> > commission structure is set, that sales manager will be paid on that
> > commission structure until the structure expires.
> >
> > problem comes in when I am querying on the receipts... the receipt records
> > are included in the query multiple times because of multiple commission
> > records, even if the commission records aren't applicable.  I think I need 
> > to
> > fix the joins somehow, but haven't been able to come up with anything.
> >
> > Thanks!!! 
> 
> 
> 
0
Utf
12/12/2007 8:17:00 PM
Reply:

Similar Artilces:

Commission reports blank
I run a report and nothing comes up in commissions, sales rep is defined with 15% and still nothing Please help Paul, Have you checked the date sold on the filter? Good luck, Matt "Paul" <Paul@discussions.microsoft.com> wrote in message news:715A32E1-DE7A-4098-9CDD-574C27CB3001@microsoft.com... > I run a report and nothing comes up in commissions, sales rep is defined with > 15% > and still nothing > > Please help Yes I have "Paul" wrote: > I run a report and nothing comes up in commissions, sales rep is defined with > 15% > and st...

Excel 2007 & 2003 Data Queries Q
I hope below will make sense. I'm trying to create a Data Query from an External source (using Excel 2007). I've done this many times under Excel 2003. I've managed to pull in the data, but it appears as a table. My Excel 2003 queries were not pulled in like a table. Is this how 2007 does it now? Also when I want to refresh the data, I usually run below Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False But under the Table query in Excel I have to run below Range("I1").Select Selection.ListObject.QueryTable.Refresh Backgro...

Trying to include all values from 2 tables in Select Query - even where there is no match
I am pulling from a database (that is in very poor shape) trying to determine weekly hours out of a project accounting system with little luck. The problem is, I'm pulling each Friday and then have to subtract YTD Hours from Week 1 out of Week 2 (Current YTD - Previous YTD). The fields I have to work with are Name, Cost Center, and Cost Center Type. In my query I concatenate these values to create a unique field, so that I can link the tables together. Where I run into problems, I have 3 options...Show where they are the same, Show only from table 1, or show only from table 2. Often w...

Commission
Good day Does anyone use GP to work out their commission - How does this work? Nelia ...

Using a Web Query
Hello Everybody I have been trying to retrieve information from the Web by using a Web query, and then inserting the information into an active worksheet in Excel 2000. In the "Returning External Data to Microsoft Excel" dialog box I want to use the Parameters button, which is not active, Could anybody tell me how to activate the Parameters button before using the "Get the value from the following cell" option button? Thank you in advance. Hi. It is possible to supply parameters to a webquery. You can do it programmatically as described in this KB article: http:/...

Comments query
All my comments have shot off the screen, when I follow the line I find the box has shrunk. I cannot view any of my comments. This'll move 'em back to where they started. From Debra Dalgleish's site: http://www.contextures.com/xlcomments03.html#Reset Actually, Deb's code just moves them back. Here's a slight modification that'll autosize the comment, too: Option Explicit Sub ResetComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments cmt.Shape.Top = cmt.Parent.Top + 5 cmt.Shape.Left = _ cmt.Parent.Offset(0, 1).Left + 5 cmt.Shape.TextFram...

PROBLEM
I'm trying to get a report to load that is based on a criteria which is set according to the value of a listbox on a form. (all in 2007) When I open the report (by clicking on button) Access asks for the parmeter [which is the listbox.value] The report will run fine if I enter the correct value but I don't want it to ask. this is the format of the criteria in the query: [Forms]![frmMain]![lstPRRListing] The listbox does have a value when the report is opened and the form is never closed. Any thoughts on why the query can not see the listbox? Is the list...

commissions
Can commissions be divided among several sales reps on the pos screen at the time of the sale? -- billr Hit F2 in the rep column on the item line you want to change, then select the new rep for that item. Marc "billr3" <billr3@discussions.microsoft.com> wrote in message news:EAB6387A-8D97-4AE7-BA7B-9571FDD55737@microsoft.com... > Can commissions be divided among several sales reps on the pos screen at > the > time of the sale? > -- > billr ...

Count Query 02-12-10
I have a query with 5 fields [permit_type_txt], [age],[schoolsid_no],[sen_passenger],[schoolname_txt] Permit Type has 3 values, either "Season Ticket, Standard Ticket & Concession" There are approx 70 different schools I need to create a report that counts how many season tickets, standard, concession passes have been issued against each school. Is this possible? 1. In query design view, depress the Total button (upper sigma icon on the toolbar/ribbon.) Access adds a Total row to the query design grid. 2. Add [permit_type_txt] and [schoolsid_no] to the query d...

Experian query failing
For the past week or so whenever I try to update my credit score in the Credit Center via the free Experian account I opened when I bought M04 Small Business it fails. I know Experian had some problems on their site about a week ago, but it seems to be up now. Anyone else having this problem or know the cause or solution? - J. I am also having that problem. >-----Original Message----- >For the past week or so whenever I try to update my >credit score in the Credit Center via the free Experian >account I opened when I bought M04 Small Business it >fails. I know Exper...

Query- missing values- Nz function
Apologies for anyone who has just read my previous question, I have just read some infomation about Nz functions and so am re-phrasing my question to explain it better. In a query I am trying to create I have one field which is the date. For every date I have corresponding values for income 1, income 2 etc. For some dates there is no value for Income 2 for example, and so instead of just leaving a blank space, the query wizard is missing this date out completely. Is there a way I can format the fields so that if there is no value for it a '0' is displayed to ensure that the ...

Excel....query
Could anyone help me out, I am trying to convert excel column, into a database (as shown below) i.e... A Substance Misuse Service Telephone: 01466 794433 7 Castle Street, Huntly, Huntly, AB54 8BP Open: Monday - Friday: 9am - 5pm (2 ROW GAP) Substance Misuse Service Telephone: 01467 620660 8 North Street, Inverurie, AB51 4QR Open: Monday - Friday: 9am - 5pm (2 ROW GAP...THOUSANDS OF RECORDS ETC) I need to fill column B with the name of the organisation, Column C with the telephone number Column D with the address (more on which in a minute, and column E with the Details........

Cancel read receipt #2
Third attempt - thanks everyone, but I know how to turn read receipts off (which I've now done). The problem is that I sent a read receipt on a message which arrived via a mail service which doesn't support send (POP3 only, no SMTP service). Outlook 2003 is continually trying to send the receipt via the service on which it arrived (and therefore continually failing). As the message doesn't appear in Outbox or Sent Items - any ideas how I can get rid of it? ...

Need help building query
I'm not that familiar to Access and would appreciate some help building the following SQL query. I have two tables, and if two categories match within the tables, then I would like a third category entry updated to the first table. 1st Table: Working 2nd Table: Original Categories (columns) that should match between table 1 and 2: Pt Acct # Charge Cd If the entires for a line match, then update: Lawson # (take from Original table and enter into line for Working table) So, basically if the (Pt Acct #) AND (Charge Cd) in the (Original) table match the (Pt Acct #) AND (Charge Cd) in...

Web Query #6
Hi I've created a workbook that contains approx 75 sheets. Each sheet links to different web pages. I then have a Summary Sheet that links to the individual sheets to pull the pieces of information I need together. Heres the problem............. Sometimes, when I click the 'Refresh All' option, it'll whizz away doing its stuff, but on about 10 of the sheets, it doesnt pull through the right information. It actually pulls through information that I have set up on a different sheet. However, when I then go to that individual sheet to see what went wrong, I click on the &#...

Total paid in Commissions
On my Home Page I am informed, under investment performance, of the total I have paid in commissions in the current month. Is it possible to find out the total paid in commissions over a period, say 3 months, or a year? Any advice would be gratefully accepted! ...

Outlook multiple send query
Hi to the group, I am a relatively new outlook user (used to use OE) and am just wondering if it is a normal function of outlook that when you send a large Email e.g. one with a large attachment if outlook "splits" it into several sends. this seems to happen to me up to 25 sends will come up in send/receive details and I only have one mail in the outbox. TIA, RickO'Shay. ...

Trying to calculate a commission
I have variable payments for two components to a sales plan: # of customers maintained which pays a rep .05% # of customers added which pays a rep 1% I receive a report each quarter telling me the number of current customers around the world. I have a spreadsheet that has the number of customers by continent at the beginning of the quarter (in column b) and at the end of the quarter (in column c). For instance, if the title "# of customers" is in cell A1: # of customers 1/1/10 4/1/10 North America 15000 16000 South America 500 400 ...

Complex Query ?
I have a table with 2 fields, Order and Zone. There are many orders but there is only 7 zone types, A, B, C, D, F, J, N. In this table I only have the orders that have 2 zones on them, so each order# will appear twice with its respective zone. How do I write a query or queries that will show me the percentage of which zones appear together more often on orders? Example, 40% of all orders have zone A and B, 20% of orders have zone J & F, etc. Thanks, Sean SELECT zone, COUNT(*) AS howMany FROM tableName GROUP BY zone would return the number of records, per zone. To add th...

Help with query
if field1 is greater than zero and less than 1 than 1 otherwise 0 Whats the right way to write that expression? QASPEC, NewCalculatedField: IIF([Field1] > 0 and [Field1] < 1, 1, 0) HTH, Conan "Qaspec" <Qaspec@discussions.microsoft.com> wrote in message news:7E8E2C7E-F5A6-417F-A2EF-1E832CBC0F1D@microsoft.com... > if field1 is greater than zero and less than 1 than 1 otherwise 0 > > Whats the right way to write that expression? > ...

Sub Queries
Hello, Does anyone know if it is possible to query within the results set of another query using MSCRM V3? So a user can create an advanced find, then the user queries from the results displayed from the search? Like a nested select. Thanks Jo Using Advanced Find, every line you add to a query narrows your results set further. So, for example, if you do an Advanced Find for Contacts where Owner | Equals | Bob Smith Email Address | Contains Data and this returns 50 contacts, if you add another line, such as Status | Equals | Active you will in effect be searching within your result...

Send query data to excel using ADO
I'm trying to build a worksheet with several tabs that each show the result of a query. This code works when I use a table name, but does not work when I use a query. Do I need to code this differently for a query? Public Sub AcquisitionReport() Set xlApp = New Excel.Application Set appworkbook = xlApp.Workbooks.Add Set appWorkSheet1 = appworkbook.Worksheets(1) appworkbook.Worksheets.Add appworkbook.Worksheets.Add xlApp.Visible = True appworkbook.Worksheets(1).Name = "AMDS" appworkbook.Worksheets(2).Name = "IMA" appworkbook.Works...

Pick from list in query
I have a form called Shop Work Order with two tab controls, the first tab has two subforms on it called Labour & Parts. On the second tab I want to associate a service request with the shop work order, the service requested is entered into a seperate table. I am needing to select certain service requests from the query I have created from the service request table. I am needing to link the shop work order ID to the service request also. Is this possible? If this is not clear enough I can try and explain better if need be. ...

Query search
I download a list of information onto a table, and i'd like to make a search on that item. I put in "*" & [Search Item] & "*" into the item field with the wildcards just in case. This works fine. Now lets say I'm looking up cars, so in the search input box that pops run when I run the query I type in dodge and that brings backs dodge matches. If I want to look up red dodges do I put like "red" and "*" & [Search Item] & "*" or "red" &"*" & [Search Item] & "*" ? I've trie...

Emailing from a query 07-06-07
I need to send emails from an access database, but I want to send them from a query so that I only email the cetian people. Access says that it can't send an email from a query. Any ideas? Sorry, I figured out the problem. "William5271" wrote: > I need to send emails from an access database, but I want to send them from a > query so that I only email the cetian people. Access says that it can't send > an email from a query. > > Any ideas? On Wed, 12 Sep 2007 10:52:03 -0700, lydia wrote: > So how did you do it. I'd love to know. > > &...