INSERT query - please help me.

[Mitchell_Collen via AccessMonster.com]

INSERT INTO Employee_Status ( [Employee Name], [Employee ID] )
SELECT [Employee List].[Employee Name], [Employee List].[Employee ID]
FROM [Employee List]
WHERE ((([Employee List].[Employee ID]) <> [Employee_Status].[Employee ID]));

I have no control over a linked table [Employee List] coming from managment
dept. I want to query to insert all employees and ids to another table called
Employee_Status (this table I will be able to create new columns such as
status on them) . That way, I can update either active/not active and create
reports/sign in sheets based on active employees only. I don't want to delete
non active employees. I know this sounds very spagetti. Will you please tell
how I can populate the table Employee_Status with the linked table Employee
List?

Thanks, Misty

 

[Amy Blankenship]

INSERT INTO Employee_Status ( [Employee Name], [Employee ID] )
SELECT [Employee List].[Employee Name], [Employee List].[Employee ID]
FROM [Employee List]
WHERE ((([Employee List].[Employee ID]) <> [Employee_Status].[Employee
ID]));

I have no control over a linked table [Employee List] coming from
managment
dept. I want to query to insert all employees and ids to another table
called
Employee_Status (this table I will be able to create new columns such as
status on them) . That way, I can update either active/not active and
create
reports/sign in sheets based on active employees only. I don't want to
delete
non active employees. I know this sounds very spagetti. Will you please
tell
how I can populate the table Employee_Status with the linked table
Employee
List?

Since you're not talking about a particular row in Employee_Status, the
WHERE clause isn't going to work properly. Possibly

WHERE [Employee List].[Employee ID] NOT IN (SELECT EmployeeID FROM
EmployeeStatus);

would work. Note I changed your field names in the table you DO control to
make them easier to work with.

HTH;

Amy

 

[John W. Vinson]

I have no control over a linked table [Employee List] coming from managment
dept. I want to query to insert all employees and ids to another table called
Employee_Status (this table I will be able to create new columns such as
status on them) . That way, I can update either active/not active and create
reports/sign in sheets based on active employees only. I don't want to delete
non active employees. I know this sounds very spagetti. Will you please tell
how I can populate the table Employee_Status with the linked table Employee
List?

I'd suggest a "frustrated outer join" query. Amy's NOT IN query is another way
to do it, but Access can be very inefficient at processing such queries - this
alternative gets to the same result but may be much faster:

INSERT INTO Employee_Status ( [Employee Name], [Employee ID] )
SELECT [Employee List].[Employee Name], [Employee List].[Employee ID]
FROM [Employee List] LEFT JOIN [Employee_Status]
ON [Employee List].[Employee ID]=[Employee_Status].[Employee ID]
WHERE [Employee_Status].[Employee ID] IS NULL;


John W. Vinson [MVP]

 

[Mitchell_Collen via AccessMonster.com]

Thanks Amy.
-Misty

INSERT INTO Employee_Status ( [Employee Name], [Employee ID] )
SELECT [Employee List].[Employee Name], [Employee List].[Employee ID]

[quoted text clipped - 16 lines]

Employee
List?

Since you're not talking about a particular row in Employee_Status, the
WHERE clause isn't going to work properly. Possibly

WHERE [Employee List].[Employee ID] NOT IN (SELECT EmployeeID FROM
EmployeeStatus);

would work. Note I changed your field names in the table you DO control to
make them easier to work with.

HTH;

Amy

 

[Mitchell_Collen via AccessMonster.com]

Thanks John.
-Misty

I have no control over a linked table [Employee List] coming from managment
dept. I want to query to insert all employees and ids to another table called

[quoted text clipped - 4 lines]

how I can populate the table Employee_Status with the linked table Employee
List?

I'd suggest a "frustrated outer join" query. Amy's NOT IN query is another way
to do it, but Access can be very inefficient at processing such queries - this
alternative gets to the same result but may be much faster:

INSERT INTO Employee_Status ( [Employee Name], [Employee ID] )
SELECT [Employee List].[Employee Name], [Employee List].[Employee ID]
FROM [Employee List] LEFT JOIN [Employee_Status]
ON [Employee List].[Employee ID]=[Employee_Status].[Employee ID]
WHERE [Employee_Status].[Employee ID] IS NULL;

John W. Vinson [MVP]