Problem with database

[Unicon]

Dear all
I am a learner with access database. Please find attached database. Things are working fine and I am still developing it but in between I got a problems as below.
•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 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 saying that concern employee earned = Amount , Deduction = Amount & Total payable = amount.
•I have created a report RptEmployeeReport which I tried to open but its saying that “System resource exceeded”. No idea what is that.

Please advise.

 

[jzwp22]

•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.

 

[Unicon]

Thanks a lot jzwp22, I want to let you know that sometimes staff will work for 26 days only where sometimes month will come with 1 Public Holiday (PH) and in this case some staff will work like 25 days as N and 1 day as PH and in this case we need to pay them 26 days as N and 1 day as PH.

Please advise.

 

[Unicon]

And one more question I have pasted those code in my query but it's showing that 500 advance for all employees in query which supposed to show for ID no. 100001 only.

 

[jzwp22]

Thanks a lot jzwp22, I want to let you know that sometimes staff will work for 26 days only where sometimes month will come with 1 Public Holiday (PH) and in this case some staff will work like 25 days as N and 1 day as PH and in this case we need to pay them 26 days as N and 1 day as PH.

Wouldn't it be just a matter of entering another record for the month? Your query is doing the calculation correctly as far as I can tell. Perhaps I do not understand what you are saying. Are employees entitled to be paid for public holidays as well?

And one more question I have pasted those code in my query but it's showing that 500 advance for all employees in query which supposed to show for ID no. 100001 only.

Did you include the criteria for the employee as well?

(SELECT AmountAdvanced from tblAdvance WHERE tblAdvance.EmpID=empID and monthname(month(advancedate)) & " " & year(advancedate)=[month]) as AmountAdvancedtoEmp

 

[Unicon]

Thanks again jzwp22, I have included same criteria but still it is not working. Yes employees are entitled to be paid for public holidays as well and sometimes they worked for 26 days as N and 1 as PH where my criteria will calculate it easily but some cases some employee will work only 26 days (25 Days as N and 1 as PH). This is the only thing which is not working out for me. Staff supposed to work for 26 days to earn 1000. In this case if staff work for (25 days as N and 1 as PH) total will be 26 days but for that PH we need to pay another 1 OT means (26 + 1 OT) which is not happening here in my calculation.
Please advice.

 

[jzwp22]

In the attached database, I added another employee's data for February with 25 normal days and 1 public holiday. If I look at qrySalaryCalculation what field is not being calculated correctly?

Also, if you note in that same query, the pay advanced to the employee showns only for the 1 employee so I'm not sure what to tell you on that other than to make sure your subquery is correct.

 

[Unicon]

Thanks jzwp22,
I understand that all the field is being calculated correctly but all those calculation is calculated based on 26 days criteria in that query if someone work for 26 days he is eligible to get 1000 and if more than it will be consider as Overtime but the problem is when some one work for 25 days as N and 1 as PH than that calculation will not work it will come less than 1000 but practically if we can see he worked on that PH day also which means he worked 26 days I mean (25+1) and as per our salary rules we need to pay him 25+1 = 26 days and he worked on PH day means he is eligible for 1 Overtime.

I have attached db again where I put one more record with month of January 2012. You can see there that 500 advance is showing for EmpID 100335 too which is supposed to be for EmpID 100001 only.

Waiting for your favourable feedback.

 

[jzwp22]

To correct the advanced amount, I had just empID in the subquery. The empID must have various sources, so I had to specify the table/query name

(SELECT AmountAdvanced from tblAdvance WHERE tblAdvance.EmpID= [CrosstabMonthlyAttenance (Total)].EmpID and monthname(month(advancedate)) & " " & year(advancedate)=[month]) AS AmountAdvancedtoEmp

As to your calculation issue, it sounds like you have to check to see if PH=0 when N<26. If N<26 and PH<>0 and then N=PH+N and add 1 to OT. If PH=0 and N<26 then N=N and no OT.

IIF(N<26 AND PH<>0,N+PH,N) as AdjustedN

You will also have to have an expression to calculated the AdjustedOT

I'm not sure what your rules are if PH>1 in a month, is N allowed to be 24 and 2 OT? You know more about that.

 

[Unicon]

Thanks jzwp22 for all your support, hats off to you guys who are always helping people like me. I will just go through your advise. I will again disturb you If I hit a snag in between.
Be ready for some more question .

Once again thanks a lot.

 

[jzwp22]

You're welcome!

 

[Unicon]

Hi jzwp22,
I have tried to calculate NormalDays and OverTime in my payroll in many ways but wasn't work for me. I just want to calculate in a way as attached. Please have a look on it and advice.
I just want to caculate NormalDays and OverTime like that way (Employee need to work 26 days to earn normal salary). And another case Whenever month falls with 31 days than employee need to work 27 days to get normal salary means 27 days will be consider as 26 days and rest days will calculate as OT. If Employee work for 25 Days as OnDuty and 1 day as PublicHoliday than it will consider as NormlDay=26, PublicHoliday=1.
Please advice.

 

[Unicon]

Hi jzwp22,

Any updates? Please advice.

 

[jzwp22]

I was doing other things over the weekend, so I did not have much time to visit the forum.

It looks like the normal days for months with 29, 30 or 31 days is 26 but for a month with 28 days the normal days is 24. Is that correct?

I am still not clear on how overtime days are determined. Do you have an actual formula?

If you do not have a formula, I was wondering if you could fill in the attached spreadsheet with the appropriate OT days for the new scenarios I have included.

Will you have more than 2 public holidays in a month?

Are the dates of the public holidays always known? Are they dependent on anything?

 

[Unicon]

Thanks agains jzwp22,

Whenever month’s falls with 31 days staff need to work for 27 days to get full basic salary means if someone works for 27 days it will consider as 26 days (1 day will be deduct).
You are correct Normal days for months with 29, 30 days is 26 and 28 days is 24. But for months with 31 days is 27 (If someone works 27 days for months with 31 days than we need to show 26 days only).
Overtime for months with 29, 30 days if someone works more than 26 days it will be consider as overtime If 29 than 3 overtime, If 30 than 4 overtime. For months with 28 days if someone works 25 days than 1 OT, IF 27 days than 2 OT.
Public holiday will not come frequently and dates of public holiday are not known always, and we don’t have more than 2 Public holiday.
Code somehow like this (If”daysinmonth”=30 or 29 and Onduty >=26 than 26) (If daysinmonth>30 and onduty>=27 than 27 else totalnumbers of onduty) (If daysinmonth =28 and onduty>=24 than 24 else totalnumbers of onduty) (If daysinmonth = 31 or 30 or 29 and Onduty = 25 than Onduty + 1 PublicHoliday(If any public holiday is there) else totalnumbers of on duty.
I have attached the spreadsheet with my comments.

 

[Unicon]

Hi jzwp22,

In addition to that Please find attached DB. I need somehow like this. But still there are some problems, you can see nothing is showing for last 4 records.
Please advice.

 

[jzwp22]

In order to do this more efficiently, I added a new table to the attached database (tblNomalDays). That table has the normal days for each of the days in a month (28,29,30,31) along with the deduction you take for months with 31 days. These values are then pulled into the query and used in the various calculations. See Query3 for the calculations of NormalDays and Overtime. Query3 is just a simplified version of your qrySalaryCalculation

 

[Unicon]

Hi jzwp22,

A bundle of thanks to you. I have just copied and paste the information but while opening query with new criteria a message popup "Too many fields defined"

Please advice. I have attached same DB for your verification. Please see query3 I have copied and pasted the information.

 

[jzwp22]

I cannot see that your query has reached any specification limits of Access 2007 (see specifications here). I don't know if it has to do with all of the nested IIF() functions which is concerning. For example, if a rate is associated with each item in tblEmirate, why not add a field that includes the rate and then just lookup the appropriate rate based on the emirate value. This would be similar to what I did for looking up the normal days. Also, it looks like you have different rates based on the code. If there is a relationship here, then you need to redesign your table structure to capture it rather than relying on a bunch of IIF() functions

So if the emirates and codes are related:

tblCodeEmirates
-pkCodeEmiID primary key, autonumber
-CodeID foreign key to tblCode
-EmirateID foreign key to tblEmirates
-ratefield

Additionally, I would probably breakdown the 1 query into smaller, more simple queries that each do specific tasks and then join them at the very end.

For example, you can have 1 query that deals with adding up the attendance date information and you would just have the employeeID which you can then use to join to other queries that provide the other information later on.

 

[Unicon]

Thanks a lot jzwp22,
I will just try to break the query into smaller and will join again at the end. Will let you know how it goes.
Once again thanks a lot for all your support.