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
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