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