I'm sure there's an easy solution to this somewhere...

I have a query that is supposed to assign a wage to a production job if the range output falls within a certain percentage range. For example, if they produce 125% to 130% of their goal, their wage is 15 cents higher. If it's between 130% and 135% of their goal, their wage is 30 cents higher. There are about 20 ranges that my bosses want set up. Short of making the longest Iif-Then-Else statement in history, does anybody have any suggestions? Should I use a separate table and somehow try to integrate it into the query?

The problem with using a table is how to lookup the range, I suppose you could put ALL the values in and look up individually not between.. and ,if thats clear.

Or you could write a function and use Select Case where you can use comparator operators, but then the values are hard coded.

I think . if the values are likely to change I would use a table and Dlookup even if it means entering all the values.
You may not have to use Dlookup you may be able to use a join which would be better.

Brian

Well, I've tried putting the two sets of values (percentage and the corresponding wage) into another table, then attempted a DLookup to make it work. The original table is a linked table (Excel spreadsheet), so I'm wondering if that has an effect. I can't do one-to-many on the relationship, obviously, so I'm wondering what alternatives I now have.

I think a table would work, with fields for the upper and lower levels (125 & 130) and the amount (.15). You could then join to that table with a non-equi join (>= instead of =)

|That's interesring Paul, I've never seen a non-equi join , how does that work. If say 125-129 is 15% and 130-135 is 30% then how is it laid out with 4 values or, well I cannot think it through, I would have just bitten the bullet and had all the values from 125 to 135 and a straight inner join.

Brian

They are handy Brian. Here's the SQL from a sample I put together to help somebody. It finds the rate appropriate to both a date and line:

SELECT tblActivity.Order_num, tblActivity.DateField, rate
FROM tblActivity LEFT JOIN Rates ON (tblActivity.DateField Between Rates.StartDate and Rates.EndDate) AND (tblActivity.Order_num = Rates.Line);

I would look the rate up and store it in the table with the rest of the production job data. :)

Thanks Paul, but why did I have to wait till I was retired to dicover that. :(:D

Brian

Thanks for the information. After several reduxes, I got this to work with my database. I just hope they never have to fix it, because this really isn't designed for the QBE grid. :D

They are handy Brian. Here's the SQL from a sample I put together to help somebody. It finds the rate appropriate to both a date and line:

SELECT tblActivity.Order_num, tblActivity.DateField, rate
FROM tblActivity LEFT JOIN Rates ON (tblActivity.DateField Between Rates.StartDate and Rates.EndDate) AND (tblActivity.Order_num = Rates.Line);

What happens if a rate changes? Seems the whole thing will be amuck - ??

The whole point of using a table is to allow the user to change the values.

I think murphy's last post followed his law and he didn't actually post his solution but Paul's example.

Brian

If I understand correctly (malt whisky kicking in), then this is a prime candidate for a cartesian join (see attached d/b). It's neat and tidy and easy to maintain. It's more overhead for the pc but quite frankly I just don't care. My PC spends the majority of its time doing nothing anyway :p. Note the absense on a proper join in the query.

Chris

The one thing I liked in my 40+years in IT was that you never stop learning.

What Malt is it? I think I'll join you with a Talisker, not my favourite but it was bought for me so must be drunk.

Brian

What Malt is it? I think I'll join you with a Talisker, not my favourite but it was bought for me so must be drunk.

Brian
Talisker (full flavour, sweet & slight peat) is my favourite but I've run out :( I have many malts (depending on the mood) but I'm sticking with Coal Ila tonight. How about you (I note you taste for a good malt in your prev posts)?

Oops, maybe going off thread slightly :o

If I understand correctly (malt whisky kicking in), then this is a prime candidate for a cartesian join (see attached d/b). It's neat and tidy and easy to maintain. It's more overhead for the pc but quite frankly I just don't care. My PC spends the majority of its time doing nothing anyway :p. Note the absense on a proper join in the query.

Chris

Wouldn't ya know it: that works, too. And it might work better since I have to flag certain records to have a flat rate instead of one based on production.

Don't you just love tearing your hair out for hours on end, only to find the solution was ridiculously simple? :eek:

I'm still not getting through...

Say job number 101 runs and they do 130 percent of the gaol and according to the rate table they get paid an extra 15 cents an hour.

Now a year later the rate for 130 percent is changed to pay an extra 20 cents an hour. If you run a query with the join as described, job 101 will look like it was paid at 20 cents an hour. The entire reason for putting the rates in a table is useless, you may as well have have hard coded it. That's why I suggested storing the rate paid in the job table. Unless you want to build an entire rate change history model, etc...

Or am I still missing something?

When you use a rate table you also need to keep the effective date and for ease of use and expiration date also. so the query actually has a two part non-equi-join.

I'm still not getting through...

Say job number 101 runs and they do 130 percent of the gaol and according to the rate table they get paid an extra 15 cents an hour.

Now a year later the rate for 130 percent is changed to pay an extra 20 cents an hour. If you run a query with the join as described, job 101 will look like it was paid at 20 cents an hour. The entire reason for putting the rates in a table is useless, you may as well have have hard coded it. That's why I suggested storing the rate paid in the job table. Unless you want to build an entire rate change history model, etc...

Or am I still missing something?

Ah, good point. Fortunately, once the rates have been added to the records, those records are appended to another table, then sent to an online source for processing. Everything has to be completed by the Monday following the week it occurred.