Design Assistance

[Anonymous]

I am not sure how to approach my report.  My 2 tables show sales invoice line items in one and rep % earned in another.  They are linked by document number.  My data looks like this:

		Sales Invoice Line Table
					What should happen:
Document#	Sales rep	Customer	Product	Sales
101	1	1	22		Rep1 get 100%
101	1	1	33		Rep1 get 100%
102	1	2	22		Rep1 gets 50% and Rep7 gets 50%   No record in Sales Invoice Line for rep 7
102	1	2	44		Rep1 gets 50% and Rep7 gets 50%   No record in Sales Invoice Line for rep 8
103	2	10	33		Rep2 gets 100% no record in sales record table defaults to 100%
104	2	22	22		Rep2 gets 100% no record in sales record table defaults to 100%
105	3	13	44		Rep3 gets 50% and Rep7 gets 50%  No record in Sales Invoice Line for rep 7
105	3	13	22		Rep3 gets 50% and Rep7 gets 50%  No record in Sales Invoice Line for rep 8
		Sales Record Table
Document	SalesRep	Margin
101	1	100
102	1	50
102	7	50
105	3	50
105	7	50

There is only one record for the sale in the invoice sales line for a rep but we need to split the margin to two reps.

Going thru the sales invoice table, I miss the splits.  

What is the best way to approach this?

[v-diye-msft]

Hi @Anonymous 

Not quite understand your logic.

How can we get the below red info?

Document#	Sales rep	Customer	Product	Sales
101	1	1	22		Rep1 get 100%
101	1	1	33		Rep1 get 100%
102	1	2	22		Rep1 gets 50% and Rep7 gets 50%   No record in Sales Invoice Line for rep 7
102	1	2	44		Rep1 gets 50% and Rep7 gets 50%   No record in Sales Invoice Line for rep 8
103	2	10	33		Rep2 gets 100% no record in sales record table defaults to 100%
104	2	22	22		Rep2 gets 100% no record in sales record table defaults to 100%
105	3	13	44		Rep3 gets 50% and Rep7 gets 50%  No record in Sales Invoice Line for rep 7
105	3	13	22		Rep3 gets 50% and Rep7 gets 50%  No record in Sales Invoice Line for rep 8

[Anonymous]

Sorry, Excel auto calculated the numbers.  There is no rep 8, it should say 7.  The sales invoice line only contains one entry that is to be split between two reps.  Rep 7 should get 50% of document 102 and 105.  The sales record table only contains entries for those documents that are to be split.  If a document is not in the table, it goes to the rep in the sales line invoice at 100%.