1

On both tables, the UserID filed is the primary key and the criteria is that the FistName filed value or the MobileNumber filed value must match. The tables look like:

Table1
+--------+-----------+---------------+
+ UserID + FirstName + MobileNumber  +
+--------+-----------+---------------+
+  100   + James     + 07725252511   +
+--------+-----------+---------------+
+  200   + Joy       + 07721323111   +
+--------+-----------+---------------+
+  300   + Pike      + 07820010012   +
+--------+-----------+---------------+

Table2
+--------+-----------+---------------+
+ UserID + FirstName + MobileNumber  +
+--------+-----------+---------------+
+  100   + James     + 07725252511   +
+--------+-----------+---------------+
+  210   + Joy       + 07721323111   +
+--------+-----------+---------------+
+  31a   + Pike      + 07820010012   +
+--------+-----------+---------------+

After Running the SQL, I expect Table1 to Look Like:

+--------+-----------+---------------+
+ UserID + FirstName + MobileNumber  +
+--------+-----------+---------------+
+  100   + James     + 07725252511   +
+--------+-----------+---------------+
+  210   + Joy       + 07721323111   +
+--------+-----------+---------------+
+  31a   + Pike      + 07820010012   +
+--------+-----------+---------------+

I have this code which runs but is not updating the UserID filed values of Table1 due to key violations error. Any help here?

SQL = "UPDATE Table1 " & _
"INNER JOIN Table2 " & _
"ON(Table1.FirstName = Table2.FirstName OR Table1.MobileNumber = 
 Table2.MobileNumber) " & _
"SET Table1.UserID = Table2.UserID, " & _
"Table1.Age = Table2.Age; "

DoCmd.RunSQL SQL
Rof
  • 47
  • 7
  • [How to ask a good SQL question](http://meta.stackoverflow.com/a/271056/3820271). Sample data and expected result are really needed here. – Andre Mar 23 '18 at 13:21
  • Hi @Andre I have updated my post with sample data. – Rof Mar 23 '18 at 14:28
  • FistName ? :-) Very clan wars – QHarr Mar 23 '18 at 14:34
  • @Harr, Typo corrected! – Rof Mar 23 '18 at 14:39
  • The SQL works as long as you add a space after `ON`, include an `Age` field in the table and I take it `Table1.MobileNumber = Table2.MobileNumber) " & _` should be on one line. – Darren Bartrup-Cook Mar 23 '18 at 15:04
  • @Darren Bartrup-Cook 1 Yes they are on the same line – Rof Mar 23 '18 at 16:44
  • Just thought.... key violation errors. Is `Table 1.UserID` a numeric field, while `Table 2.UserID` is a text field? You'll get that error when it tries to put `31a` into a numeric field. – Darren Bartrup-Cook Mar 23 '18 at 16:53
  • @Darren Bartrup-Cook 1 But in the design view the data types in both fields are set to Short Text. – Rof Mar 23 '18 at 17:09
  • 1
    I'm not sure then. Have you tried adding the SQL to a query and running it outside of a VBA procedure? `UPDATE Table1 INNER JOIN Table2 ON (Table1.FirstName = Table2.FirstName OR Table1.MobileNumber = Table2.MobileNumber) SET Table1.UserID = Table2.UserID, Table1.Age = Table2.Age`. The only problem I've come across is if I don't include an `Age` field in the tables. – Darren Bartrup-Cook Mar 23 '18 at 17:16
  • 1
    As Darren wrote, run your SQL in an actual query and see if you get better error information (e.g. *which* record(s) are the problem). [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) – Andre Mar 23 '18 at 17:37
  • NO Luck! But on a second thought, Table2 always holds the true primary key while Table1 Adds New Columns and updates if there is any so would It be a good idea to just add the new columns to Table2 and try updating the other rows with values from Table1? – Rof Mar 23 '18 at 17:44
  • Yes, add the columns to Table2. You shouldn't need to add columns to a table after you've built the database. – Darren Bartrup-Cook Mar 26 '18 at 07:48

0 Answers0