I am having problems trying to merge two tables

I am having problems trying to merge two tables

Post by Jomo Willa » Sun, 02 Nov 2003 00:10:57


Hello Everyone,

I have two tables - Contacts1 and Employee1 - and I am
trying to merge the Contacts1 table with the Employee1
table, based on the primary key "ID" field. I wish to
update the information in Contacts1 with the information
in Employee1 if the "ID" field are the same.
Whenever I try to do this I get the error message there
must be a destination field. However you cannot change
the updated field or the destination field.

Does anyone have any idea how I can solve this problem ?

Sincerely Yours,


Jomo Willacy
 
 
 

I am having problems trying to merge two tables

Post by John Vinso » Sun, 02 Nov 2003 01:28:07

On Fri, 31 Oct 2003 07:10:57 -0800, "Jomo Willacy"



Are you storing information redundantly in the two tables? If so, why?
Typically information should be stored ONCE.


Please post the SQL view of the query. Are you running an Update
query, or an Append query, or what? What do you mean that "you cannot
change the updated field or the destination field" - the destination
field IS the field that you want to update!


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://www.yqcomputer.com/

 
 
 

I am having problems trying to merge two tables

Post by anonymou » Sun, 02 Nov 2003 01:46:55


Hello John,

Here is a copy of my SQL View.

UPDATE Contacts1 LEFT JOIN EMPLOYERS1 ON Contacts1.ID =
EMPLOYERS1.ID SET;



tables? If so, why?
Update
that "you cannot
destination
 
 
 

I am having problems trying to merge two tables

Post by John Vinso » Sun, 02 Nov 2003 03:15:47

On Fri, 31 Oct 2003 08:46:55 -0800,




Ok. You're not updating any fields to anything. Normally after the SET
keyword you would indicate what fields you want updated, and what you
want them updated to: for instance

SET [Contacts1].[LastName] = [Employers1].[LastName]

That's why you're getting the error message - you're creating an
update query and then not updating anything!

In the query grid, select all of the fields in Contacts1 that you want
to update; on the Update To line of the query grid, type in

[Employers1].[fieldname]

for the field containing the data you want to insert.

Note that the LEFT JOIN is going to cause problems. If there are
records in Contacts1 which do not exist in Employers1, you will get
NULL values for all the fields in Employers1 for those records; the
update query will then erase any existing data. Is that what you want,
or do you want to change it to INNER instead of LEFT?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://www.yqcomputer.com/
 
 
 

I am having problems trying to merge two tables

Post by Jomo Willa » Sun, 02 Nov 2003 06:19:36

Hello Mr. Vinson,

Thank you for your help. That was what the problem was.

Jomo






after the SET
and what you
creating an
that you want
in
there are
you will get
records; the
what you want,