Multiple IIF statements in Query

[indyaries]

Work-Access 97 SR1
Home-Access 2000

Greetings,

Apologizing for the long post. Analyzing Time and Billing. I receive a flat file in Excel every two weeks, which I append to an Access file. A large portion of the analysis is capturing errors between the HourTypeCode and WC5. As of now, I have over 12K records to process.

Example: If HourTypeCode = LS then WC5 must equal GL000007. If WC5 is not GL000007 then it's an error.

I've played with several methods of doing this. First was using a query for every single type of error, then creating a new table called t_Errors, and appending the results of each query into the Errors table. I would then use the Errors table to generate the reports I'm responsible for.

I've also played with IIF statements. Here is an SQL example:
SELECT t_EmplInfo.BUserId, t_EmplInfo.EmplName, t_EmplInfo.PERN, t_EBiz_YTD.ID, t_EBiz_YTD.HourTypeCode, t_EBiz_YTD.HourTypeDescription, t_EBiz_YTD.WC5, t_EBiz_YTD.WC5Desc, IIf([HourTypeCode]=&quot;LS&quot; And [WC5]<>&quot;GL000007&quot;,&quot;HrCd LS/WC5<>GL000007&quot;,Null) AS TypeErrors
FROM t_EmplInfo INNER JOIN t_EBiz_YTD ON t_EmplInfo.BUserId = t_EBiz_YTD.BUserId
WHERE (((IIf([HourTypeCode]=&quot;LS&quot; And [WC5]<>&quot;GL000007&quot;,&quot;HrCd LS/WC5<>GL000007&quot;,Null)) Is Not Null));

Is it possible for me to use multiple criteria in my IIF statements? As an example, here are three IIFs that are in my query grid which are looking for three distinct errors. LS=SickLeave, LA=AnnualLeave, LN=AdminLeave:
Query1: TypeErrors: IIf([HourTypeCode]=&quot;LS&quot; And [WC5]<>&quot;GL000007&quot;,&quot;HrCd LS/WC5<>GL000007&quot;,Null)
Query2: TypeErrors: IIf([HourTypeCode]=&quot;LA&quot; And [WC5]<>&quot;GL000002&quot;,&quot;HrCd LA/WC5<>GL000002&quot;,Null)
Query3: TypeErrors: IIf([HourTypeCode]=&quot;LN&quot; And [WC5]<>&quot;GL000001&quot;,&quot;HrCd LN/WC5<>GL000001&quot;,Null)

As I've stated, there are about 50 HourTypeCodes I have to evaluate. Alas, WC5 is not the only evaluation that needs to be done. If HourTypeCode = LS (Leave Sick) then WC4 must equal GL000 and WC5 must equal GL000007. I have 31 HourTypeCodes that pertain to Leave, and about 19 that pertain to Work. The crux is that sometimes WC4 = GL000 but WC5 <> GL000007, then other times WC4 <>GL000 and WC5 = GL000007

I'll take whatever advice I can get, but my goal would be to minimize the number of queries I have to run, which I then must append to a new table. One record can in fact have multiple errors on it! So, I created another table called t_EmplInfo, which contains as it's PK the field BUserId. I was hoping that in doing this, I could make it clear to those who make the corrections that One Employee can have multiple errors, and that one record could have multiple errors.

Thanks in advance for any suggestions!! Hope I'm not making this sound too difficult.

Bob in Indy

 

[johnnymac43]

However you arrange your queries, your aim of reporting multiple errors concurrently is sound business practice. You could consider using a method that was used extensively in the days of batch data entry (which is essentially what you are dealing with here). The basic concept is as follows:

> Print each line containing one or more errors, immediately followed by a line indicating the error positions (and error types, if required). The result would look like this (underlines = blank spaces):

EMP0023NNMMNNNNNNNNNNNNNNNNNNNNMMNNN
__________^^^^^^^______________^^^^^^^_02_04

> If the maximum number of error types may exceed the capacity of a print line, place ** in the last error type field on the line to indicate 'more errors than can be listed'.

> Use a separator line between each pair of report lines.

> Group multiple error line pairs for an employee on some suitable key in the data (perhaps date). If required, indicate a change of employee by some suitable means.

HTH

John

 

[indyaries]

John,

Thanks for your reply. I think I have an idea how to do the report, of which some are for management analysis, others for error corrections in the Time & Billing system.

Have you any ideas how I can use multiple IIF statements within a query?

 

[johnnymac43]

Hi Bob,

I have used nested IIF statements in a query, but they are a nightmare and best avoided if possible.

Could you set up a table of valid combinations in your data and use that to identify and categorise errors? I'm not sure how it would work, but the 'valid combination' with the highest (but not complete) match on a prioritised field by field comparison could be used to identify the error that prevented a 100% match.

For your example: 'If HourTypeCode = LS (Leave Sick) then WC4 must equal GL000 and WC5 must equal GL000007', the table would contain the following entries in fields named Priority1, Priority2 and Priority3:

Priority1 Priority2 Priority3
LS GL0000 GL000007
LS GL000007 GL0000

By running 2 queries on a record with an error in one of the fields WC4 or WC5, the one to match on fields Priority1 and Priority2 would identify the error. If both fields were wrong, both queries would not find a match beyond Priority1.

Gotta go

Cheers

John

 

[indyaries]

John,

Thanks for your reply. Interesting concept. I gather that you would create a table containing all of the error types that I can identify, and using 2 queries, run this against the main table.

So, if my new table (ErrorTrap) looked something like this...:
Priority1 Priority2 Priority3
LS GL000 GL000007
LS GL000007 GL000
LA GL000 GL000002
LA GL000002 GL000
LN GL000 GL000001
LN GL000001 GL000
.....and so on.....
...how would I set up the two queries to run against the main table? What method is used to assign the error so it can be reported on?

Thanks again!

Bob in Indy

 

[johnnymac43]

Hi Bob

To tackle this I set up the following tables:

> tblTimeData, containing fields for EmployeeNumber, HourTypeCode, WC4 and WC5.
> tblErrorTrap, containing fields Priority1, Priority2 and Priority3 as previously discussed.
> tblTimeError, containing fields as per tblTimeData, plus fields for WC4Error and WC5Error.

I then wrote 3 queries to append records with error indicators to tblTimeError. (The third query is for when both fields are wrong. It is a grouping query to avoid duplicate reporting of these errors. The SQL for each query is as below:

qryAppendWC4Errors
INSERT INTO tblTimeError ( EmployeeNumber, HourTypeCode, WC4, WC5, WC4Error, WC5Error )
SELECT tblTimeData.EmployeeNumber, tblTimeData.HourTypeCode, tblTimeData.WC4, tblTimeData.WC5, Yes AS Expr2, No AS Expr1
FROM tblTimeData INNER JOIN tblErrorTrap ON tblTimeData.HourTypeCode = tblErrorTrap.Priority1
WHERE (((tblTimeData.WC4)<>[tblErrorTrap]![Priority3]) AND ((tblTimeData.WC5)=[tblErrorTrap]![Priority2]));

qryAppendWC5Errors
INSERT INTO tblTimeError ( EmployeeNumber, HourTypeCode, WC4, WC5, WC5Error, WC4Error )
SELECT tblTimeData.EmployeeNumber, tblTimeData.HourTypeCode, tblTimeData.WC4, tblTimeData.WC5, Yes AS Expr2, No AS Expr1
FROM tblTimeData INNER JOIN tblErrorTrap ON tblTimeData.HourTypeCode = tblErrorTrap.Priority1
WHERE (((tblTimeData.WC4)=[tblErrorTrap]![Priority2]) AND ((tblTimeData.WC5)<>[tblErrorTrap]![Priority3]));

qryAppendWC4_5Errors
INSERT INTO tblTimeError ( EmployeeNumber, HourTypeCode, WC4, WC5, WC5Error, WC4Error )
SELECT tblTimeData.EmployeeNumber, tblTimeData.HourTypeCode, tblTimeData.WC4, tblTimeData.WC5, Yes AS Expr2, Yes AS Expr1
FROM tblTimeData INNER JOIN tblErrorTrap ON tblTimeData.HourTypeCode = tblErrorTrap.Priority1
WHERE (((tblTimeData.WC4)<>[tblErrorTrap]![Priority2] And (tblTimeData.WC4)<>[tblErrorTrap]![Priority3]) AND ((tblTimeData.WC5)<>[tblErrorTrap]![Priority2] And (tblTimeData.WC5)<>[tblErrorTrap]![Priority3]))
GROUP BY tblTimeData.EmployeeNumber, tblTimeData.HourTypeCode, tblTimeData.WC4, tblTimeData.WC5, Yes, Yes;

I hope that you can adapt some of this to your database and progress towards a satisfactory solution.

If you are unsure of how to use this SQL, please refer to FAQ181-3317 for further information.

Cheers

John