Help using UNION in an Append Query

[ecugrad]

Having trouble running the below append query utilizing a union statement. Can this be done, if not any suggestions.

Thanks


INSERT INTO [R&R_MonthlyMatrixScores] ( BNumber, [Year], [Month], DepartmentID, Department, LastName, FirstName, Salary, IndPhoneMonitoringScore, DeptPhoneMonitoringScore, IndQAMScore, TypeID, DeptQAMScore, ProductivityScore, HPDPoints, DisqID, DisqDesc, RunDate, MDisq, Unit )
SELECT [R&R_EmployeeDepartmentLink].BNumber, 2007 AS [Year], 4 AS [Month], [R&R_EmployeeDepartmentLink].DepartmentID, [R&R_EmployeeDepartmentLink].Department, [R&R_EmployeeDepartmentLink].LastName, [R&R_EmployeeDepartmentLink].FirstName, [R&R_EmployeeDepartmentLink].NewSalary, [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_IndPhoneMonitoringScore] AS IndPhoneMonitoringScore, [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_DeptPhoneMonitoringScore] AS DeptPhoneMonitoringScore, QA_CombinedIndTeamScores.QA_IndAccuracy AS IndQAMScore, [R&R_EmployeeDepartmentLink].TypeID, QA_CombinedIndTeamScores.QA_DeptAccuracy AS DeptQAMScore, [R&R_MonthlyProductionScores].Score AS ProductivityScore, [R&R_HPDPoints].HPDPoints, QuarterlyDisqEmployees.DisqID, QuarterlyDisqEmployees.DisqDesc, Now() AS RunDate, [R&R_EmployeeDepartmentLink].MDesq, [R&R_EmployeeDepartmentLink].Unit
FROM (((((([R&R_EmployeeDepartmentLink] LEFT JOIN [R&R_CombinedIndTeamPhoneMonitoringScores] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_BNumber]) LEFT JOIN QA_CombinedIndTeamScores ON [R&R_EmployeeDepartmentLink].BNumber = QA_CombinedIndTeamScores.BNumber) LEFT JOIN [R&R_HPDPoints] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_HPDPoints].BNumber) LEFT JOIN [R&R_MonthlyProductionScores] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_MonthlyProductionScores].USERNAME) LEFT JOIN QuarterlyDisqEmployees ON [R&R_HPDPoints].BNumber = QuarterlyDisqEmployees.BNumber) LEFT JOIN [R&R_MonthlyDisqEmployees] ON [R&R_HPDPoints].BNumber = [R&R_MonthlyDisqEmployees].BNumber) LEFT JOIN [R&R_ClientSupportCapacity] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_ClientSupportCapacity].BNumber
WHERE ((([R&R_EmployeeDepartmentLink].DepartmentID)<>19));
ORDER BY [R&R_EmployeeDepartmentLink].BNumber;

INSERT INTO [R&R_MonthlyMatrixScores] ( BNumber, [Year], [Month], DepartmentID, Department, LastName, FirstName, Salary, IndPhoneMonitoringScore, DeptPhoneMonitoringScore, IndQAMScore, TypeID, DeptQAMScore, ProductivityScore, HPDPoints, DisqID, DisqDesc, RunDate, MDisq, Unit )
UNION SELECT [R&R_EmployeeDepartmentLink].BNumber, 2007 AS [Year], 4 AS [Month], [R&R_EmployeeDepartmentLink].DepartmentID, [R&R_EmployeeDepartmentLink].Department, [R&R_EmployeeDepartmentLink].LastName, [R&R_EmployeeDepartmentLink].FirstName, [R&R_EmployeeDepartmentLink].NewSalary, [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_IndPhoneMonitoringScore] AS IndPhoneMonitoringScore, [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_DeptPhoneMonitoringScore] AS DeptPhoneMonitoringScore, QA_CombinedIndTeamScores.QA_IndAccuracy AS IndQAMScore, [R&R_EmployeeDepartmentLink].TypeID, QA_CombinedIndTeamScores.QA_DeptAccuracy AS DeptQAMScore, [R&R_ClientSupport].Score AS ProductivityScore, [R&R_HPDPoints].HPDPoints, QuarterlyDisqEmployees.DisqID, QuarterlyDisqEmployees.DisqDesc, Now() AS RunDate, [R&R_EmployeeDepartmentLink].MDesq, [R&R_EmployeeDepartmentLink].Unit
FROM (((((([R&R_EmployeeDepartmentLink] LEFT JOIN [R&R_CombinedIndTeamPhoneMonitoringScores] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_BNumber]) LEFT JOIN QA_CombinedIndTeamScores ON [R&R_EmployeeDepartmentLink].BNumber = QA_CombinedIndTeamScores.BNumber) LEFT JOIN [R&R_HPDPoints] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_HPDPoints].BNumber) LEFT JOIN [R&R_MonthlyProductionScores] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_MonthlyProductionScores].USERNAME) LEFT JOIN QuarterlyDisqEmployees ON [R&R_HPDPoints].BNumber = QuarterlyDisqEmployees.BNumber) LEFT JOIN [R&R_MonthlyDisqEmployees] ON [R&R_HPDPoints].BNumber = [R&R_MonthlyDisqEmployees].BNumber) LEFT JOIN [R&R_ClientSupportCapacity] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_ClientSupportCapacity].BNumber
WHERE ((([R&R_EmployeeDepartmentLink].DepartmentID)=19))
ORDER BY [R&R_EmployeeDepartmentLink].BNumber;

 

[AlexCuse]

You need to treat your union as a subquery I believe. Like this:

insert into SomeTable

select a.* from
(
select * from ThisTable

union all 

select * from ThatTable
) a

Another option is to save your union as a querydef, and do it like this:

insert into SomeTable
select * from MySavedUnionQuery

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom