I want to display employees who match both the following two criteria

1. earningcode =2111 , monthlyamount= 3040 , TermsOfservce < 5
2. earningcode =2131 , monthlyamount= 1200 , TermsOfservce < 5

when I use the following SQL statement, it displays both those who meet
the two criteria including others who meet only one


SELECT tblEmployees.EmployeeNo, tblEmployees.EmployeeName,
tblEmployeeEarnings.EarningCode,
tblPayrollCodes.NameOnPayslip , tblEmployeeEarnings.MonthlyAmount FROM (tblEmployees
INNER JOIN tblEmployeeEarnings ON tblEmployees.EmployeeNo = tblEmployeeEarnings.EmployeeNo)
INNER JOIN tblPayrollCodes ON tblEmployeeEarnings.EarningCode = tblPayrollCodes.ItemCode
WHERE (((tblEmployeeEarnings.EarningCode)=2111)
AND ((tblEmployeeEarnings.MonthlyAmount)=3040)
AND ((tblEmployees.TermsOfService)<5))
OR (((tblEmployeeEarnings.EarningCode)=2131)
AND ((tblEmployeeEarnings.MonthlyAmount)=1200)
AND ((tblEmployees.TermsOfService)<5))

Where am I going wrong? Pse assist

I'm confused.

How can the value for earningcode be both 2111 and 2131 for a single record?

The list of records that satisfy both of your criteria groups at once will be 'Null'

You are already finding those that satisfy one or the other of your criteria.


What is it you are after?

You have extraneous parentheses that are confusing the fact that the important ones are improperly placed.

WHERE (tblEmployeeEarnings.EarningCode = 2111
AND tblEmployeeEarnings.MonthlyAmount = 3040
AND tblEmployees.TermsOfService < 5)
OR (tblEmployeeEarnings.EarningCode = 2131
AND tblEmployeeEarnings.MonthlyAmount = 1200
AND tblEmployees.TermsOfService < 5);

Thanks KKilfoil

I am after the employees who have both code 2111 with a value of 3040 and also code 2131 with a value of 1200. The returned employees must also have a Terms of service code less than 5.

I hate to ask a stupid question but since you seem to have ignored my response entirely, did you fix the problem with your parentheses?