Edited after testing the aircode
first find the minimum value - query Q1
Code:
SELECT table1.name, table1.value, min(abs(table1.value-table2.value)) as minnum
FROM table1 inner join table2 on table1.name=table2.name)
GROUP by Table1.name, table1.value
next join this to table2 - query Q2
Code:
SELECT table2.name, Q1.minnum, min(table2.ID) as minID
FROM table2 INNER JOIN Q1 ON table2.name=Q1.name AND table2.value=Q1.minnum
WHERE (((Table2.value) Between [q1].[value]-[minnum] And [q1].[value]+[minnum]))
GROUP BY table2.name
finally update table1
Code:
UPDATE Table1 SET Number_of_nearest_ID=DLookUp("minID","Q2","Name='" & [Table1].[name] & "'");
Note I really hope your example naming is just for convenience. If you are actually using them then be aware Name and Value are reserved words and should not be used for field names (e.g. fld.name will return the name of a field, fld.value will return it's value) - you will get weird errors at some point