Revenue Rollup to the next level manager - recursive queries

[SalesOp]

Hello,

I have sales revenue data that I want to roll up to the next level manager from all his reports. The achievement of a manager is the sum of the achievements of his direct reports.

I have a 5 level employee hierarchy with level 0 being the field sales people and levels 1 to 5 are the successive levels of management. In my table (attached) I have four columns: EmployeeName, EmployeeLevel, ManagerName, SalesRevenue. For level 0 employees the SalesRevenue column has their actual sales achievements. All all other levels (1-5) I need to compute the total achievement of this manager's direct reports and this will become the manager's achievement. Recursively I will need to do this for all levels. Column D is what I need to fill and the answer should be as in column E

In the example data I am assuming each manager has two reports. Using Access queries I am creating 5 temporary tables to store rollup of each and then have 5 update queries to update the SalesRevenue column. I am wondering if I can automate this task without having to use multiple temp tables and update queries and make it more general for a specified hierarchy level.

I will appreciate if someone can suggest a cleaner solution using queries or perhaps using VBA recursive procedure, etc.


Regards
SalesOP

The same coTeh same in teh same

 

[plog]

As long as you have a fixed number of levels, and because there are only 6 of them, I think you can use a series of subqueries that are brought together via a UNION query. In general, I would create a subquery for each level, where each level would be based on your initial table and the prior level's subquery. Then I would use a UNION query to bring all those sub queries data together.

First, you shouldn't use names to identify your rows, you should use some sort of number--preferably an autonumber primary key from an Employees table. Also, you didn't provide your table's name, so I used 'YourTableNameHere' in my SQL--you should replace all instances of that with your actual table name to make my solution work.

This is the code for Level 0 sales totals:

SELECT YourTableNameHere.EmployeeName, YourTableNameHere.SalesRevenue AS TotalRevenue, YourTableNameHere.ManagerName
FROM YourTableNameHere
WHERE (((YourTableNameHere.EmployeeLevel)=0));

Paste that into a query and save it with the name 'RevenueRollup_sub_0'. Since this has no downstream revenue to account for, its the simplest one. Use this code for Level 1:

SELECT YourTableNameHere.EmployeeName, Sum(RevenueRollup_sub_0.TotalRevenue) AS TotalRevenue, YourTableNameHere.ManagerName
FROM YourTableNameHere LEFT JOIN RevenueRollup_sub_0 ON YourTableNameHere.EmployeeName = RevenueRollup_sub_0.ManagerName
WHERE (((YourTableNameHere.EmployeeLevel)=1))
GROUP BY YourTableNameHere.EmployeeName, YourTableNameHere.ManagerName;

Paste that into a query and name it 'RevenueRollup_sub_1'. It sums up all the values in RevenueRollup_sub_0 for all Level 1 employees. This is the code you will use as a pattern for the other 4. For the next queries you will change the '_sub_0' portion to one less than the level you are working on, you will also change the WHERE clause to the level you are working on. As an example this is what 'RevenueRollup_sub_2' will be:

SELECT YourTableNameHere.EmployeeName, Sum(RevenueRollup_sub_1.TotalRevenue) AS TotalRevenue, YourTableNameHere.ManagerName
FROM YourTableNameHere LEFT JOIN RevenueRollup_sub_1 ON YourTableNameHere.EmployeeName = RevenueRollup_sub_1.ManagerName
WHERE (((YourTableNameHere.EmployeeLevel)=2))
GROUP BY YourTableNameHere.EmployeeName, YourTableNameHere.ManagerName;

Keep doing that until you have RevenueRollup_sub_5. Then you make your UNION query:

SELECT EmployeeName, TotalRevenue FROM RevenueRollup_sub_0
UNION ALL SELECT EmployeeName, TotalRevenue FROM RevenueRollup_sub_1
UNION ALL SELECT EmployeeName, TotalRevenue FROM RevenueRollup_sub_2
UNION ALL SELECT EmployeeName, TotalRevenue FROM RevenueRollup_sub_3
UNION ALL SELECT EmployeeName, TotalRevenue FROM RevenueRollup_sub_4
UNION ALL SELECT EmployeeName, TotalRevenue FROM RevenueRollup_sub_5;

That will give you the results you want.

 

[SalesOp]

Thank you so much Plog, I will try this. Much appreciated

 

[SalesOp]

Thanks, it all worked very beautifully and neatly

 

[SalesOp]

Hi Plog

After the Union query generates the rolled up revenues for all the levels how do I update the field SalesRevenue in the original YourTableNameHere table. When I use an update query with the Union query, it complains "Operation must use an updateable query" . So basically how do I put the results back. I guess I could a temporary table but it seems I can't convert a Union query into a table.

Appreciate your help.

Regards
SalesOp

 

[jmetzger]

Pplog,

When, I use your queries, I am getting the same total at each level. How are the revenues at the intermediate levels added to the totals? It seems that each query is just bringing up the lowest level's (0 level) totals.

Thanks

 

[plog]

Can you post a sample database with what you have?