If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Payroll
Dear Friends
I am planning to design a database for maintaing payroll information. At present i am using database where in I am unable to maintain historic data like details of previous months. I have payheadmaster and employee master table. Reports and payslips are generated through querries from master table itself. data changes will be made in master table itself Can anyone guide me how to create a database where in I can maintain/store a data for elapsed previous months also. Report for any given period. There will not be major changes in the payhead and employee data month after month , changes may be to the extent of 2% So I think it may not be good design if a store salary releated information for everymonth and every employee. Is there any other way where I can list only the changes in pay head data thanks Ramesh |
#2
|
|||
|
|||
Payroll
A core question is: what level of detail is going into the DB? For example:
- punch-in and punch-out times from time clocks. (e.g. Access is doing the hours calculations) - total hours, holidays, sick days etc. - just pay for the pay period. Also could yu clarify what your payheadmaster table is? "SHETTY" wrote: Dear Friends I am planning to design a database for maintaing payroll information. At present i am using database where in I am unable to maintain historic data like details of previous months. I have payheadmaster and employee master table. Reports and payslips are generated through querries from master table itself. data changes will be made in master table itself Can anyone guide me how to create a database where in I can maintain/store a data for elapsed previous months also. Report for any given period. There will not be major changes in the payhead and employee data month after month , changes may be to the extent of 2% So I think it may not be good design if a store salary releated information for everymonth and every employee. Is there any other way where I can list only the changes in pay head data thanks Ramesh |
#3
|
|||
|
|||
Payroll
Payment is on monthly attendance basis,
full day or half day attendance absence can be marked Casula leave -6, Sick Leave-6 & Paid Leave -30 will be provided leave taken in exess of above will be dedcuted from salary (loss of pay) Leave under each of above head will be credited to respective A/c only at the end of calender year ie 31st December. Employee can carry forward PL up to 180 days , Sick leave up to 15 days, Casual leave cannot be caarried forward. Payheadmaster table stores pay details as per appoitnment order Structure ID (PK), EmployeeCode (FK), Basic, Da, & other pay heads including profession tax and other deductions at present one LOP (loss of pay days) field is provided in above table and no of days leave to be deducted updated in that field and pay head under each head is worked. There will be defferent profession tax applicable for defferecent states. Profession tax slab is pre defined and can be called for each branch or employee wise also There will be 3-4 grades in basic salary and modification in salary master will happen April month of every year Apart from monthly salary following will be paid occationally Paid leave encashment,Leave Travel allowance, Medical expenses reimburshment(non taxable), statutory bonus, performacne incentives, all of above must be added total yearly salary . Our financila year is April to March Hope I have provided all the required information Please guide me Ramesh "Fred" wrote in message ... A core question is: what level of detail is going into the DB? For example: - punch-in and punch-out times from time clocks. (e.g. Access is doing the hours calculations) - total hours, holidays, sick days etc. - just pay for the pay period. Also could yu clarify what your payheadmaster table is? "SHETTY" wrote: Dear Friends I am planning to design a database for maintaing payroll information. At present i am using database where in I am unable to maintain historic data like details of previous months. I have payheadmaster and employee master table. Reports and payslips are generated through querries from master table itself. data changes will be made in master table itself Can anyone guide me how to create a database where in I can maintain/store a data for elapsed previous months also. Report for any given period. There will not be major changes in the payhead and employee data month after month , changes may be to the extent of 2% So I think it may not be good design if a store salary releated information for everymonth and every employee. Is there any other way where I can list only the changes in pay head data thanks Ramesh |
#4
|
|||
|
|||
Payroll
There are several challenges here, both on learning the key info and that
this will be a project rather than just one post/question. But here's and idea on structure. It sounds like payheadmaster has one record per employee or may actually be an employee table. I'll call that the "Employee" table. It souds like your pay and pay records are a once-per month summary. So, one "Employee" table which has all of the "once per employee" information. Plus a PrimaryKey "EmployeeNumber" Next a "Pay" table which has a record for each instance of a monthly entry for an employee. It will have an "EmployeeNumber" field (NOT a PK), and link it to the Employee table (EmployeeNumber to EmployeeNumber). It will have a PayDate field It will have fields for all of the data that changes pay-period to pay-period. Any calculations where the "equation" never changes will run at the instant that you are providing the data (via a report etc.). Just a start, but hopefully on a foundation of a good underlying structure. "SHETTY" wrote: Payment is on monthly attendance basis, full day or half day attendance absence can be marked Casula leave -6, Sick Leave-6 & Paid Leave -30 will be provided leave taken in exess of above will be dedcuted from salary (loss of pay) Leave under each of above head will be credited to respective A/c only at the end of calender year ie 31st December. Employee can carry forward PL up to 180 days , Sick leave up to 15 days, Casual leave cannot be caarried forward. Payheadmaster table stores pay details as per appoitnment order Structure ID (PK), EmployeeCode (FK), Basic, Da, & other pay heads including profession tax and other deductions at present one LOP (loss of pay days) field is provided in above table and no of days leave to be deducted updated in that field and pay head under each head is worked. There will be defferent profession tax applicable for defferecent states. Profession tax slab is pre defined and can be called for each branch or employee wise also There will be 3-4 grades in basic salary and modification in salary master will happen April month of every year Apart from monthly salary following will be paid occationally Paid leave encashment,Leave Travel allowance, Medical expenses reimburshment(non taxable), statutory bonus, performacne incentives, all of above must be added total yearly salary . Our financila year is April to March Hope I have provided all the required information Please guide me Ramesh "Fred" wrote in message ... A core question is: what level of detail is going into the DB? For example: - punch-in and punch-out times from time clocks. (e.g. Access is doing the hours calculations) - total hours, holidays, sick days etc. - just pay for the pay period. Also could yu clarify what your payheadmaster table is? "SHETTY" wrote: Dear Friends I am planning to design a database for maintaing payroll information. At present i am using database where in I am unable to maintain historic data like details of previous months. I have payheadmaster and employee master table. Reports and payslips are generated through querries from master table itself. data changes will be made in master table itself Can anyone guide me how to create a database where in I can maintain/store a data for elapsed previous months also. Report for any given period. There will not be major changes in the payhead and employee data month after month , changes may be to the extent of 2% So I think it may not be good design if a store salary releated information for everymonth and every employee. Is there any other way where I can list only the changes in pay head data thanks Ramesh |
#5
|
|||
|
|||
Payroll
Thank you sir,
I want to know how I can add data to pay table month after month automatically, may be I have to run a append querry The update to pay table will be based on the attendance leave status etc. I have written a querry which work out pay for current/running month. How I can append this data to pay table with the pay date ? Actaullay I have created database which works fine for a single month, reports- generating pay slips all are working fine. But I dont know how to work on date values (say for a particular period) and updating paytable automatically as per current month salary Please guide Ramesh Shetty "Fred" wrote in message ... There are several challenges here, both on learning the key info and that this will be a project rather than just one post/question. But here's and idea on structure. It sounds like payheadmaster has one record per employee or may actually be an employee table. I'll call that the "Employee" table. It souds like your pay and pay records are a once-per month summary. So, one "Employee" table which has all of the "once per employee" information. Plus a PrimaryKey "EmployeeNumber" Next a "Pay" table which has a record for each instance of a monthly entry for an employee. It will have an "EmployeeNumber" field (NOT a PK), and link it to the Employee table (EmployeeNumber to EmployeeNumber). It will have a PayDate field It will have fields for all of the data that changes pay-period to pay-period. Any calculations where the "equation" never changes will run at the instant that you are providing the data (via a report etc.). Just a start, but hopefully on a foundation of a good underlying structure. "SHETTY" wrote: Payment is on monthly attendance basis, full day or half day attendance absence can be marked Casula leave -6, Sick Leave-6 & Paid Leave -30 will be provided leave taken in exess of above will be dedcuted from salary (loss of pay) Leave under each of above head will be credited to respective A/c only at the end of calender year ie 31st December. Employee can carry forward PL up to 180 days , Sick leave up to 15 days, Casual leave cannot be caarried forward. Payheadmaster table stores pay details as per appoitnment order Structure ID (PK), EmployeeCode (FK), Basic, Da, & other pay heads including profession tax and other deductions at present one LOP (loss of pay days) field is provided in above table and no of days leave to be deducted updated in that field and pay head under each head is worked. There will be defferent profession tax applicable for defferecent states. Profession tax slab is pre defined and can be called for each branch or employee wise also There will be 3-4 grades in basic salary and modification in salary master will happen April month of every year Apart from monthly salary following will be paid occationally Paid leave encashment,Leave Travel allowance, Medical expenses reimburshment(non taxable), statutory bonus, performacne incentives, all of above must be added total yearly salary . Our financila year is April to March Hope I have provided all the required information Please guide me Ramesh "Fred" wrote in message ... A core question is: what level of detail is going into the DB? For example: - punch-in and punch-out times from time clocks. (e.g. Access is doing the hours calculations) - total hours, holidays, sick days etc. - just pay for the pay period. Also could yu clarify what your payheadmaster table is? "SHETTY" wrote: Dear Friends I am planning to design a database for maintaing payroll information. At present i am using database where in I am unable to maintain historic data like details of previous months. I have payheadmaster and employee master table. Reports and payslips are generated through querries from master table itself. data changes will be made in master table itself Can anyone guide me how to create a database where in I can maintain/store a data for elapsed previous months also. Report for any given period. There will not be major changes in the payhead and employee data month after month , changes may be to the extent of 2% So I think it may not be good design if a store salary releated information for everymonth and every employee. Is there any other way where I can list only the changes in pay head data thanks Ramesh |
#6
|
|||
|
|||
Payroll
Dear Shetty,
In my opinion, you need to start with a description of the fundamental organization and aspects of your real world process, and of what you want the database to accomplish. A way to force yourself to do this properly would be to do it without using any database implementation terminology. I believe that you have not given that in your posts. Instead, you've given certain secondary details about your process plus various database implementation details which are not relevant at this stage. Other than to write this paragraph which hopefully might be useful I'm unable to help. "SHETTY" wrote: Thank you sir, I want to know how I can add data to pay table month after month automatically, may be I have to run a append querry The update to pay table will be based on the attendance leave status etc. I have written a querry which work out pay for current/running month. How I can append this data to pay table with the pay date ? Actaullay I have created database which works fine for a single month, reports- generating pay slips all are working fine. But I dont know how to work on date values (say for a particular period) and updating paytable automatically as per current month salary Please guide Ramesh Shetty "Fred" wrote in message ... There are several challenges here, both on learning the key info and that this will be a project rather than just one post/question. But here's and idea on structure. It sounds like payheadmaster has one record per employee or may actually be an employee table. I'll call that the "Employee" table. It souds like your pay and pay records are a once-per month summary. So, one "Employee" table which has all of the "once per employee" information. Plus a PrimaryKey "EmployeeNumber" Next a "Pay" table which has a record for each instance of a monthly entry for an employee. It will have an "EmployeeNumber" field (NOT a PK), and link it to the Employee table (EmployeeNumber to EmployeeNumber). It will have a PayDate field It will have fields for all of the data that changes pay-period to pay-period. Any calculations where the "equation" never changes will run at the instant that you are providing the data (via a report etc.). Just a start, but hopefully on a foundation of a good underlying structure. "SHETTY" wrote: Payment is on monthly attendance basis, full day or half day attendance absence can be marked Casula leave -6, Sick Leave-6 & Paid Leave -30 will be provided leave taken in exess of above will be dedcuted from salary (loss of pay) Leave under each of above head will be credited to respective A/c only at the end of calender year ie 31st December. Employee can carry forward PL up to 180 days , Sick leave up to 15 days, Casual leave cannot be caarried forward. Payheadmaster table stores pay details as per appoitnment order Structure ID (PK), EmployeeCode (FK), Basic, Da, & other pay heads including profession tax and other deductions at present one LOP (loss of pay days) field is provided in above table and no of days leave to be deducted updated in that field and pay head under each head is worked. There will be defferent profession tax applicable for defferecent states. Profession tax slab is pre defined and can be called for each branch or employee wise also There will be 3-4 grades in basic salary and modification in salary master will happen April month of every year Apart from monthly salary following will be paid occationally Paid leave encashment,Leave Travel allowance, Medical expenses reimburshment(non taxable), statutory bonus, performacne incentives, all of above must be added total yearly salary . Our financila year is April to March Hope I have provided all the required information Please guide me Ramesh "Fred" wrote in message ... A core question is: what level of detail is going into the DB? For example: - punch-in and punch-out times from time clocks. (e.g. Access is doing the hours calculations) - total hours, holidays, sick days etc. - just pay for the pay period. Also could yu clarify what your payheadmaster table is? "SHETTY" wrote: Dear Friends I am planning to design a database for maintaing payroll information. At present i am using database where in I am unable to maintain historic data like details of previous months. I have payheadmaster and employee master table. Reports and payslips are generated through querries from master table itself. data changes will be made in master table itself Can anyone guide me how to create a database where in I can maintain/store a data for elapsed previous months also. Report for any given period. There will not be major changes in the payhead and employee data month after month , changes may be to the extent of 2% So I think it may not be good design if a store salary releated information for everymonth and every employee. Is there any other way where I can list only the changes in pay head data thanks Ramesh |
Thread Tools | |
Display Modes | |
|
|