Updating a field based on a range of values

[jobb]

can someone please help me do this?

I have 2 fields [TotalVolume] and [ComissionRate] in one table. This field can have any number in it, for example $845,231.

In my other table I have a chart with round numbers in the field [Volume] ($600,000, $650,000, $800,000, $900,000), and their respective basis points (another field [BPs].. (75, 85, 90, 95).

In this example, since $845,231 is between $800,000 and $899,999, I would like to update the [ComissionRate] field for that particular record to 90.

I can do this in Excel using VLOOKUP, but am unable to figure out how to solve this in Access.

Any help?

Thanks

 

[SydneyGeek]

Not sure, but you may get some joy out of DLOOKUP. I know that it does exact matches (like VLOOKUP(...,...,...,FALSE) but I'm not sure that it does "step" matches like you want.

If not, you may need to use an expression to fix the problem -- but that may hard-wire the step values and rates.