If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automaticall move records between tables
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? |
#2
|
|||
|
|||
Automaticall move records between tables
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 "BrookieOU" wrote in message ... 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? |
#3
|
|||
|
|||
Automaticall move records between tables
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 "BrookieOU" wrote: 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? |
#4
|
|||
|
|||
Automaticall move records between tables
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BrookieOU" wrote in message ... 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? |
#5
|
|||
|
|||
Automaticall move records between tables
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. "BrookieOU" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|