Dear VBA Gurus,
I have sales revenue data that I need to frequently analyze and roll up to the next level manager from all his reports. The sales 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 F is what I need to fill and the answer should be as in column G.
In the example data I am assuming each manager has two reports. With help from this forum I have been able to achieve this with select queries and a Union query with the following steps:
1. Create a select query for Level 0 employees - qryRevenueLevel0
2. Create similar queries for up to Level 5 where the input for each is the previous level query (i.e. qryRevenueLevel(n-1)). The join is between the Employee ID and his Manager ID.
3. Create a Union query for these select queries.
4. Create a MakeTableQuery that creates a temp table and stores the union of all the above select queries
5. Update the original table from the temp table.
As I need to do this very frequently for different kinds of revenues. I would like to create a VBA function that takes the original table and does all of the above and fills Column D with the revenues.
I am very new to VBA and would appreciate any help.
The Database and the Excel files are attached.
Thanks in Advance
SalesOP
I have sales revenue data that I need to frequently analyze and roll up to the next level manager from all his reports. The sales 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 F is what I need to fill and the answer should be as in column G.
In the example data I am assuming each manager has two reports. With help from this forum I have been able to achieve this with select queries and a Union query with the following steps:
1. Create a select query for Level 0 employees - qryRevenueLevel0
2. Create similar queries for up to Level 5 where the input for each is the previous level query (i.e. qryRevenueLevel(n-1)). The join is between the Employee ID and his Manager ID.
3. Create a Union query for these select queries.
4. Create a MakeTableQuery that creates a temp table and stores the union of all the above select queries
5. Update the original table from the temp table.
As I need to do this very frequently for different kinds of revenues. I would like to create a VBA function that takes the original table and does all of the above and fills Column D with the revenues.
I am very new to VBA and would appreciate any help.
The Database and the Excel files are attached.
Thanks in Advance
SalesOP
