468,505 Members | 1,714 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,505 developers. It's quick & easy.

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
Jun 22 '08 #1
3 2037
debasisdas
8,127 Expert 4TB
What is the database you are using ?
Jun 23 '08 #2
What is the database you are using ?
I am using MS-Access
Jun 23 '08 #3
Dave44
153 100+
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.
Jun 24 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by george young | last post: by
3 posts views Thread by Alexander Anderson | last post: by
7 posts views Thread by Christian Christmann | last post: by
3 posts views Thread by jw | last post: by
1 post views Thread by John Wright | last post: by
reply views Thread by NPC403 | last post: by
3 posts views Thread by gieforce | last post: by
reply views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.