Update a Record From a Record in Another Table

[gray]

Hi

WinXPPro
Access 2002 SP3

After a six hour battle with what I thought would be easy, I've decided to ask for help again. Essentially, I want to update several fields of a record in one table with data from the common fields of another.

Amongst the common fields is, "Contact_Id". In Table 1, this field is the autonumber field with Indexed Yes(No Duplicates) set. In Table 2, this field is just a Long Integer with no indexing set. Table 2 has it's own autonumber field, "Table2_Contact_Id" with Indexed Yes(No Duplicates).

So:

Table 1
--------
Contact_Id - Indexed Yes(No Duplicates)
Contact_First_Name - Text
Contact_Surname - Text

Table 2
--------
Table2_Contact_Id - Indexed Yes(No Duplicates)
Contact_Id - Long Int
Contact_First_Name - Text
Contact_Surname - Text


As an example. I have a record in Table 1, Contact_id = 50, whose contact_id, first and surnames I want to copy to an existing record in Table 2, namely Table2_Contact_Id = 5.

Having gone through the usual research, I built the following Query to do this:-

UPDATE Contacts_Table1 INNER JOIN Contacts_Table2 ON Contacts_Table1.Contact_Id = Contacts_Table2.Contact_Id SET Contacts_Table2.Contact_First_Name = [Contacts_Table1].[Contact_First_Name], Contacts_Table2.Contact_Surname = [Contacts_Table1].[Contact_Surname]
WHERE (((Contacts_Table1.Contact_Id)=50) AND ((Contacts_Table2.Table2_Contact_Id)=5));


It runs without error but does not update my Table2 record?? I get the "you are about to update 0 rows" prompt. I used the Query Builder in Access to create the above but the first line looked a little strange to me? ... so I also tried

UPDATE Contacts_Table2 INNER JOIN Contacts_Table1

but that didn't update anything either...

Any idea where I'm going wrong please? Thanks in advance.

 

[Anchoress]

Do you have the field properties set to accommodate the incoming data? Compatible field sizes etc?

 

[Premy]

Try something like:

UPDATE Table2 SET Table2.Contact_First_Name = DLookup("Contact_First_Name","Table1","Table1.Contact_Id=50")
WHERE Table2.Contact_Id=5;

HTH

 

[gray]

Thanks for the advice chaps, appreciated.

I created Table2 from Table1 so they all have the same field sizes etc .

I was about to try the DLookup method but experimented a little further with my query and discovered that if I first populated the target table's Contact_ID with the source table's Contact_ID value then the update of Table2's fields took place.

It's not quite as I expected and means an extra SQL call but at least it works.

Again, thanks for taking the time to have a look at my problem.