I have 3 tables in an Access db. Table_1 and Table_2 are where the source data comes from and I am filling Table_3.

Table_1 has the following fields:

  • Acct_Name
  • Amt

Table_2 has the following fields:

  • Acct_Name
  • Amt_1
  • Amt_2

Table_3 has the following fields:

  • Acct_Name
  • Final_Value

Taking the values in Table_1.Amt to be valid, I want to know which of the two values, Amt_1 or Amt_2, from Table_2 is a better match to Table_1.Amt. Amt_1 and Amt_2 are arrived at via 2 different summing algorithms (different pieces summed together to arrive at the total value). I want to use the closer match to fill Table_3.Final_Value.

For the sake of simplicity we'll say there's a 1-to-1 match for Table_1.Acct_Name to Table_2.Acct_Name.

Since it is known that for a large fraction of rows in Table_2 (Amt_1) - (Amt_2) = 0, I start with the following append query (Table_3 is empty):

INSERT INTO Table_3 ([Acct_Name],[Final_Value])
SELECT [Table_2].[Acct_Name], [Table_2].[Amt_2]
FROM Table_2
GROUP BY [Table_2].[Acct_Name], [Table_2].[Amt_2]
HAVING ((([Table_2].[Acct_Name])<>"0"));

At this point all Table_3.Final_value fields equal Table_2.Amt_2 with Table_2.Acct_Name equal to Table_3.Acct_Name. Now I just want to update any Table_3.Final_Value to Table_2.Amt_1 if the absolute value of the difference between Table_1.Amt and Table_2.Amt_1 is less than the absolute difference between Table_1.Amt and Table_2.Amt_2 where Table_1.Acct_Name equals Table_2.Acct_Name. So I run the following update query:

UPDATE Table_3, 
Table_1 INNER JOIN Table_2 ON [Table_1].[Acct_Name] = [Table_2].[Acct_Name] 
SET [Table_3].[Final_Value] = [Table_2].[Amt_1]
WHERE (((Abs([Table_1]![Amt]-[Table_2]![Amt_1]))<(Abs([Table_1]![Amt]-[Table_2]![Amt_2])))

Somehow when I run the above update query all rows in Table_3.Final_Value are equal. What!?

Any help would be appreciated.Thanks in advance.


The Update statement you posted doesn't include any join to table_3. This leads to a cross join and all the rows will be set to the same (rather random) value.

Here's the query that should perform the correct update:

UPDATE (Table_1 INNER JOIN Table_2 ON Table_1.Acct_Name = Table_2.Acct_Name)  
INNER JOIN Table_3 ON Table_2.Acct_Name = Table_3.Acct_Name 
SET Table_3.Final_Value = [Table_2].[Amt_2]
WHERE Abs([Table_1].[Amt]-[Table_2].[Amt_1])<Abs([Table_1].[Amt]-[Table_2].[Amt_2]);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.