Need help with append and delete duplicates
I have tables namely
1)emp, 2)time and 3)payroll
TABLE emp
ssn text [U]PK[/U]
name text
regular_rate number
ot_rate number
TABLE time
regular_hours number
ot_hours number
period_from date/time
period_to date/time
check_date date/time
deductions number
TABLE payroll
ssn text
name text
regular_rate number
ot_rate number
regular_hours number
ot_hours number
period_from date/time
period_to date/time
check_date date/time
regular_salary = regular_rate * regular_hours number
ot_salary=ot_ra te * ot_hours number
total_salary = regular_salary + ot_salary number
I want the user only to enter the hours and the payroll has to be calculated basing on the hours entered.
Option1: Either payroll table should be appended using append query after hours entered in time table but must restrict duplicate entries or delete duplicate entries after append.
Option2: Just not to have any payroll table at all and everything to be taken care in time table and create a form for hours entry that will restrict users from entering duplicate records. For example for ssn=123-45-6789 and period_from =Jan 01, 2008 and period_to=Jan 31, 2008. I want to restrict users from entering hours for this particular period(Jan 01, 2008 to Jan 31, 2008) if hours already exists for employee whose ssn=123-45-6789 and at the same time I want to allow database user to enter record for ssn 123-45-6789 for other periods(Feb, Mar, Apr and so on).
I cannot have PK on SSN in time table as it will restrict me from entering more than one record as every month I will have new record entry for this ssn=123-45-6789.
Any ideas will be highly appreciated. Please bear with me if I am silly or stupid as I am very new to databases
I have tables namely
1)emp, 2)time and 3)payroll
TABLE emp
ssn text [U]PK[/U]
name text
regular_rate number
ot_rate number
TABLE time
regular_hours number
ot_hours number
period_from date/time
period_to date/time
check_date date/time
deductions number
TABLE payroll
ssn text
name text
regular_rate number
ot_rate number
regular_hours number
ot_hours number
period_from date/time
period_to date/time
check_date date/time
regular_salary = regular_rate * regular_hours number
ot_salary=ot_ra te * ot_hours number
total_salary = regular_salary + ot_salary number
I want the user only to enter the hours and the payroll has to be calculated basing on the hours entered.
Option1: Either payroll table should be appended using append query after hours entered in time table but must restrict duplicate entries or delete duplicate entries after append.
Option2: Just not to have any payroll table at all and everything to be taken care in time table and create a form for hours entry that will restrict users from entering duplicate records. For example for ssn=123-45-6789 and period_from =Jan 01, 2008 and period_to=Jan 31, 2008. I want to restrict users from entering hours for this particular period(Jan 01, 2008 to Jan 31, 2008) if hours already exists for employee whose ssn=123-45-6789 and at the same time I want to allow database user to enter record for ssn 123-45-6789 for other periods(Feb, Mar, Apr and so on).
I cannot have PK on SSN in time table as it will restrict me from entering more than one record as every month I will have new record entry for this ssn=123-45-6789.
Any ideas will be highly appreciated. Please bear with me if I am silly or stupid as I am very new to databases
Comment