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
3  2367 
What is the database you are using ?
What is the database you are using ?
I am using MS-Access
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: george young |
last post by:
For each run of my app, I have a known set of (<100) wafer names.
Names are sometimes simply integers, sometimes a short string, and
sometimes a short string followed by an integer, e.g.:
5, 6, 7, 8, 9, bar, foo_6, foo_7, foo_8, foo_9, foo_10, foo_11
I need to read user input of a subset of these. The user will type a
set of names separated by commas (with optional white space), but there
may also be sequences indicated by a dash...
|
by: Alexander Anderson |
last post by:
I have a DELETE statement that deletes duplicate data from a table. It
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:
+-----------+
| a | b |
+-----+-----+
| 123 | 234 |
| 345 | 456 |
|
by: Christian Christmann |
last post by:
Hi,
in the past I always appreciated your help and hope that you also can help
me this time. I've spent many many hours but still can't solve the
problem by myself and you are my last hope.
I've a program which is using self-written double-linked lists as a data
structure. The template list consists of list elements and the list itself
linking the list elements.
|
by: Amy |
last post by:
I'm trying to add an autoincrementing id to a table based on an
existing field Name, but Name has duplicated records. How can I do
that in ACCESS?
Thanks.
Amy
|
by: Mark |
last post by:
I have a table about people containing 25 fields. The table contains the usual
fields - first, last, address, city, state and zip. There is no primary key.
These fields all have data with the exception of a few records missing zip. A
person may be in one to five records in the database. If a person is in multiple
records, the other fields in the table in each record for that person may or may
not contain data. I have two problems:
1. I...
| | |
by: jw |
last post by:
i have a program it has a list of random numbers and my aim is to add
the nodes in another list
the nodes will be in an order at the new link list
#include<iostream>
#include<cmath>
using namespace std;
class Node{
private:
|
by: John Wright |
last post by:
I am running a console application that connects to an Access database (8
million rows) and converts it to a text file and then cleans and compacts
the database. When it runs I get the following error:
The CLR has been unable to transition from COM context 0x1a2008 to COM
context 0x1a2178 for 60 seconds. The thread that owns the destination
context/apartment is most likely either doing a non pumping wait or
processing a very long...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for
renaming the duplicate records? My thinking was to take the results of the
duplicate query, and somehow have it number each line where there is a
duplicate (tried a groups query, but "count" won't work), then do an update
query to change the duplicate to...
|
by: allingame |
last post by:
Hello Friends,
I Need help with append and delete duplicates
I have tables namely
1)emp, 2)time and 3)payroll
TABLE emp
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
