I am having problems trying to merge two tables

  • Thread starter Thread starter Jomo Willacy
  • Start date Start date
J

Jomo Willacy

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
 
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.
Click to expand...

Are you storing information redundantly in the two tables? If so, why?
Typically information should be stored ONCE.
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.
Click to expand...

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!
 
-----Original Message-----
On Fri, 31 Oct 2003 07:10:57 -0800, "Jomo Willacy"
Click to expand...
Hello John,

Here is a copy of my SQL View.

UPDATE Contacts1 LEFT JOIN EMPLOYERS1 ON Contacts1.ID =
EMPLOYERS1.ID SET;
 
Hello John,

Here is a copy of my SQL View.

UPDATE Contacts1 LEFT JOIN EMPLOYERS1 ON Contacts1.ID =
EMPLOYERS1.ID SET;
Click to expand...

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?
 
Hello Mr. Vinson,

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

Jomo



-----Original Message-----
Hello John,

Here is a copy of my SQL View.

UPDATE Contacts1 LEFT JOIN EMPLOYERS1 ON Contacts1.ID =
EMPLOYERS1.ID SET;
Click to expand...

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?


.
Click to expand...
 
Back
Top