Best way to handle Split Sales between Reps, 2 different databases ?

[Peter_Brunner]

Hi All,

I have an issue on best way to handle the following :

We Have

1) A Customer Database with Sales Reps Numbers allocated to their Customer Account Areas(Groups) that they look after.

e.g.:

Customer Table

Customer No, Customer Name,   Rep             Rep No

00011,               Bill Const,               Harry           0014

00022,               Concrete,               Harry            0014

00033,               Timber,                   Harry           0014

00044,               Joe Plumbing,        Paul             0012

00055                Steve Posts           Paul             0012

So the "Sales"  these Customers Groups Generate are totaled and allocated to that Rep.

Sales Table

2) Sales Table with Customer Numbers and Different Salesman Numbers (populated by Sales Person that made the Actual Sale)

Issue how to best handle below ?

e.g: if Rep Harry looks after Customer = 00011, Bill Const ...but  Paul actually made the Sale, Paul should get Sale NOT Harry!

So Paul made a $4,000 sale to 00011, Bill Const  Paul = $4,000 Sales for the Day

thanks for any help on this

regards Peter

[#2]

This problem is typical in Sales. I think you should recreate your model schema. For example it could be:

- Customer table with Customer no and Customer name (join Fact on Cust No)

- Sales person table with Rep no and Rep name (join Fact on Rep No)

- Fact table with actual sale fact and Customer no and Rep no (because rep which made a sale can change in time)

You can also keep Rep name+RepID in customer table for information about Rep responsible for customer. But change the name of this column and don't use it as a link between Customer and Fact table - in other words you can use it as a attribute of Customer when it's needed.

[Peter_Brunner]

Hi Mitosz,

thanks for your reply I will re visit this

regards Peter