Help with query

[snoopy2003]

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

[June7]

I recently attempted to run UNION query in Access VBA and it would not work. Would not build recordset from a saved UNION query object nor would it build a recordset with the UNION SQL statement in VBA. Finally gave up and restructured procedure to not need UNION.

Weird thing is I have VBA in Excel that does build recordset from a saved UNION query.

The only thing I came up different for your SQL is in the PIVOT clause. Doesn't seem the query name is needed but might try. If I had data I would test.
PIVOT qryCountManagersCrosstab.EmployeeType) As qryCountManagers;

[snoopy2003]

Hello,

Thank you for your answer.
I added the query name but it did not help.

What is the alternative to UNION in VBA ?

Thank you for your help

[June7]

There is no defined alternative I can provide. Solution would be unique to your data. I had to reconfigure my procedure. I no longer have the old version. If I remember right, my solution was to write data to a temp table. Temp meaning data lives in table for the duration of process and is purged when done.

In your case, maybe it is the PIVOT that is issue. I don't use many PIVOT queries and never tried coding one. As test, try coding just the UNION part - will the recordset open?

If you want to provide project, I will test query.