I've looked through several other questions of this type and have not found one that will help me resolve this issue. What I'm trying to do is this: I want to create a table for all employees for the purpose of assigning an EmployeeID that will be used across several other tables. That table and the others work fine. My issue arises when I try to create a new table based off the EmployeeType so I can bring up information based solely on employees of a specific type. On the SELECT statement for all three of the tables I get this error:
Incorrect syntax near '('. Expecting ID.
I've googled and googled for how to resolve it, but nothing I've tried is working. What am I missing?
CREATE TABLE Employees
(
EmployeeID int NOT NULL PRIMARY KEY IDENTITY,
EmpFirstName char(50) NOT NULL,
EmpLastName char(50) NOT NULL,
EmpAddress varchar(50) NOT NULL,
EmpCity char(50) NOT NULL,
EmpState char(2) NOT NULL,
EmpZipCode varchar(10) NOT NULL,
EmpPhone varchar(12) NOT NULL,
EmpJobTitle char(30) NOT NULL,
EmployeeType char(30) NOT NULL,
Salary money NOT NULL,
HoursPerWeek int NOT NULL,
);
CREATE TABLE Collective AS
(SELECT
*
FROM
[dbo].[Employees]
WHERE
EmployeeID = Employees.EmployeeID
AND EmployeeType = 'Collective');
CREATE TABLE PaidStaff AS
(SELECT
EmployeeID AS ReviewerID,
EmpFirstName,
EmpLastName,
EmpAddress,
EmpCity,
EmpState,
EmpZipCode,
EmpPhone,
EmpJobTitle
Salary,
HoursPerWeek
FROM
Employees
WHERE
EmployeeID = Employees.EmployeeID
AND EmployeeType = 'PaidStaff');
CREATE TABLE Volunteers AS
(SELECT
EmployeeID AS ReviewerID,
EmpFirstName,
EmpLastName,
EmpAddress,
EmpCity,
EmpState,
EmpZipCode,
EmpPhone,
EmpJobTitle
FROM
Employees
WHERE
EmployeeType = 'Volunteer');