Lookup Between Two Tables

[NumberXYZ]

Hello

I've done this in excel but can't replicate in access.

Table 1
Member Id Name Income Score
765849 Davis 51,000
678459 Smith 52,500
567483 Jones 54,00

Table 2
Income Score
50,000 20
51,000 30
52,000 40
53,000 50
54,000 60

I'm trying to update the field in table one with the nearest score in table 2..?!

Any ideas welcome

 

[Tranman]

NumberXYZ,
Do you mean the score that's "nearest", or the score range that the income falls in? If you mean the range, something like this will do:

SELECT TB1.[Member Id], TB1.Name, TB1.Income, TB2.Score, TB2.Income
FROM TB2, TB1
WHERE (((TB2.Income)>=[TB1.Income] And (TB2.Income)<[TB1.Income]+1000));

If you really did mean nearest (income of 51,900 would relate to score of 40), I'd break up the Table 2 on nn,500 increments, then use the technique above.

Good Luck,
Tranman

 

[gol4]

assuming you are rounding up and using those values

update table1 set score = (select score from table2 where left(table2.income,2) = left(table1.income))


otherwise follow same principle using math function to round table 2 then find match in table1