Lookup data across tables using next-highest value

[littlespoon]

Hi,



I hope you guys can help me with this. (I also hope I can explain it clearly!)

So. I have a master database (TABLE 1), and I want to write a query that will show the records' data, including price, and also show the corresponding price tier.

In other words, I want to write a query that will do the below:
1) Get price (a) from Table 1
2) Look up price (a) in a lookup table (which is called Tier Lookup. Price field is called 'GBP' and tier is called 'Tier'). Round up if necessary.
3) Pull the corresponding tier from Tier Lookup.


For example, if a record is priced at £1.75, I want my query to check the tier lookup, see that the next tier is £1.99, and return the corresponding tier.

If you could explain it using Design View, that would be ideal -- I am much less confident at the SQL side of Access.

Thank you!

[JamesDeckert]

Can you provide a few records from your Tier Lookup table.

You could call a VBA function with the price passed to the function. The function returns the tier value.