Q: Using an Excel function to lookup Access table

[John]

I have an Access table that stores data used as a payment schedule.
For example, two records would be these:
StartingSales ($0), EndingSales ($1000), CommissionRate (.12)
StartingSales ($1001), EndingSales ($5000), CommissionRate (.15)

I need help understanding how I can get an Excel function to use this
data to determine what an employees commission will be based on this
"schedule." I believe I should use SELECT and CASE statements to set
up the ranges based on the Access data...i just don't know how to pull
it in to use.


Function Commission(Sales) <----Sales is an Excel range
Select Sales
Case 0 to 1000 <----- This is the same as the Access record
Commission = Sales * .12 <-----same as Access record (again)

So, how can I use the Access records to plug in the Case X to X part
and Sales * X?

Any help is really appreciated!

 

[Earl Kiosterud]

John,

Take a look at Data - Import external data - New database query. Once you
set it up to retrieve data from an Access table, you can run it any time you
want (Right click it and select ! ). You can also put in criteria for the
rows you want.