Joining queries created with QueryDef

[SalesOp]

Hi All,

I need a way to create successive select queries and join the next query with the previous one in VBA using QueryDef.

Example Query Level 0: RevenueRollup_sub_0

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

Example Query Level 1: RevenueRollup_sub_1
SELECT SalesRevenues.EmployeeName, Sum(RevenueRollup_sub_0.TotalRevenue) AS TotalRevenue, SalesRevenues.ManagerName
FROM SalesRevenues LEFT JOIN RevenueRollup_sub_0 ON SalesRevenues.EmployeeName = RevenueRollup_sub_0.ManagerName
WHERE (((SalesRevenues.EmployeeLevel)="1"))
GROUP BY SalesRevenues.EmployeeName, SalesRevenues.ManagerName;

I want to create the level 0 query with QueryDef in VBA and then somehow JOIN it on a field to create the next level 1 query with QueryDef. How do I use a specific field in query level 0 to Join and create a new query? The next level query depends on the recordset created in the previous query.

Thanks
SalesOps

 

[arnelgp]

here is the vba you requested for you roll-up.
be reminded that it can be very slow when stepping through each record.
the code is in module1. please take a look.
query1 is sample of using it.

 

[SalesOp]

Hi Arnelgp,

Many thanks, surprisingly the function is very compact. How exactly do I run it? Do I iterate from the highest level manager Level 5 and then it will recursively call all the below level managers and Level 0 staff. Each call will return the rolled up revenue for that manager, etc. So I would need some wrapper function that will call fnRevenueRollUp.

Also I notice you have not used a criteria to select the managers at each level.

While I understand that p is the EmployeeID, what is the use of v and bolFirst variables?

I had thought that we could somehow manipulate at query level instead of going down to the level of each record.

Thanks much for your support

Regards
SalesOp

 

[arnelgp]

you used it in a query as expression or on your unbound textbox on your form.
see query1 for example of using this function.

on a form you can use an unbound textbox at set its recordsource to the function:

=fnRevenueRollUp([EmployeeID],[SalesRevenue])

the parameter "p" of the function is the EmployeeID of current record, "v" is the SalesRevenue of current record. dont supply the bolFirst parameter, it is used internally by the function for recursion.

it recursively calls itself (the function). going from its current employee level down to the last level of employee.

if you are on the top employee level, it will recursively calls itself down to the last level.

if your record is in the lowest level, it will not go any further since this is the last level. therefore Salesrevenue will not accummulate.

haven't fully tested it. should compare it with your .xlsx file.

=