August 15, 2005 at 6:16 pm
hi all, i think the heading line sums up exactly what I am trying to achieve but here is the SQL from the Access DB that I am trying to create a view in SQL Server 2000 with...
SELECT qryPT001.EmployeeNo
FROM qryPT001
UNION SELECT qryPT002.EmployeeNo
FROM qryPT002
UNION SELECT qryPT003.EmployeeNo
FROM qryPT003
UNION SELECT qryPT004.EmployeeNo
FROM qryPT004
UNION SELECT qryPT005.EmployeeNo
FROM qryPT005
UNION SELECT qryPT006.EmployeeNo
FROM qryPT006
UNION SELECT qryPT007.EmployeeNo
FROM qryPT007
UNION SELECT qryPT008.EmployeeNo
FROM qryPT008
UNION SELECT qryPT009.EmployeeNo
FROM qryPT009
UNION SELECT qryPT010.EmployeeNo
FROM qryPT010;
..but I get this error message when i try to save the view "The Query Designer does not support the UNION SQL construct.". does anyone have any advice to offer to help resolve this please ?
Mitch....
August 15, 2005 at 9:00 pm
I ran the following in QA with no problem:
use Northwind
SELECT qryPT001.EmployeeID
FROM dbo.Employees qryPT001
UNION SELECT qryPT002.EmployeeID
FROM dbo.Employees qryPT002
I used Enterprise Manager to create a view in Northwind and entered the above commands minus the use statement and got the same error message you did. I said yes I wanted to continue and it reformatted my output:
SELECT qryPT001.EmployeeID
FROM dbo.Employees qryPT001
UNION
SELECT qryPT002.EmployeeID
FROM dbo.Employees qryPT002
The column list and graphical image panes remained blank. When I asked for output, it gave me output. You should be able to create your view using the UNION command, the full EM support for doing so is not available.
August 16, 2005 at 10:34 am
Thanks Kenneth, like you I did get the results to display but had problems saving the view so I added the tables manually, re-pasted the Sql code back in the SQL pane and that took care of it.
Thanks again,
Mitch....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply