Update query for Minimum absolute difference in Access [SQL]

Question

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.

Answer 1

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]);