You're welcome. I would definitely look into your table structure regarding any potential relationships with the codes. That might help to get rid of many of the IIF() functions and simplify the query considerably.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Problem with database
- Thread starter Unicon
- Start date
In addition to that I have attached db again as there is a problem with criteria which you have provided to me. Just have a look on Query3 where ID no. 100338 worked for 26 days in DXB and 5 days Training in AD but the criteria is showing 26 NormalDays for both where it has to be 26 Days Normal (N) and 5 Days (T) Training.
Please advice.
Please advice.
Attachments
The reason it is not calculating correctly is because the on duty field is showing as null. You cannot conduct a calculation correctly on a null field. I thought I had taken care of that in a previous version of the database by forcing a zero for any null fields in your cross tab query (CrosstabMonthlyAttenance (Total)). Also by forcing a zero, you will no longer need the NZ() functions you have when you calculated totals. I have made the necessary changes in the attached DB.
Regarding your table structure, I do not know what relationships you have since I do not know the details of your business process, you have to determine that. But from what I see there appears to be a relationship between the values in tblEmirates and the way you calculate emirate overtime. I see the value 770 in your query, this should be in a table if it is associated with the emirate code. There also appears to be a relationship between the emirate code and performance bonus as well as the emirate and the food allowance. If you could explain those relationships, then we can figure out how to handle them in the table structure.
Regarding your table structure, I do not know what relationships you have since I do not know the details of your business process, you have to determine that. But from what I see there appears to be a relationship between the values in tblEmirates and the way you calculate emirate overtime. I see the value 770 in your query, this should be in a table if it is associated with the emirate code. There also appears to be a relationship between the emirate code and performance bonus as well as the emirate and the food allowance. If you could explain those relationships, then we can figure out how to handle them in the table structure.
Attachments
You clearly have a relationship between the emirate and salary type, the emirate and rate and the emirate and hours. I have created the applicable tables and updated your relationships in the relationship window and populated the tables with the data from your spreadsheet. The revised database is attached.
Attachments
Thanks a lot Jzwp,
I have updated salary part (QrySalaryCalculation) with the new tables but unable to update Rates and hours into my query. Honestly speaking I don't know how to get it in query. Please advise me regarding the same so that I can start update my database.
Please advice.
I have updated salary part (QrySalaryCalculation) with the new tables but unable to update Rates and hours into my query. Honestly speaking I don't know how to get it in query. Please advise me regarding the same so that I can start update my database.
Please advice.
Attachments
I did come across an additional question. In the rates table of the spreadsheet you attached earlier, you show BasicSalary/30/9 or BasicSalary/26/9. Where do the 30, 26 and 9 come from?
As to using the new tables, you would use a subquery to pull in the information, but before I can do that I have to understand why you have entitled basic salary, Performance Bonus, NormalOT, food allowance and special allowance fields in your tblEmployeeDetails. Is this salary related information determined by what site the person works at which in turn is determined by the emirate to which the site is tied?
As to using the new tables, you would use a subquery to pull in the information, but before I can do that I have to understand why you have entitled basic salary, Performance Bonus, NormalOT, food allowance and special allowance fields in your tblEmployeeDetails. Is this salary related information determined by what site the person works at which in turn is determined by the emirate to which the site is tied?
Hi jzwp,
For DXB staffs and the staffs who are earning BasicSalary more than 2000, they need to work 9 hours for 26 days and for other Emirates they need to work 9 hours for 30 days. That is the entire concept.
Yes it’s determined by what Emirates the person works but the challenge is some of the staffs are having BasicSalary more than 2000 and NormalOT more than others. In this case what is the better way we can do it?
Please advice.
For DXB staffs and the staffs who are earning BasicSalary more than 2000, they need to work 9 hours for 26 days and for other Emirates they need to work 9 hours for 30 days. That is the entire concept.
Yes it’s determined by what Emirates the person works but the challenge is some of the staffs are having BasicSalary more than 2000 and NormalOT more than others. In this case what is the better way we can do it?
Please advice.
In addition to that as per the contract which we are providing them is DXB salary total 1000, and the moment they go to other emirates they will get 2000 concept salary and once they come back again to DXB than they are eligible for 1000 salary. And one more thing I need to show salary breakdown in payslip too which you can see in my RptPayslip (EntitledSalary column).
For DXB staffs and the staffs who are earning BasicSalary more than 2000, they need to work 9 hours for 26 days and for other Emirates they need to work 9 hours for 30 days. That is the entire concept.
Yes it’s determined by what Emirates the person works but the challenge is some of the staffs are having BasicSalary more than 2000 and NormalOT more than others. In this case what is the better way we can do it?
This is confusing, can we separate the DXB from others?
The DXB basic salary is 570 according to your spreadsheet. So to get this they must work how many hours a day for how many days? It looks like 9 hours a day for 26 days. Is that correct?
If a DXB staff is making more than basic salary (more than 570) they must work how many hours a day for how many days?
For staff members who do not work at DXB and who have a basic salary of 2000, they must work 9 hours a day for 30 days, correct?
For staff member who do not work at DXB and their basic salary is higher than 2000, how many hours a day and how many days must they work?
What do you call the salary if someone makes more than Basic salary?
If a person makes more than the basic salary, we will need to document that and I assume that the salary can change over time, so we will need to keep a history of the salary changes.
What happens if the basic salary or rates change? How are you going to handle that?
Regards to normalOT, is normalOT based on the salary * factor? So if someone is earning basic salary the normalOT is determined by basic salary * factor
If someone is making more than basic salary, the normalOT is determined by thier current salary * factor. I understand the factor changes. So for DXB staff the factor is 1.25. I assume that those earning basic salary and not at DXB, the factor is 2. I further assume that those earning more than basic salary and do not work at DXB, their rate is 1.25, correct?
Hi jzwp,
Please see my comments below,
This is confusing, can we separate the DXB from others?
Yes we can separate.
The DXB basic salary is 570 according to your spreadsheet. So to get this they must work how many hours a day for how many days? It looks like 9 hours a day for 26 days. Is that correct?
Correct.
If a DXB staff is making more than basic salary (more than 570) they must work how many hours a day for how many days?
They will never make more than basic salary. If they work for 26 days than they will make exactly 570. More we can so as Overtime. They need to work 9 hours a day to make overtime.
For staff members who do not work at DXB and who have a basic salary of 2000, they must work 9 hours a day for 30 days, correct?
Correct.
For staff member who do not work at DXB and their basic salary is higher than 2000, how many hours a day and how many days must they work?
They must need to work 9 hours for 26 days to earn their exact basic salary. More than that consider as overtime which will calculate as *2.
What do you call the salary if someone makes more than Basic salary?
OverTime
If a person makes more than the basic salary, we will need to document that and I assume that the salary can change over time, so we will need to keep a history of the salary changes.
Yes correct.
What happens if the basic salary or rates change? How are you going to handle that?
Rate will never change but still we can keep a record somewhere. Basic salary will change that is also we can keep a history but don’t know how to keep that record.
Regards to normalOT, is normalOT based on the salary * factor? So if someone is earning basic salary the normalOT is determined by basic salary * factor
Normal OT is determined with NormalDays work. IF DXB than *3 else *2.
If someone is making more than basic salary, the normalOT is determined by thier current salary * factor. I understand the factor changes. So for DXB staff the factor is 1.25. I assume that those earning basic salary and not at DXB, the factor is 2. I further assume that those earning more than basic salary and do not work at DXB, their rate is 1.25, correct?
Correct.
Please see my comments below,
This is confusing, can we separate the DXB from others?
Yes we can separate.
The DXB basic salary is 570 according to your spreadsheet. So to get this they must work how many hours a day for how many days? It looks like 9 hours a day for 26 days. Is that correct?
Correct.
If a DXB staff is making more than basic salary (more than 570) they must work how many hours a day for how many days?
They will never make more than basic salary. If they work for 26 days than they will make exactly 570. More we can so as Overtime. They need to work 9 hours a day to make overtime.
For staff members who do not work at DXB and who have a basic salary of 2000, they must work 9 hours a day for 30 days, correct?
Correct.
For staff member who do not work at DXB and their basic salary is higher than 2000, how many hours a day and how many days must they work?
They must need to work 9 hours for 26 days to earn their exact basic salary. More than that consider as overtime which will calculate as *2.
What do you call the salary if someone makes more than Basic salary?
OverTime
If a person makes more than the basic salary, we will need to document that and I assume that the salary can change over time, so we will need to keep a history of the salary changes.
Yes correct.
What happens if the basic salary or rates change? How are you going to handle that?
Rate will never change but still we can keep a record somewhere. Basic salary will change that is also we can keep a history but don’t know how to keep that record.
Regards to normalOT, is normalOT based on the salary * factor? So if someone is earning basic salary the normalOT is determined by basic salary * factor
Normal OT is determined with NormalDays work. IF DXB than *3 else *2.
If someone is making more than basic salary, the normalOT is determined by thier current salary * factor. I understand the factor changes. So for DXB staff the factor is 1.25. I assume that those earning basic salary and not at DXB, the factor is 2. I further assume that those earning more than basic salary and do not work at DXB, their rate is 1.25, correct?
Correct.
My apologies, I had misinterpreted something you wrote in a previous post, but what you have provided does make things clearer for me.
Regarding the history of salary changes, we would need another table since now the combination of emirate and salary type can have many related salaries (over time). So I have created tblEmirateSalaryHistory and moved the salary amounts from tblEmirateSalary to this new table. I have also included an effective date field which tells when that salary goes into effect.
Now to get the most current salaries for each emirate/salary type we need a query to get the most recent effective date for each emirate/salary combination. For that I created qryMostCurrentSalaryDate. We then need to join that query back to tblEmirateSalaryHistory to get the actually current salary. I used qryCurrentSalary for that.
Since this is a lot to consider I will stop there. The revised DB is attached.
Just to make sure, the rates and hours associated with each emirate will not change over time, correct?
Regarding the history of salary changes, we would need another table since now the combination of emirate and salary type can have many related salaries (over time). So I have created tblEmirateSalaryHistory and moved the salary amounts from tblEmirateSalary to this new table. I have also included an effective date field which tells when that salary goes into effect.
Now to get the most current salaries for each emirate/salary type we need a query to get the most recent effective date for each emirate/salary combination. For that I created qryMostCurrentSalaryDate. We then need to join that query back to tblEmirateSalaryHistory to get the actually current salary. I used qryCurrentSalary for that.
Since this is a lot to consider I will stop there. The revised DB is attached.
Just to make sure, the rates and hours associated with each emirate will not change over time, correct?
Attachments
Similar threads
- Replies
- 9
- Views
- 542
- Replies
- 64
- Views
- 2,949
- Replies
- 43
- Views
- 1,779
Users who are viewing this thread
Total: 1 (members: 0, guests: 1)