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

-- 
Message posted via http://www.accessmonster.com

"Mitchell_Collen via AccessMonster.com" <u33726@uwe> wrote in message 
news:7bf24d7c58c0f@uwe...
> 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 

On Thu, 29 Nov 2007 17:09:45 GMT, "Mitchell_Collen via AccessMonster.com"
<u33726@uwe> wrote:

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

Thanks Amy.
-Misty
Amy Blankenship wrote:
>> 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

-- 
Message posted via http://www.accessmonster.com

Thanks John.
-Misty

John W. Vinson wrote:
>>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]

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1