And the light bulb comes on, although rather dimly. I was seeing the data in SalesRep as dollars and now I see it is the Region. However, I see the same rep with more than one record for the same region.
EmployeeID |
Managed Area1 |
Managed Area2 |
Managed Area3 |
Managed Area4 |
Managed Area5 |
Managed Area6 |
Managed Area7 |
Managed Area8 |
Managed Area9 |
1364626 |
11304 |
11306 |
11308 |
|
|
|
|
|
|
1364626 |
11304 |
11306 |
11307 |
|
|
|
|
|
|
Not clear to me what you want to accomplish with these two tables. The Excel formula sums data yet the TotalSales table is already aggregated data. How should this be related to the SalesRep table?
Sales Rep 1364626 held the first grouping of regions (11304;11306;11308) for the first six month of the year and then (11304;11306;11307) for the last six months of the year. The difference is Managed Area3 (11308 vs 11307). What I am trying to do is sum the total sales of each region (Region 11304 ($47,814,118.21)+Region 11306 ($50,847,562.31)+ Region 11308 ($52,282,152.23)= $150,943,833. This is from the FieldFESAlesActuals fields on the TotalSales table. I would also do this same thing for FieldFESalesPlan (total sum is $152,737,166).
Maybe a little more background about the whole process would be helpful. Once I have everything summed, I then divide the two ($150,943,833/$152,737,166= .988). With this result, I have a matrix that reflects a bonus amount. Lets say this would be a $10,000 bonus. Since the sales rep held this combination of regions for the first 6 months, it is prorated by 6/12=.5, or $5,000. Working through the same process with the other combination of regions ($152,620,155/$154,399,093=.988). Just so happens the results are the same in this example and the proration .5 for these regions will be $5,000. The last task is then to sum the two bonus amounts ($10,000) for a total bonus payment. Hope this explanation gives some background to the whole process but my biggest challenge is summing these regions together.
Thank you for your directions below.
A UNION query can rearrange data to normalized structure. And if you want EVERY value even if it creates a duplicate record, use UNION ALL, otherwise remove ALL.
SELECT EmployeeID, [Managed Area1] AS Region, 1 AS SID FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area2], 2 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area3], 3 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area4], 4 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area5], 5 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area6], 6 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area7], 7 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area8], 8 FROM SalesRep
UNION ALL SELECT EmployeeID, [Managed Area8], 9 FROM SalesRep;
Now use that query like a table in subsequent queries.
The query can join to the TotalSales table to associate all reps that contributed to the region sales but the same sales data will repeat.
SELECT TotalSales.Region, TotalSales.FieldFESalesPlan, TotalSales.FieldFESalesActuals, Query1.EmployeeID
FROM TotalSales LEFT JOIN Query1 ON TotalSales.Region = Query1.Region;
Shows that region 10012 has no reps.