Results 1 to 7 of 7
  1. #1
    Newcomer is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3

    Reference table

    I'm getting a headache trying to figure this out, so I'm hoping someone can help me here.



    I have commission that I need to pay out to salespeople. Each salesperson has his/her own unique rep number. At the same time, there are also rep numbers that are shared by two or three salesperson. Is there anywhere I can have a lookup table to split the commission that's associated with a shared rep number so that access know to allocate 50% of the commission to a none shared rep number and 50% to another?

    Thanks.

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Assuming you already have a table (tblRep) that lists your rep numbers with the salespeople? Something like:

    Code:
    Rep	SalesPerson
    123	Jones
    345	Smith
    785	Wallace
    345	Williams
    785	Kent
    785	Freeman
    677	Loblaw
    If so, then build a query like:

    Code:
    SELECT Rep, COUNT(SalesPerson) AS Coconuts
    FROM tblRep
    GROUP BY Rep
    This should give you a result of:

    Code:
    Rep	Coconuts
    123	1
    345	2
    785	3
    677	1
    From this, you can use:

    Code:
    Share: [Commission]/[Coconuts]
    to distribute your commission.

    Cheers,

  3. #3
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    That idea works, but from a design standpoint, you would want to separate your uniques from your shared. Give each salesperson his/her own ID and then for those that you haves shared IDs, instead create another table called Groups and add a foreign key to Employee for whatever group they are in. You can then do your shared comission calculation based off of the groups instead of essentially creating them on the fly.

  4. #4
    Newcomer is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3

    Still searching for a solution

    ConneXionLost,

    That idea will probably work if the split is always equal based on the number of people number a rep number. I should have been more clear. the split of commisison is not always equal.

    For Example:

    Name RepNum
    Salesperson A 123
    Salesperson B 456
    Salesperson A & B 789

    Transaction showed commission of $1,000.00 is booked number RepNum 789 under tbltransaction.

    The split for RepNum 789 is not always 50/50. It might be 40% Salesperson A and 60% Salesperson B or it can be 30/70, 20/80 and so on.

    I need some sort of reference table to indicate that if commission booked number RepNum 789, then split 50% to Salesperson A and 50% to Salesperson B, or 40% to A 60% to B, ...


    TheShabz,

    I agree from design standard point of view, it's better to have shared RepNum on one table and non-shared RepNum on the other. However I'm not entirely sure how the calculation will work. If you can elaborate, it'll be helpful.

    Thanks.

  5. #5
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Will the breakdown in each group be the same always?
    for example, we have groupA with rep1 and rep2
    If the group is broken down to rep1 given 60% and rep2 40%, will that always be the case when groupA has a sale? If so, you can do it. If not, I have no clue.

    If so, here's how I would do it.

    GroupID (PK)
    RepID (PK)
    CommPerc

    Code:
    GroupID RepID CommPerc
    GroupA Rep1 60
    GroupA Rep2 40
    to calculate commission,
    comm=totalcomm/100*commPerc

  6. #6
    Newcomer is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    3
    TheShabz,

    I guess I can make it the same always. If there are any changes to the percentage, I can also create new RepNum so it retain historical integrality. However, using your method will create too much tables since each shared RepNum will basically be a group due to the different percentage split. Again, what I'm trying to accomplish is the following:

    tblRepNum tblShareRepNum
    RepNum Name
    123 John A
    456 Chris B
    321 Luke C
    654 Nick D

    tblShareRepNum
    ShRepNum RepNum Percentage
    789 123 40%
    789 456 60%
    987 321 20%
    987 654 80%

    tbltransactions
    RepNum Commission
    123 $500.00
    456 $800.00
    789 $1,000.00
    321 $200.00
    654 $400.00
    987 $100.00

    When I run a qry, I want the qry to be able to tell me that:

    RepNum Total Commission
    123 $500.00
    123 $400.00 (40% of $1,000.00 from ShRepNum)
    456 $800.00
    456 $600.00 (60% of $1,000.00 from ShRepNum)
    321 $200.00
    321 $20.00 (20% of $100.00 from ShRepNum)
    654 $400.00
    654 $80.00 (80% of $100.00 from ShRepNum)

    If it's not possible using what's avaliable in Access, then I might have to move to the next level and use VBA which will take me a while to learn.

    Thanks.
    Last edited by Newcomer; 06-03-2010 at 02:01 PM. Reason: break table into sections

  7. #7
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I'm pretty sure it will be done with VBA regardless. What I was going with is that you create the 3 tables as you have, and then run the calculations in the report. Basically, you pull the from your sales table and, assuming there is a way for you to identify a group RepNum just by looking at it (>x for example) to go grab the reps from that group table and assign each a share of the total commission. I'm not the best with VBA so I probably wont be able to code it for you but I think thats a viable way to go about doing what you need done.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reference to a Query
    By starhannes in forum Forms
    Replies: 5
    Last Post: 05-09-2010, 02:53 PM
  2. Replies: 2
    Last Post: 05-09-2010, 04:10 AM
  3. Replies: 0
    Last Post: 08-01-2009, 12:43 PM
  4. Form Name Reference in VBA
    By Simon Sweet in forum Programming
    Replies: 0
    Last Post: 05-22-2008, 01:55 PM
  5. How to add Opensource reference???
    By loui in forum Access
    Replies: 0
    Last Post: 10-04-2007, 04:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums