Trying to find records NOT IN

[sue0912]

Hi all,

I just want to see if I am on the right track to get the results I am looking for....I have looked at it to long and need another set of eyes.

My goal is try and display the records that don't equal a certain criteria...

For example Sara's criteria is suppose to be FTE 1, BU 2, HRS 80 and ACCR_CODE B0280Y

BUT the database is set to FTE 0.3, BU 2, HRS 80 and ACCR_CODE B0280Y

SO I want her to display her info in my query...if her criteria was 1 ,2 80 and B0280Y I don't want to display her info

I am using MS Access right now, but here is what I have so far....

SELECT DISTINCT [7_19_acc_ck].EMP_ID, [7_19_acc_ck].FNAME, [7_19_acc_ck].LNAME, depts.LOCANAME, [7_19_acc_ck].ST, [7_19_acc_ck].TITLE, [7_19_acc_ck].FTE, [7_19_acc_ck].BU, [7_19_acc_ck].HRS, [7_19_acc_ck].CI, [7_19_acc_ck].BASIS, [7_19_acc_ck].SALARY, [7_19_acc_ck].ACCR_CODE, [7_19_acc_ck].ACCR_SEQ, [7_19_acc_ck].PL_DATE, [7_19_acc_ck].VAC_ANN_DATE, [7_19_acc_ck].SUPVR_ID, [7_19_acc_ck].SUPVR_FNAME, [7_19_acc_ck].SUPVR_LNAME, tk_assignment.USERID_ANN, tk_assignment.USERID_BIW, accrual_maxtrix.Active
FROM ((7_19_acc_ck LEFT JOIN accrual_maxtrix ON ([7_19_acc_ck].ACCR_SEQ=accrual_maxtrix.ACCR_SEQ) AND ([7_19_acc_ck].ACCR_CODE=accrual_maxtrix.ACCR_CODE)) LEFT JOIN depts ON [7_19_acc_ck].EMP_ID=depts.EMP_ID) LEFT JOIN tk_assignment ON depts.DEPT_CODE=tk_assignment.DEPT_CODE
WHERE [7_19_acc_ck].BU NOT IN (Select [7_19_acc_ck].BU FROM 7_19_acc_ck WHERE [7_19_acc_ck].FTE=1 AND [7_19_acc_ck].BU=2 AND [7_19_acc_ck].HRS=80 AND [7_19_acc_ck].ACCR_CODE='B0280Y')
OR [7_19_acc_ck].BU NOT IN (Select [7_19_acc_ck].BU FROM 7_19_acc_ck WHERE [7_19_acc_ck].FTE=0.5 AND [7_19_acc_ck].BU=5 AND [7_19_acc_ck].HRS=40 AND [7_19_acc_ck].ACCR_CODE='B0540P')
;

Thanks
Sue

 

[lameid]

Looks like your problem is with an or and multiple NOT IN statements. From your text it sounds like you have an example you want to see which looks like it is the where clause of the second not in Select... You could remove the NOT so it is IN instead of Not IN but I think you simply want to remove the OR and second not In criteria altogether. The thing is that either it is not going to match the first or it isn't going to match the second, so you are getting everything.

[7_19_acc_ck].BU NOT IN (...)
OR [7_19_acc_ck].BU NOT IN (...)

I guess the question is are you intending to exclude or limit your records anyother way?