Need help with append and delete duplicates

[allingame]

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_rate * 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

[debasisdas]

What is the database you are using ?

[allingame]

What is the database you are using ?

I am using MS-Access

[Dave44]

Ya this thread really should be in the access forum, not in oracle.

As to your question, i would put constraints in place to not let duplicates in. Garbage in... garbage out... i wouldnt let it in in the first place then you dont have to worry about deleting it out after.