Append Multiple Rows based on One Criterion

[GSCaupling]

Using Office 365.

This is basically a commission scenario.

I have a Sales Table with:

SaleID

AgentID​

Product​

Price​

and a Commission Splits Table:

SplitID

SaleID​

AgentID​

Split%​

I can populate the Sales table without problem

The issue is populating the Splits Table. Depending on WHO made the sale (AgentID), one to three people may get a percentage of the sale. How can I append the Splits Table based on the AgentID to include these one-to-three records for Agents who get a piece of the action?

I know this is an Access forum, but if the solution involves creating a table in Excel and appending from there, that's okay. The Sales data is collected in Excel before being exported to Access. And PLEASE tell me this is possible without VBA!

Thank you!

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[dhookom]

It would help if you took the time to enter a few records and then provided the desired results.


Duane
Hook'D on Access
MS Access MVP

 

[GSCaupling]

You're right, Duane. Let me try again.

Example: The Tropical Fish Store rewards salespeople when they recruit other salespeople like this: You get 10% of the sales of your direct recruits and 5% of the sales of THEIR recruits. Rewards are only two generations deep, after which the initial recruiter falls off.

In my example, David recruited Keith, Keith recruited James, and James recruited both Bobby and Susan. David was a direct hire, so no one gets a percentage of his sales.

Sales table:
[pre]
SaleID AgentID Product Price
446 Bobby Goldfish $10.00
447 James Aquarium $79.00
448 Keith Filter $21.00
449 Bobby Fish Food $12.00
450 David Aquarium $99.00
451 Susan Angel Fish $18.00
[/pre]

Desired Splits Table
[pre]
SplitID SaleID AgentID Split%
1 446 James 10%
2 446 Keith 5%
3 447 Keith 10%
4 447 David 5%
5 448 David 10%
6 449 James 10%
7 449 Keith 5%
8 451 James 10%
[/pre]
For each of Bobby and James's sales, I need two records in the Splits table, while for Keith I need only one and none at all for David.

I want to automate the task of appending the Splits tables based on the AgentID in the Sales Table. Thank you so much for your help!

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[MajP]

So you could do this in SQL. But you need the recruit table. For flexibility lets say your commissions could be varied not everyone gets 10, and 5

Commissions:
AgentID
RecruiterID
PrimaryCommission
SecondaryCommission

AgentID RecruiterID PrimaryCommission SecondaryCommission
David                .1  
Keith  David         .1                .5
James  Keith         .1                .5
Bobby  James         .1                .5
Susan  James         .1                .5

So you can use a union query. In the first you would join on the AgentID, and the second on the recuriterID

Select
 Sales.SaleID,
 Sales.AgentID,
 Commission.PrimaryCommission as Split
From
 Sales
Inner Join
 Commissions
ON
 Sales.AgentID = Commissions.AgentID
UNION
Select
 Sales.SaleID,
 Sales.AgentID,
 Commission.SecondaryCommission as Split
From
 Sales
Inner Join
 Commissions
ON
 Sales.AgentID = Commissions.RecruiterID
ORDER BY
 2

 

[GSCaupling]

Thanks MajP! I'll study this tomorrow when I'm fresher, but I suspected I would need a Recruits Table.

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[MajP]

Sorry I was being a very nice boss and was giving the recruitee the commission on the recruiters sale, oppositte

UNION
Select
 Sales.SaleID,
 Sales.RecruiterID,
 Commission.SecondaryCommission as Split
From
 Sales
Inner Join
 Commissions
ON
 Sales.AgentID = Commissions.AgentID
ORDER BY
 2

So now that joins on the person who sold, but returns their recruiter.