Greater/Less Than Relationship to table

[Guest]

I am working on an incentive program that is going to use hours worked as a
factor. I want to compare hours worked to a table that will bring up a
corresponding factor. Example table:

Hours Worked Factor
2080 1.00
1560 .75
1040 .50

If hours are 2080 or more, then I want a factor of 1.00. If hours are more
than 1,560 but less than 2,080, I want a factor of .75 to show up and so on.
I know I could write a nested IF function if all else fails but just wanted
to see if there is a way to get this to work in case I need something more
complicated like this in the future. Thanks

 

[Douglas J Steele]

You should be able to adapt what I described in my December, 2004 "Access
Answers" column in Pinnacle Publication's "Smart Access" to your situation.

You can download the column (and accompanying sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Guest]

When you join tables it boils down to the expression in the JOIN clause of
the query, or a join expression in the WHERE clause if you do it that way,
evaluating to TRUE for each combination of rows from the two tables, so you
just need an expression which evaluate to TRUE if the hours worked value
falls with in a range determined by two values in a row of the other table.
Lets say the main table is called Timesheet and has columns EmployeeID and
HoursWorked (this can of course be a computed column in a query based on the
aggregation of values from a set of rows, in which case Timesheet would be
the query rather than a base table).

Lets assume the other table, which will be a base table, is called Factors.
It should have the Factor column, which can be its primary key, and two other
columns LowValue and HighValue say. The values for the Factor 0.75 row would
be 1560 (LowValue) and 2080 (HighValue). As factor one covers an open ended
range (>2080) include an artificially high value for this, 10000 say.
Similarly for the bottom factor you'd probably have a LowValue of 0. Note
that the HighValue for one row is the LowValue for the next factor up.

A query to join the tables would be like something this:

SELECT EmployeeID, Hours, Factor
FROM Timesheet INNER JOIN Factors
ON Timesheet.HoursWorked >= Factors.LowValue
AND Timesheet.HoursWorked < Factors.HighValue;

or using a join criterion in the WHERE clause:

SELECT EmployeeID, Hours, Factor
FROM Timesheet, Factors
WHERE Timesheet.HoursWorked >= Factors.LowValue
AND Timesheet.HoursWorked < Factors.HighValue;

You'll see that the first part of the expression uses a >= operator while
the second part a < operator. This is why the HighValue of one row is the
LowValue of the row for the next factor. You can't use a BETWEEN….AND
operation in the join here as that would join any row in Timesheet with a
value exactly at the start of a range, e.g. 2080 to two rows in Factors. In
any case a BETWEEN….AND operation can't be used in a JOIN clause, though it
can be used as a join criterion in a WHERE clause.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks for the answers! I'm sure one or both of those will work. I just
started using Access a few weeks ago as you can tell.