Report back a fee based on time

[vangogh228]

Hello.

I have been given a set of pricing tables in Excel that, based on the type of work done, show a dollar amount for time ranges. In other words, if WorkType = A, the appropriate pricing table is referenced and the amount of time in minutes is found within ranges ("0 - .3333", ".3333 - .6500", etc) shown in hour decimals (.3333 = 20 minutes), then an associated dollar amount is reported back. (I know... very awkward, but not my choice). The time ranges are not consistent in size either within a particular pricing table nor across tables. The person using these tables is doing this manually now.

What I need to be able to do is enter the WorkType and duration, and get the dollar amount from the table, even though the duration is not going to exactly match the value in the table. In Excel's VLOOKUP, you can put in amounts that not exact, and the function finds the highest value less than your entry. Can Access do that? I tried reading up on DLOOKUP, but I don't think that is what I need (or maybe I just don't understand it). I'm not sure of an efficient table setup or how to get the correct output.

Thanks for all your help!!

Tom

 

[The_Doc_Man]

The answer depends on how the spreadsheet data are presented. Assuming you either (a) import the spreadsheet or (b) link to it, if it is suitable for linking...

This is either an SQL "Find First" problem, or it is a case where a "Between...And" operation would work. Without knowing the structure of the spreadsheet, I can't begin to know which will be correct.

If a "find first" type of problem, what you want to do is (perhaps) a dlookup of the rate where you get the maximum time limit less than the time you enter (or the minimum time limit greater than the time you enter, hard to tell from your presentation). One of those two will give you what you wanted.

If a "between / and" type of problem, you would have two times in each of two columns, with a number as the third column. Then you would want to find the rate corresponding to the record where your input time is between the lower time and the upper time for that record.

 

[vangogh228]

Not importing or linking. I am looking to set up a new way of maintaining these price sheets that will be easier and more accessible. Thanks for the help. I will check out "Between...and."