Automaticall move records between tables

[BrookieOU]

I have a table in my database that tracks employees. We would like to begin
tracking candidates. Is it possible to create a table for candidates where
once I filled in the hire date field it would automatically move the
information I need from the candidates table to the employees table?

 

[Jeff Boyce]

If you were working in Excel or another spreadsheet, it might make sense to
"move" folks from one spreadsheet to another.

You posted in an MS Access newsgroup, so I'll assume you're trying to do
something in Access.

Instead of moving, what about adding a [DateHired] field to your table?
When you have someone who is a candidate, they won't have a value in that
field (yet). Once you hire them, just add the date. Now all your records
are in a single table!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

I don't favour using a single table as Jeff suggests. Candidates and
Employees are in my view different entity types as, while they have some
attributes in common, others are specific to each entity type. I deal with
this in more detail below in connection with their being sub-types of a
'people' type. Firstly I'll cover the two-table model:

You'd handle it in a form bound to the Candidates table with code which (a)
executes an 'append' query to insert a row into the Employees table, and (b)
then (possibly) deletes the row from the Candidates table. You can do this
in the AfterUpdate event procedure of the HireDate control on the candidates
form. You'll need a primary key column of Candidates to identify the current
record, so I'll assume this is called CandidateID and is a number data type,
e.g. an autonumber. For this example I'll assume for simplicity that it’s
the FirstName, LastName and HireDate columns that you'll fill in the
Employees table:

Const conMESSAGE = _
"Move current candidate to employees table?"
Dim cmd As ADODB.Command
Dim strSQL As String

If MsgBox(conMESSAGE, vbQuestion + vbYesNo, "Confirm") = vbYes Then
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' first save current record
Me.Dirty = False

' insert current row into Employees
strSQL = "INSERT INTO Employees" & _
"(FirstName, LastName, HireDate) " & _
"SELECT FirstName, LastName, HireDate " & _
"FROM Candidates " & _
"WHERE CandidateID = " & Me.CandidateID
cmd.CommandText = strSQL
cmd.Execute

' delete current record
strSQL = "DELETE * " & _
"FROM Candidates " & _
"WHERE CandidateID = " & Me.CandidateID
cmd.CommandText = strSQL
cmd.Execute

' requery form
Me.Requery
End If

You might not want to delete the candidate record but just copy the data to
Employees so that data about an employee's candidature was retained.
However, this would leave the database open to inconsistent data being
entered as there would be nothing to stop a name being changed in one table
but not the other for instance.

To get round this Candidates and Employees are regarded as sub-types of a
People type, so rather than having two tables, you'd have three, People,
Employees and Candidates. People would have columns for those attributes
common to both candidates and employees, names, date of birth, address data
etc, while candidates and employees would have columns for those attributes
specific to each sub-type, e.g. interview date for candidates and
DepartmentID for employees. The relationships between People and Candidates,
and between people and Employees would be on-to-one, i.e. the primary keys of
Candidates and Employees would also be foreign keys referencing the primary
key of People.

With this normalized design you'd still insert a row with data from
Candidates into Employees on hiring, but only the primary key column, as all
the common attributes would remain in place in the people table. The primary
keys of Candidates and Employees could not be autonumbers of course, but
straightforward long integer number data type. The primary key of people can
be an autonumber, however.

Ken Sheridan
Stafford, England

 

[Allen Browne]

You already have some good answers, but if you want more information, see:
Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

The article talks about archiving, but the process is the same for what you
want to do, and the transaction makes it safe.

 

[Klatuu]

Sure. Write an Append query that will update the employee table with the
data from the candidate table, then a delete query to delete the data from
the candidate table.