•If someone work 25 Days as code N means normal days and 1 day as PH means public holiday and it supposed to be a NormalDays =26 and PublicHoliday =1 as per my calculation but it’s showing NormalDays =25 (Please see the QrySalaryCalculation, 1st record of IDNo 100001 for February month)
I did not see a problem here. In tblDailyAttendance you have 26 total records for the employee with February dates. 25 of the 26 are identified with an N while 1 is identified with a PH, so normal days has to equal 25. Did you miss entering a record?
•I have an advance table separately which I need to deduct as per concern employee advance record. I just want to deduct those advances for concern employee from their monthly payroll (earning) as monthly basis depends upon the advance they took. HOW CAN I DO THAT? I want a report every month
You can use either a subquery in your salary calculation query or a DLookup(). The following is your salary query with the subquery shown in red.
SELECT [CrosstabMonthlyAttenance (Total)].EmpID, [CrosstabMonthlyAttenance (Total)].IDNumber, [CrosstabMonthlyAttenance (Total)].FirstName, [CrosstabMonthlyAttenance (Total)].Month, Day(DateSerial(Year([Month]),Month([Month])+1,0)) AS DaysInMonth, [CrosstabMonthlyAttenance (Total)].Emirate, [CrosstabMonthlyAttenance (Total)].EntitledBasic, [CrosstabMonthlyAttenance (Total)].PerformanceBonus, [CrosstabMonthlyAttenance (Total)].NormalOT, (IIf([Emirate]="AD",IIf([NormalOT]>770,[NormalOT],770),IIf([Emirate]="SHJ",770,IIf([Emirate]="FUJ",770,IIf([Emirate]="RAK",770,IIf([Emirate]="AJ",770,IIf([Emirate]="UAQ",770,[NormalOT]))))))) AS EmiratesNormalOT, [CrosstabMonthlyAttenance (Total)].FoodAllowance, [CrosstabMonthlyAttenance (Total)].SpecialAllowance, IIf([Emirate]="DXB",[EntitledBasic],2000) AS EmiratesBasic, IIf([Emirate]="AD",0,IIf([Emirate]="SHJ",0,IIf([Emirate]="AJ",0,IIf([Emirate]="FUJ",0,IIf([Emirate]="UAQ",0,[PerformanceBonus]))))) AS EmiratePerformanceBonus, IIf([Emirate]="AD",0,IIf([Emirate]="SHJ",0,IIf([Emirate]="AJ",0,IIf([Emirate]="FUJ",0,IIf([Emirate]="UAQ",0,100))))) AS EmirateFoodAllowance, [CrosstabMonthlyAttenance (Total)].N AS OnDuty, IIf([DaysInMonth]<=30,IIf([OnDuty]>26,[OnDuty]-26),IIf([DaysInMonth]>30,IIf([OnDuty]>26,[OnDuty]-27,0))) AS OverTime, IIf([OnDuty]<=25,[OnDuty],IIf([DaysInMonth]=31,IIf([OnDuty]>=27,26),[OnDuty]-[OverTime])) AS NormalDays, [CrosstabMonthlyAttenance (Total)].PH AS PublicHoliday, [CrosstabMonthlyAttenance (Total)].R AS Rest, [CrosstabMonthlyAttenance (Total)].A AS Absent, [CrosstabMonthlyAttenance (Total)].S AS SickDays, [CrosstabMonthlyAttenance (Total)].T AS Training, [CrosstabMonthlyAttenance (Total)].V AS Vacation, [CrosstabMonthlyAttenance (Total)].I AS IdleDays, (Nz([NormalDays],0)+Nz([OverTime],0)+Nz([PublicHoliday],0)) AS TotalDaysWorked, (Nz([SickDays],0)+Nz([IdleDays],0)+Nz([Training],0)) AS NonDeployedDays, IIf([TotalDaysWorked]<26,IIf([TotalDaysWorked]+[NonDeployedDays]>=26,26-[TotalDaysWorked],[NonDeployedDays]),0) AS TotalNonDeployedDays, (Nz([NormalDays],0)+Nz([Training],0)+Nz([IdleDays],0))*9 AS NormalHours, (IIf([Emirate]="AD",[NormalDays]*2,IIf([Emirate]="SHJ",[NormalDays]*2,IIf([Emirate]="FUJ",[NormalDays]*2,IIf([Emirate]="RAK",[NormalDays]*2,IIf([Emirate]="AJ",[NormalDays]*2,IIf([Emirate]="UAQ",[NormalDays]*2,[NormalDays]*3))))))) AS NormalOTHours, (IIf([Emirate]="AD",[OverTime]*11,IIf([Emirate]="SHJ",[OverTime]*11,IIf([Emirate]="FUJ",[OverTime]*11,IIf([Emirate]="RAK",[OverTime]*11,IIf([Emirate]="AJ",[OverTime]*11,IIf([Emirate]="UAQ",[OverTime]*11,[OverTime]*12))))))) AS WeeklyOffOTHours, ([PublicHoliday]*12) AS SpecialOTPHHours, (Nz([NormalHours],0)+Nz([NormalOTHours],0)+Nz([WeeklyOffOTHours],0)+Nz([SpecialOTPHHours],0)) AS TotalHours, Round([EmiratesBasic]/234*[NormalHours]) AS BasicEarned, IIf([NormalDays]=26,[EmiratesNormalOT],Round([NormalOTHours]*[OTRates],0)) AS NormalOTEarned, (Round([WeeklyOffOTHours]*[SpecialOTRates],0)) AS WeeklyOffOTEarned, (Round([SpecialOTPHHours]*([PublicHolidayOTRates]-[NormalRates]),0)) AS PublicHolidayOTEarned, Round(IIf([OnDuty]>=26,[EmiratePerformanceBonus],[EmiratePerformanceBonus]/26*[OnDuty])) AS EarnedPerformanceBonus, Round(IIf([OnDuty]>=26,[EmirateFoodAllowance],[EmirateFoodAllowance]/26*[OnDuty])) AS EarnedFoodAllowance, Round(IIf([OnDuty]>=26,[SpecialAllowance],[SpecialAllowance]/26*[OnDuty])) AS EarnedSpecialAllowance, (Nz([BasicEarned],0)+Nz([NormalOTEarned],0)+Nz([PublicHolidayOTEarned],0)+Nz([EarnedSpecialAllowance],0)+Nz([EarnedFoodAllowance],0)+Nz([WeeklyOffOTEarned],0)+Nz([EarnedPerformanceBonus],0)) AS TotalEarned, Round(([EmiratesBasic]+[EmirateFoodAllowance])/26*[TotalNonDeployedDays]) AS NonDeployedDaysEarned, (IIf([Absent]>=1,92,0)) AS PerformanceBonusDeduction, [CrosstabMonthlyAttenance (Total)].Accepted AS 28Days, (IIf([Emirate]="DXB",IIf([OnDuty]>=28,IIf([28Days]="Yes",112,0)))) AS 28DaysAllowanceEarned, (IIf([TotalDaysWorked]>=28,IIf([28Days]=Yes,[TotalDaysWorked]-28,0))) AS OT28Days, [OT28Days]*44 AS 28DaysOTEarned, Nz([TotalEarned],0)+Nz([28DaysAllowanceEarned],0)-Nz([PerformanceBonusDeduction],0) AS NetEarned, IIf([SpecialAllowance]=482,(Nz([EmiratesBasic],0)+Nz([SpecialAllowance],0))/26/9,[EmiratesBasic]/26/9) AS DXBNormalRates, (IIf([Emirate]="AD",IIf([EmiratesBasic]>2300,[EmiratesBasic]/26/9,[EmiratesBasic]/30/9),IIf([Emirate]="SHJ",([EmiratesBasic]/30/9),IIf([Emirate]="FUJ",([EmiratesBasic]/30/9),IIf([Emirate]="RAK",([EmiratesBasic]/30/9),IIf([Emirate]="AJ",([EmiratesBasic]/30/9),IIf([Emirate]="UAQ",([EmiratesBasic]/30/9),[DXBNormalRates]))))))) AS NormalRates, (IIf([Emirate]="AD",[NormalRates]*2,IIf([Emirate]="SHJ",[NormalRates]*2,IIf([Emirate]="FUJ",[NormalRates]*2,IIf([Emirate]="RAK",[NormalRates]*2,IIf([Emirate]="AJ",[NormalRates]*2,IIf([Emirate]="UAQ",[NormalRates]*2,[DXBNormalRates]*1.25))))))) AS OTRates, (IIf([Emirate]="AD",[NormalRates]*2,IIf([Emirate]="SHJ",[NormalRates]*2,IIf([Emirate]="FUJ",[NormalRates]*2,IIf([Emirate]="RAK",[NormalRates]*2,IIf([Emirate]="AJ",[NormalRates]*2,IIf([Emirate]="UAQ",[NormalRates]*2,[DXBNormalRates]*2.5))))))) AS PublicHolidayOTRates, (IIf([Emirate]="AD",[NormalRates]*2,IIf([Emirate]="SHJ",[NormalRates]*2,IIf([Emirate]="FUJ",[NormalRates]*2,IIf([Emirate]="RAK",[NormalRates]*2,IIf([Emirate]="AJ",[NormalRates]*2,IIf([Emirate]="UAQ",[NormalRates]*2,[DXBNormalRates]*1.5))))))) AS SpecialOTRates, [CrosstabMonthlyAttenance (Total)].ModeOfPayment,
(SELECT AmountAdvanced from tblAdvance WHERE tblAdvance.EmpID=empID and monthname(month(advancedate)) & " " & year(advancedate)=[month]) as AmountAdvancedtoEmpFROM [CrosstabMonthlyAttenance (Total)]
GROUP BY [CrosstabMonthlyAttenance (Total)].EmpID, [CrosstabMonthlyAttenance (Total)].IDNumber, [CrosstabMonthlyAttenance (Total)].FirstName, [CrosstabMonthlyAttenance (Total)].Month, Day(DateSerial(Year([Month]),Month([Month])+1,0)), [CrosstabMonthlyAttenance (Total)].Emirate, [CrosstabMonthlyAttenance (Total)].EntitledBasic, [CrosstabMonthlyAttenance (Total)].PerformanceBonus, [CrosstabMonthlyAttenance (Total)].NormalOT, [CrosstabMonthlyAttenance (Total)].FoodAllowance, [CrosstabMonthlyAttenance (Total)].SpecialAllowance, [CrosstabMonthlyAttenance (Total)].N, [CrosstabMonthlyAttenance (Total)].PH, [CrosstabMonthlyAttenance (Total)].R, [CrosstabMonthlyAttenance (Total)].A, [CrosstabMonthlyAttenance (Total)].S, [CrosstabMonthlyAttenance (Total)].T, [CrosstabMonthlyAttenance (Total)].V, [CrosstabMonthlyAttenance (Total)].I, [CrosstabMonthlyAttenance (Total)].Accepted, [CrosstabMonthlyAttenance (Total)].ModeOfPayment;
Of course you may run into issues if the person did not earn enough money to cover the advance. In your query results, you show the employee has having 2 records (AD and DBX) returned for January (the advanced occurs in January) but neither is enough to cover the advance of 500. To only apply the advance to either AD or DBX then you would have to do that in relative to the advance itself.
•I have created a report RptEmployeeReport which I tried to open but its saying that “System resource exceeded”. No idea what is that.
I had no trouble opening the report. Perhaps your system is restrained by its memory.