IIF Statement Pulling Too Many Records 2

[awl]

1. I have 3 Job Titles: [PEPolice1], [PEDriver1] and [PSWSP1].
2. The 2 “Meets/Does Not Standards”: [FRMS1] and [FRDNMS1] apply only to the Police and Driver.
3. The [PSWSP1] may have only 4 of the 8 “Other Standards”: [NoEvidA1]+[NoEvidB1]+[NoPersA1]+[NoPersB1]+[NoAllegA1]+[NoAllegB1]+[AllegA1]+[AllegB1]. These 8 standards do not apply to the Police or Driver.

The outcome for either [PEPolice1] or [PEDriver1] is either [FRMS1] or [FRDNMS1]; and [PSWSP1] is 4 of the 8 Standards. A 1 in either of the fields indicates a Yes.

PDOJSActions:

IIf([PEPolice1]=1 Or [PEDriver1]=1 And [NoEvidA1]+[NoEvidB1]+[NoPersA1]+[NoPersB1]+[NoAllegA1]+[NoAllegB1]+[AllegA1]+[AllegB1]>0,"Standards Need Corrections",

IIf([PSWSP1]=1 And [FRMS1]+[FRDNMS1]>0,"Standards Need Corrections","Records OK"))


Some new staff members are making multiple entries. With the IIF statement provided I have identified those records of [PEPolice1] and [PEDriver1] who have checked either [FRMS1] or [FRDNMS1] and have also checked 1 or more of the Other 8 Standards. I also have identified those records of [PSWSP1] who have checked either [FRMS1] or [FRDNMS1].

However, the query also includes those records of [PEPolice1] and [PEDriver1] who have only checked either [FRMS1] or [FRDNMS1] and none of the 8 Standards. I want to exclude those records since those records are the appropriate outcomes. Any suggestions would be helpful. Thank you.

 

[lespaul]

You have provided a lot of detail and information, which is good, but you haven't really provided the context to decipher your information.

There must be a table or two involved somewhere? Are any of the [NAMES] above in the tables? Which ones belong where? How are the tables related? Are all the field [NAMES] regarding 'standards' logical (T/F) fields?

What is the existing SQL for the query that almosts works?

THanks,

leslie

Leslie

 

[awl]

I apologize for not sending the complete SQL for the “qryDIR6 Police Driver Other Job-Standards A”. In bold is the part of the query in question. All the selected fields in the table are True/False except the first 4; MFSID, Name, SSN and DateOfExam. If this is not sufficient, please let me know.

SELECT
[tblMFS Master Data List].[MFSID#],
[tblMFS Master Data List].Name,
[tblMFS Master Data List].SSN,
[tblMFS Master Data List].DateOfExam,

IIf([PEPolice1]=1,"Police",IIf([PEDriver1]=1,"Driver",IIf([PSWSP1]=1,"Other","Need Correction"))) AS JobTitle,
IIf([PSExam]=True,1,0) AS PSExam1,
IIf([PEPolice]=True,1,0) AS PEPolice1,
IIf([PEDriver]=True,1,0) AS PEDriver1,
IIf([PSWSP]=True,1,0) AS PSWSP1,
IIf([FRMS]=True,1,0) AS FRMS1,
IIf([FRDNMS]=True,1,0) AS FRDNMS1,
IIf([NoEvidA]=True,1,0) AS NoEvidA1,
IIf([NoEvidB]=True,1,0) AS NoEvidB1,
IIf([NoPersA]=True,1,0) AS NoPersA1,
IIf([NoPersB]=True,1,0) AS NoPersB1,
IIf([NoAllegA]=True,1,0) AS NoAllegA1,
IIf([NoAllegB]=True,1,0) AS NoAllegB1,
IIf([AllegA]=True,1,0) AS AllegA1,
IIf([AllegB]=True,1,0) AS AllegB1,

IIf([PEPolice1]=1 Or [PEDriver1]=1 And [NoEvidA1]+[NoEvidB1]+[NoPersA1]+[NoPersB1]+[NoAllegA1]+[NoAllegB1]+[AllegA1]+[AllegB1]>0,"Standards Need Corrections",IIf([PSWSP1]=1 And [FRMS1]+[FRDNMS1]>0,"Standards Need Corrections","Records OK")) AS PDOJSActions,

IIf([JobTitle]="Other" And [NoEvidA1]+[NoEvidB1]+[NoPersA1]+[NoPersB1]+[NoAllegA1]+[NoAllegB1]+[AllegA1]+[AllegB1]<>4,1,0) AS OtherMST,
[tblMFS Master Data List].PSExam,
[tblMFS Master Data List].PRpt,
[tblMFS Master Data List].FRpt
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PSExam)=True) AND (([tblMFS Master Data List].PRpt)=False))
ORDER BY [tblMFS Master Data List].Name;

 

[lespaul]

OK, easy enough.

The reason the iff statement doesn't work correctly is because you can't use the alias, you have to use the formula again.


I'm not really sure why you are doing all those iif statements anyway! A logical field (T/F) is already stored as 0 and -1! Try this and let me know whether it works for you or not!

Leslie

SELECT 
[tblMFS Master Data List].[MFSID#], 
[tblMFS Master Data List].Name, 
[tblMFS Master Data List].SSN, 
[tblMFS Master Data List].DateOfExam, 

IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Driver",IIf([PSWSP]=True,"Other","Need Correction"))) AS JobTitle, 

PSExam, PEPolice, PEDriver, PSWSP, FRMS, FRDNMS, NoEvidA, NoEvidB, NoPersA, NoPersB, NoAllegA, NoAllegB, AllegA, AllegB, 

IIf([PEPolice]=True Or [PEDriver]=True) And ([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])*(-1))>0,"Standards Need Corrections",IIf([PSWSP]=True And (([FRMS]+[FRDNMS])*(-1))>0,"Standards Need Corrections","Records OK")) AS PDOJSActions, 

IIf([PSWSP]=True And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB]) *(-1))<>4,1,0) AS OtherMST, 
[tblMFS Master Data List].PSExam, 
[tblMFS Master Data List].PRpt, 
[tblMFS Master Data List].FRpt
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PSExam)=True) AND (([tblMFS Master Data List].PRpt)=False))
ORDER BY [tblMFS Master Data List].Name;

 

[awl]

Thank you. I copied directly the SQL code, however we had a problem.
1. The first error: Extra) in query expression ‘IIf([PEPolice]=True Or [PEDriver]=True) And ([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])*(-1))>0’. Result: After pressing OK, the cursor went to the last closing parenthesis before the > sign which I highlighted in red. I deleted that closing parenthesis if it was appropriate.
2. The second error: Extra) in query expression ‘IIf([PSWSP]=True And (([FRMS]+[FRDNMS])*(-1))>0,"Standards Need Corrections","Records OK"))’. Result: After pressing OK, the cursor went to the last closing parenthesis which I highlighted in red. Again I deleted that closing parenthesis if it was appropriate.
3. The third error: Wrong number of arguments used with function in query expression ‘IIf([PEPolice]=True Or [PEDriver]=True) And ([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])*(-1)>0’. Here I am stuck. On another occasion I deleted the closing parenthesis after the True) in #1 above, however errors 2 and 3 above followed again.

 

[lespaul]

OK, i checked the parens and think I've got it all sussed out. Try this:

SELECT 
[tblMFS Master Data List].[MFSID#], 
[tblMFS Master Data List].Name, 
[tblMFS Master Data List].SSN, 
[tblMFS Master Data List].DateOfExam, 

IIf ([PEPolice]=True,"Police",IIf([PEDriver]=True,"Driver",IIf([PSWSP]=True,"Other","Need Correction"))) AS JobTitle, 

PSExam, PEPolice, PEDriver, PSWSP, FRMS, FRDNMS, NoEvidA, NoEvidB, NoPersA, NoPersB, NoAllegA, NoAllegB, AllegA, AllegB, 

IIf(([PEPolice]=True Or [PEDriver]=True) And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])*(-1)) > 0,"Standards Need Corrections",IIf([PSWSP]=True And (([FRMS]+[FRDNMS])*(-1))>0,"Standards Need Corrections","Records OK")) AS PDOJSActions, 

IIf([PSWSP]=True And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB]) *(-1))<>4,1,0) AS OtherMST, 
[tblMFS Master Data List].PSExam, 
[tblMFS Master Data List].PRpt, 
[tblMFS Master Data List].FRpt
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PSExam)=True) AND (([tblMFS Master Data List].PRpt)=False))
ORDER BY [tblMFS Master Data List].Name;

Leslie

 

[JonFer]

For True/False fields, you don't need to add the "=True" when checking that they are True. See below for examples. It should make it easier to maintain.

I also removed the -1 multiplies and either made the other side of the equation negative or swapped the < > signs.

IIf ([PEPolice],"Police",IIf([PEDriver],"Driver",IIf([PSWSP],"Other","Need Correction"))) AS JobTitle,

IIf(([PEPolice] Or [PEDriver]) And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])) < 0,"Standards Need Corrections",IIf([PSWSP] And (([FRMS]+[FRDNMS]))<0,"Standards Need Corrections","Records OK")) AS PDOJSActions,

IIf([PSWSP] And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB]) )<> -4,1,0) AS OtherMST,

 

[awl]

Lespaul: Yes, the corrected query works. Lespaul and JonFer: However, there are two situations that the records are not being pulled if I may: 1) For [PEPolice] and [PEDriver] Job Titles, there were no checks entered for either [FRMS] or [FRDNMS], which there is supposed to be 1 entry (1 or the other). Of these same 19 individuals, no entry was made in the 8 “Other Standards” which is correct. 2) Only one case of job title [PSWSP] where there were no entries in [FRMS] or [FRDNMS] which is correct, however of the 8 “Other Standards”, only one was checked, where as there should be 4 areas checked. I hope the two situations are clear. Note: I transferred my Access Data to an Excel spreadsheet to search for possible errors for that is one reason for me using the IIF Statements for 1’s were easier to use than T/F.

 

[lespaul]

I'm not clear on your two issues.

Your first issue deals with this clause?:

IIf ([PEPolice]=True,"Police",IIf([PEDriver]=True,"Driver",IIf([PSWSP]=True,"Other","Need Correction"))) AS JobTitle

Are you saying that it doesn't work sometimes?


And your second issue is with this clause?:

IIf([PSWSP]=True And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB]) *(-1))<>4,1,0) AS OtherMST




Leslie

 

[awl]

Sorry, the 2nd issue was cleared by your 2nd clause. The first issue though: If [PEPolice] Or [PEDriver] fields are checked And either [FRMS] or [FRDNMS] are not checked, then these records need: "Standards Need Corrections". I believe it would apply to the following coding. I should not have used the word job titles. Hope this clarification is better.

IIf(([PEPolice] Or [PEDriver]) And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])) < 0,"Standards Need Corrections",IIf([PSWSP] And (([FRMS]+[FRDNMS]))<0,"Standards Need Corrections","Records OK")) AS PDOJSActions,

 

[lespaul]

Ok, this clause is doing the following:


1. If PEPolice or PEDriver is checked and the sum of all that stuff is less than zero then enter 'Standards Need Corrections'

2. if the above is false, then check if PSWSP is true AND one or both of FRMS and FRDNMS is not checked then enter 'Standards Need Correction'

3. if the second condition is false then 'Records OK'.

So all the stuff you add up has to be zero in order to get to the second iif statement.

perhaps if you change it to:

IIf((([PEPolice] Or [PEDriver]) And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])) < 0) OR (([PSWSP] And (([FRMS]+[FRDNMS]))<0),"Standards Need Corrections","Records OK")) AS PDOJSActions,

HTH

leslie

 

[awl]

If I may, I had a bit of trouble getting the query to work for the error message kept appearing: Syntax error (comma) in query expression ‘IIf((([PEPolice] Or [PEDriver]) And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])) < 0) OR (([PSWSP] And (([FRMS]+[FRDNMS]))<0),"Standards Need Corrections","Records OK"))’ Thanks for your patience!

 

[JonFer]

IIf(
     (
       ([PEPolice] Or [PEDriver]) 
       And 
       (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])< 0)
     ) 
     OR 
     (
       [PSWSP] And (([FRMS]+[FRDNMS])<0)
     ),
     "Standards Need Corrections",
     "Records OK"
   )

 

[lespaul]

thanks jonfer for fixing that pesky little comma error!

Glad you finally got it working awl!

Leslie

 

[awl]

I was not pulling those records who did check either [PEPolice] Or [PEDriver] and “did not” check either [FRMS] Or [FRDNMS]. So I created a new field in my query which pulls those records, although 3 of those records included records that have active checks in the 8 Other Standards which were being pulled in [PDOJSActions] field. What I have now is working for me.

PDNoMS: IIf(([PEPolice] Or [PEDriver]=True) And (([FRMS]+[FRDNMS])*(-1))<>1,"Standards Need Corrections","Records OK")

So my full SQL is as follows. I thank both LesPaul and JonFer for your help.


SELECT [tblMFS Master Data List].[MFSID#], [tblMFS Master Data List].Name, [tblMFS Master Data List].SSN, [tblMFS Master Data List].DateOfExam, IIf([PEPolice],"Police",IIf([PEDriver],"Driver",IIf([PSWSP],"Other","Need Correction"))) AS JobTitle, PEPolice, PEDriver, PSWSP, FRMS, FRDNMS, NoEvidA, NoEvidB, NoPersA, NoPersB, NoAllegA, NoAllegB, AllegA, AllegB, IIf((([PEPolice] Or [PEDriver]) And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])<0)) Or ([PSWSP] And (([FRMS]+[FRDNMS])<0)),"Standards Need Corrections","Records OK") AS PDOJSActions, IIf([PSWSP]=True And (([NoEvidA]+[NoEvidB]+[NoPersA]+[NoPersB]+[NoAllegA]+[NoAllegB]+[AllegA]+[AllegB])*(-1))<>4,1,0) AS OtherMST, IIf(([PEPolice] Or [PEDriver]=True) And (([FRMS]+[FRDNMS])*(-1))<>1,"Standards Need Corrections","Records OK") AS PDNoMS, [tblMFS Master Data List].PSExam, [tblMFS Master Data List].PRpt, [tblMFS Master Data List].FRpt
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PSExam)=True) AND (([tblMFS Master Data List].PRpt)=False))
ORDER BY [tblMFS Master Data List].Name;