INSERT query - please help me.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mitchell_Collen - 29 Nov 2007 17:09 GMT

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

Reply to this Message

Amy Blankenship - 29 Nov 2007 18:01 GMT

> 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

Reply to this Message

Mitchell_Collen - 29 Nov 2007 22:05 GMT

Thanks Amy.
-Misty

>> INSERT INTO Employee_Status ( [Employee Name], [Employee ID] )
>> SELECT [Employee List].[Employee Name], [Employee List].[Employee ID]
[quoted text clipped - 14 lines]
>
>Amy

Reply to this Message

John W. Vinson - 29 Nov 2007 18:55 GMT

>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]

Reply to this Message

Mitchell_Collen - 29 Nov 2007 22:05 GMT

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 - 13 lines]
>
> John W. Vinson [MVP]

Reply to this Message