Sum Total Sales Revenue by Sales Rep coving Multiple Regions in Different Months

[macmill]

The following query sums the TotalSales data for each rep.
SELECT Sum(TotalSales.FieldFESalesPlan) AS SumOfFieldFESalesPlan, Sum(TotalSales.FieldFESalesActuals) AS SumOfFieldFESalesActuals, Query1.EmployeeID
FROM Query1 RIGHT JOIN TotalSales ON Query1.Region = TotalSales.Region
GROUP BY Query1.EmployeeID;

However, if you need the data aggregated by 6-month periods, then need another field in SalesRep to identify the period (1 or 2).

Rep 1621098 has 3 records. Possibly need to delete the first one and renumber the other 2.

EmployeeID	Period	Managed Area1	Managed Area2	Managed Area3
1621098	1	11201
1621098	2	11201	11202	11209
1621098	3	11203	11209	11210

Revise the UNION query:

SELECT EmployeeID, Period, [Managed Area1] AS Region, 1 AS SID FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area2], 2 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area3], 3 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area4], 4 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area5], 5 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area6], 6 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area7], 7 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area8], 8 FROM SalesRep
UNION SELECT EmployeeID, Period, [Managed Area9], 9 FROM SalesRep;

Then the aggregate query:
SELECT Sum(TotalSales.FieldFESalesActuals) AS SumOfFieldFESalesActuals, Sum(TotalSales.FieldFESalesPlan) AS SumOfFieldFESalesPlan, Avg([FieldFESalesActuals]/[FieldFESalesPlan]) AS Pct, Query1.EmployeeID, Query1.Period
FROM Query1 RIGHT JOIN TotalSales ON Query1.Region = TotalSales.Region
GROUP BY Query1.EmployeeID, Query1.Period;

There will be a record with no EmployeeID because region 10012 has no reps.

BTW, a UNION query has a limit of 50 SELECT lines - I know because I've hit that limit.

June7,

Wanted to thank you for your help. Taking a look at your SQL code, made me realize I left out an important piece of information. You used Period but to keep track of everything and give me a way to track back steps, I concatenate the regions together. I struggled to create the Union query but you will see that i created 1 make table query and then 8 following append queries. I think this closely represents the union query and then I have one query summing the aggregate.

Again, just want to thank you for helping me think through this process.

I've attached a final version of the database if interested.

Regards,
macmill

[June7]

Instead of a MAKE TABLE, suggest tblRollValue is a permanent table and the data is temporary - DELETE all records before running procedure to INSERT and UPDATE. This is called a 'temp' table - table is permanent but data is temporary. A process that routinely modifies structure is less efficient and has higher chance of db corruption.