Help with a list of audits for all staff

Hi guys, so you may be aware I am working on a call auditing system for a call centre and I am trying to get a list to show of all staff, and how many audits they need. Now when staff have have audits in other months it is removing them from the list of the current month, which is rendering it completely non-functional.

Here is my SQL code although I did not write this, i used the Query Builder.

SELECT [Agent Data].[Agent Name], [Agent Data].AprReason AS Comments, IIf(IsNull([AprAdjusted]),IIf([End Date]<43922,0,IIf([Start Date]>43739,DFirst("[AprTier1]","[TierTable]"),IIf([Auditfail]>0,DFirst("[AprTier1]","[TierTable]"),DFirst("[AprTier2]","[TierTable]")))),[AprAdjusted]) AS [Required audits], Sum([Peer Audit Database].NonReqCount) AS Completed, Max([Peer Audit Database].[Audit Date]) AS [Last audited], Max([Peer Audit Database].[Feedback date]) AS [Last feedback], [Agent Data].[Start Date] FROM [Agent Data] LEFT JOIN [Peer Audit Database] ON [Agent Data].[Agent Name] = [Peer Audit Database].[Agent Name] WHERE ((([Agent Data].[End Date])>=43922 Or ([Agent Data].[End Date]) Is Null) AND (([Peer Audit Database].[Case Month])=4 Or ([Peer Audit Database].[Case Month]) Is Null)) GROUP BY [Agent Data].[Agent Name], [Agent Data].AprReason, IIf(IsNull([AprAdjusted]),IIf([End Date]<43922,0,IIf([Start Date]>43739,DFirst("[AprTier1]","[TierTable]"),IIf([Auditfail]>0,DFirst("[AprTier1]","[TierTable]"),DFirst("[AprTier2]","[TierTable]")))),[AprAdjusted]), [Agent Data].[Start Date] HAVING ((([Agent Data].[Start Date])<=43951)); 

Now I do know what the problem is; where it filters by Case Month to either be 4 (for April) or null, any audits for other months mean that those staff members do not meet that criteria of ‘4 or null’

I just don’t know how to solve this problem? I need a list for every month with all staff members and their audit requirement which is worked out with this expression:

IIf(IsNull([AprAdjusted]),IIf([End Date]<43922,0,IIf([Start Date]>43739,DFirst("[AprTier1]","[TierTable]"),IIf([Auditfail]>0,DFirst("[AprTier1]","[TierTable]"),DFirst("[AprTier2]","[TierTable]")))),[AprAdjusted]) 

Then I have one query for each month. Please, any help you can give me is so appreciated.

submitted by /u/amazingoomoo
[link] [comments]

Posted on