Hello,
I have 2 queries defined in Access:
When I run the following query in VBA I get the desired results.
stSQL = "SELECT * INTO tmpEmpDistPerMan FROM qryCountManagers;"
DoCmd.RunSQL stSQL
qryCountManagers
TRANSFORM Count(qryCountManagersCrosstab.Manager) AS CountOfManager
SELECT qryCountManagersCrosstab.Manager
FROM qryCountManagersCrosstab
GROUP BY qryCountManagersCrosstab.Manager
PIVOT qryCountManagersCrosstab.EmployeeType;
qryCountManagersCrosstab
SELECT tblEmployees.M1 AS Manager, tblEmployees.EmployeeType, tblEmployees.Status
FROM tblEmployees
WHERE (Not IsNull(M1) AND Status = 'Active')
UNION ALL SELECT tblEmployees.M2 AS Manager, tblEmployees.EmployeeType, tblEmployees.Status
FROM tblEmployees
WHERE (Not IsNull(M2) AND Status = 'Active')
UNION ALL SELECT tblEmployees.M3 AS Manager, tblEmployees.EmployeeType, tblEmployees.Status
FROM tblEmployees
WHERE (Not IsNull(M3) AND Status = 'Active');
When I try to define all the queries as 1 query in VBA and run it (as string variable) I get an error - "Error in From".
stSQL = "SELECT * INTO tmpEmpDistPerMan FROM (" & qryCountManagers & ");"
DoCmd.RunSQL stSQL
qryCountManagers is equal to:
SELECT * INTO tmpEmpDistPerMan FROM (TRANSFORM Count(qryCountManagersCrosstab.Manager) AS CountOfManager SELECT qryCountManagersCrosstab.Manager FROM (SELECT tblEmployees.M1 AS Manager, tblEmployees.EmployeeType, tblEmployees.Status FROM tblEmployees WHERE (Not IsNull(M1) AND Status = 'Active') UNION ALL SELECT tblEmployees.M2 AS Manager, tblEmployees.EmployeeType, tblEmployees.Status FROM tblEmployees WHERE (Not IsNull(M2) AND Status = 'Active') UNION ALL SELECT tblEmployees.M3 AS Manager, tblEmployees.EmployeeType, tblEmployees.Status FROM tblEmployees WHERE (Not IsNull(M3) AND Status = 'Active')) AS qryCountManagersCrosstab GROUP BY qryCountManagersCrosstab.Manager PIVOT qryCountManagersCrosstab.EmployeeType);
What am I doing wrong ?
Thank you for your help