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:
Table_2 has the following fields:
Table_3 has the following fields:
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.