Some background: Our accounting system follows a three step process for billing customers. First, we create a sales order (SO) for the items we are shipping. Then at the designated date, we create a shipper (SH) that we ship from. Then finally, after the merchandise is shipped, we create an invoice. Each order is assigned a team of salespeople - anywhere from 1 to 10 sales people per order. Each salesperson is assigned a % of the sale; the total for all sales people adds up to 100%. When the SH is created, the user may change it, depending on what has changed in the business since the original sales order was created. For example, if the sales order was created several months ago, prices may have changed, so we update the SH for the new prices. Additionally, the team of salespeople may change when it gets to the shipper, so we have one team on the sales order, but by the time we ship it, the team has changed. We may add salespeople, remove salespeople and/or change percentages.
Here's my challenge: I have to create a report that shows each sale, broken down by the percentage assigned to each salesperson, sorted and subtotaled by salesperson. For example, if I have a $10,000 sales order with a team of 3 assigned to it - 50%, 25% and 25%, then I need the sales order listed on the report three times - $5,000, $2,500, and $2,500. Each amount will be in the group for the assigned salesperson. So the report might look like this:
Mary - Sales Order 123 - $5,000
Subtotal Mary $5,000
Sally - Sales Order 123 - $2,500
Subtotal Sally $2,500
Suzy - Sales Order 123 - $2,500
Subtotal Suzy $2,500
Grand Total $10,000
One wrinkle: I want to include ALL sales orders, whether a shipper has been created or not. If a shipper has been created, I want to pull the salespeople assignment and the sales $$$ from the shipper tables, since they will have the most current info. If no shipper has been created, then I want to pull the salespeople assignment and the sales $$$ from the sales order tables.
Where I am now: I have a Sales Order Header table that has one row per sales order. It has the following columns (plus a few more):
Sales Order #
Shipper #
Sales Order Sales Amount
Shipper Sales Amount
From this table, I can very easily create a formula that says if the shipper # is null, give me the sales order sales amount, else give me the shipper sales amount.
I also have two SalesPeople tables (one for sales orders and one for shippers) that have columns that look like this:
Sales Order Table:
Sales Order #
Sales Person ID
Sales Person %
Shipper Table:
Sales Order #
Shipper #
Sales Person ID
Sales Person %
Each table has multiple rows per sales order/shipper - one for each sales person.
My issue is how do I join the SO Header table with the two salespeople tables so I can properly allocate sales. I want to use the shipper table for salespeople assignments/splits if a shipper has been created and the sales order table if no shipper has been created.
Thanks in advance for your help!!!
Sherry
Here's my challenge: I have to create a report that shows each sale, broken down by the percentage assigned to each salesperson, sorted and subtotaled by salesperson. For example, if I have a $10,000 sales order with a team of 3 assigned to it - 50%, 25% and 25%, then I need the sales order listed on the report three times - $5,000, $2,500, and $2,500. Each amount will be in the group for the assigned salesperson. So the report might look like this:
Mary - Sales Order 123 - $5,000
Subtotal Mary $5,000
Sally - Sales Order 123 - $2,500
Subtotal Sally $2,500
Suzy - Sales Order 123 - $2,500
Subtotal Suzy $2,500
Grand Total $10,000
One wrinkle: I want to include ALL sales orders, whether a shipper has been created or not. If a shipper has been created, I want to pull the salespeople assignment and the sales $$$ from the shipper tables, since they will have the most current info. If no shipper has been created, then I want to pull the salespeople assignment and the sales $$$ from the sales order tables.
Where I am now: I have a Sales Order Header table that has one row per sales order. It has the following columns (plus a few more):
Sales Order #
Shipper #
Sales Order Sales Amount
Shipper Sales Amount
From this table, I can very easily create a formula that says if the shipper # is null, give me the sales order sales amount, else give me the shipper sales amount.
I also have two SalesPeople tables (one for sales orders and one for shippers) that have columns that look like this:
Sales Order Table:
Sales Order #
Sales Person ID
Sales Person %
Shipper Table:
Sales Order #
Shipper #
Sales Person ID
Sales Person %
Each table has multiple rows per sales order/shipper - one for each sales person.
My issue is how do I join the SO Header table with the two salespeople tables so I can properly allocate sales. I want to use the shipper table for salespeople assignments/splits if a shipper has been created and the sales order table if no shipper has been created.
Thanks in advance for your help!!!
Sherry