I have some queries that I use on a form to generate reports, and an example of one is one I call qryBudgetNewJoined:
SELECT Department, [Dept ID], [Job Code], [Job Description],
[Total Regular FTE], [Total Overtime FTE], [Total Holiday FTE],[Total FTE]
FROM BudgetNew
UNION SELECT [Rpt Dept], [Rpt Dept Desc], PositionDetail.[Job code], [Jobcode Title],
Null, Null, Null, Null
FROM PositionDetail PositionDetail LEFT JOIN BudgetNew
ON PositionDetail.[Rpt Dept]=BudgetNew.Department AND PositionDetail.[Jobcode Title]=BudgetNew.[Job Description]
WHERE BudgetNew.[Job Description] IS NULL
I have another query that is formed from this query and another query, that I call qryDirectorBudget:
SELECT DISTINCT Directors.Service, qryBudgetNewJoined.Department, qryBudgetNewJoined.[Dept ID], Directors.Segment
FROM Directors RIGHT JOIN qryBudgetNewJoined ON Directors.DeptID = qryBudgetNewJoined.Department
WHERE (((Directors.Service) Is Null Or (Directors.Service) Like [Forms]![frmReportGenerator].[cboService] & "*") AND ((qryBudgetNewJoined.Department) Like [Forms]![frmReportGenerator].[cboDept] & "*") AND ((qryBudgetNewJoined.[Dept ID]) Like [Forms]![frmReportGenerator].[cboDeptNo] & "*") AND ((Directors.Segment) Is Null Or (Directors.Segment) Like [Forms]![frmReportGenerator].[cboSegment] & "*"));
OK, both these queries work fine for me (albeit slowly on large datasets). But as I am attempting to automate a process so that I don't have to individually run 200 reports (one for each department), I understand that recordsets require sql that pulls from tables only. So I am attempting to "unpack" the one query so that I can effectively insert it into the other, and I get an error--'not allowed in subqueries' or language to that effect.
What can or should I do? Granted, some of my queries are perhaps more complicated than they should be, but they emerged gradually and work correctly, so I am somewhat reluctant to re-engineer them.
Tnx,
Tom Y
SELECT Department, [Dept ID], [Job Code], [Job Description],
[Total Regular FTE], [Total Overtime FTE], [Total Holiday FTE],[Total FTE]
FROM BudgetNew
UNION SELECT [Rpt Dept], [Rpt Dept Desc], PositionDetail.[Job code], [Jobcode Title],
Null, Null, Null, Null
FROM PositionDetail PositionDetail LEFT JOIN BudgetNew
ON PositionDetail.[Rpt Dept]=BudgetNew.Department AND PositionDetail.[Jobcode Title]=BudgetNew.[Job Description]
WHERE BudgetNew.[Job Description] IS NULL
I have another query that is formed from this query and another query, that I call qryDirectorBudget:
SELECT DISTINCT Directors.Service, qryBudgetNewJoined.Department, qryBudgetNewJoined.[Dept ID], Directors.Segment
FROM Directors RIGHT JOIN qryBudgetNewJoined ON Directors.DeptID = qryBudgetNewJoined.Department
WHERE (((Directors.Service) Is Null Or (Directors.Service) Like [Forms]![frmReportGenerator].[cboService] & "*") AND ((qryBudgetNewJoined.Department) Like [Forms]![frmReportGenerator].[cboDept] & "*") AND ((qryBudgetNewJoined.[Dept ID]) Like [Forms]![frmReportGenerator].[cboDeptNo] & "*") AND ((Directors.Segment) Is Null Or (Directors.Segment) Like [Forms]![frmReportGenerator].[cboSegment] & "*"));
OK, both these queries work fine for me (albeit slowly on large datasets). But as I am attempting to automate a process so that I don't have to individually run 200 reports (one for each department), I understand that recordsets require sql that pulls from tables only. So I am attempting to "unpack" the one query so that I can effectively insert it into the other, and I get an error--'not allowed in subqueries' or language to that effect.
What can or should I do? Granted, some of my queries are perhaps more complicated than they should be, but they emerged gradually and work correctly, so I am somewhat reluctant to re-engineer them.
Tnx,
Tom Y
