Query for all variations of 3 data types

[skcrival]

Info below shows which benefits an employee receives based on their status (full-time/part-time) and yearly hours worked.

Benefits EE Status Hrs/Year
100% Full-Time Regular 2080
80% Part-Time Regular 1664-2028
70% Part-Time Regular 1248-1820
NONE Part-Time Regular <1248

The table I'm querying against contains all 3 of these fields/columns for each employee. How do I specify criteria that would return 1 column matches, columns 2 and 3 don't match AND records where column 2 matches but columns 1 and 3 don't and so forth?

Anotherwards capture all variations that don't match exactly across (e.g. 100%; Full-Time Reg; 2080), where any one of the three fields are incorrect.

 

[randy700]

I don't think you'll be able to easily query using the Hrs/Year field you currently have. Consider changing it to reflect just the MAXIMUM value. Then your queries can look for:
1. Status = Full-Time
2. Status = Part-Time and Hrs/Year < ####

For the part-time employees, use a case (or if) statement, something like:

SELECT Case Hrs/Year
    Case < 1248:
        Benefits = 0
    Case < 1821:
        Benefits = 70
    Case < 2029:
        Benefits = 80
    Case Else:
        Benefits = ??
End Select

Randy

 

[skcrival]

I'm not familiar with Case Statements.
How would I go about setting this up in the query design view of Access (2002)?

 

[randy700]

Something like....

iif([Hrs/Yr] < 1248, 0, iif([Hrs/Yr] < 1821, 70, iif([Hrs/Yr] < 2029, 80, ??)))

Randy