Need help with append and delete duplicates

Hello Friends,

I Need help with append and delete duplicates


I have tables namely

1)emp, 2)time and 3)payroll

TABLE emp
ssn text PK
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. What is the best option I should adopt and how. Please bear with me if I am silly or stupid as I am very new to databases

re: Need help with append and delete duplicates

It looks like you should make employee ID, period_to, and period_from the fields for a primary key in the time table.

Using that as the primary key, you could only enter the employee once for each period (but still have the employee listed every pay period).

Also, it looks like you don't need the actual table 'payroll'--all the information there can be derived from the other two tables so I would actually just make a query to pull that information.

Let me know if I can further explain any of my ideas above..

Thanks,

Zachh

re: Need help with append and delete duplicates

Thanks for the reply Zachh.

If I make period_from or period_to as prime key it will allow me to enter data for employee a but it then it will not allow me to enter data for employee b

Thanks,

Allingame

re: Need help with append and delete duplicates

The point here is that keys (Primary Key in this case) need not be restricted to a single field.

Complex keys are made up of multiple individual fields. This is what has been suggested and I see this as a good way to hold your data.