Copying selected records to another table

[TC]

Does each employee have his own training table? If so, that is definitely
not the way to go. You really need something like this:

tblEmployee
EmployeeID (PK)
name, address etc.

tblCourse
CourseID (PK)
description, cost, prerequisites etc.

tblEmployeeCourse
EmployeeID ( composite )
CourseID ( primay key )
date enrolled etc.

Then, you add a course to an employee by creating a new record in
tblEmployeeCourse - not by "copying the course record to the employee
training table".

This is called database normalization. Take a look at this article for more
information:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH
TC

 

[Alan]

What is the easiest way to copy a selected set of records from one table
into another without creating duplicate rows?

I need to create a simple method for the user to copy a list of job specific
training courses over to an employee. I have a table Positions which has a
set of related training required table. When the user clicks a "copy to
employee" button, each course required by the job position is copied to the
employee's Training table. It would be nice to have this code could check
that the employee has not already taken the exact training course before
copying it over.

Appreciate all the help

Alan

 

[Alan]

Sorry about that....I did a poor job of laying out the table structures.
The way you have it is basically what I have.

tblEmployee
EmployeeId (PK) tblEmployeeTrain
----------------------------------> EmployeeId

CourseId <----

| tbl Courses

|------ CourseId (PK)
tblJobTrain
| Desc, expire date, type etc......
----------> JTId (PK)
|
| CourseId
<------------
tblJobs |
JobId (PK) -----

Hope this is more clear. There are more data fields in each table
obviously. tblEmployeeTrain and tblJobTrain are nothing more than linking
tables.

The process as I understand it would be to copy the required CourseId from
the tblJobTraining to the tblEmployeeTrain marrying them with the currently
viewed EmployeeId.

What is the easiest way to do that copying while checking that the employee
doesn't already have the CourseId that I am about to copy?

 

[TC]

Sorry about that....I did a poor job of laying out the table structures.
The way you have it is basically what I have.

The linewraps mangled your diagram! I'll assume it said this:

tblEmployee
EmployeeId (PK)

tblCourses
CourseId (PK)

tblJobs
JobId (PK)

tblEmployeeTrain
EmployeeId ( compsite )
CourseId ( primary key )

tblEmployeeJob
EmployeeId ( composite )
JobId ( primary key )

The process as I understand it would be to copy the required CourseId from
the tblJobTraining to the tblEmployeeTrain marrying them with the currently
viewed EmployeeId.

What is the easiest way to do that copying while checking that the employee
doesn't already have the CourseId that I am about to copy?

You mentioned a table Positions containing a list of courses required to be
taken by a person in a given Position. I think that when a new employee
comes on board (or is promoted to a new position), you need to copy the
standard courses (from the Positions table), over to that new (or promoted)
employee, except where a course may already exist (for that employee).
Correct?

If so, you really need *two* tables to define the standard courses for each
position:

tblPosition
PositionId (PK)
description, etc.

tblPositionCourse
PositionId ( composite )
CourseId ( primary key )

Then to copy the standard courses to John Smith (except where they already
exist), it would be something like this (untested):

INSERT INTO tblEmployeeTrain (EmployeeId, CourseId)
SELECT 999, pc.CourseId
FROM tblPosition AS p,
tblPositionCourse AS pc
WHERE p.PositionId = 888
AND pc.PositionId = p.PositionId
AND NOT EXISTS
( SELECT 1
FROM tblEmployeeTrain AS et
WHERE et.EmployeeId = 999
AND et.CourseId = pc.CourseId )

where 999 is John Smith's EmployeeId, and 888 is his PositionId.

You might want to track each employee's position, over time:

tblEmployeePosition
EmployeeId ( composite )
DateEffective ( primary key )
PositionId

in which case you could amend the SQL (non-trivially!) to get the employee's
position from that table.

And a tip: go for consistency with your naming conventions:

if tblEmployee (singular), then tblCourse - not tblCourseS;
if tblEmployee and tblCourse, then tblEmployeeCOURSE - not tblEmployeeTRAIN.

Consistency makes it easier to write your SQL statements without having to
continually look-up the correct table & column names. For example, in my own
work, if I need to join tblThis to tblThat, I *know* that the junction table
is tblThisThat - I do not have to check.

HTH!
TC