Trying to eliminate double work in tables

[airbus2]

Hello all,
At the moment I have two unrelated (atleast within the db) tables. One is "Employees" the second is "Instructors". All of my "instructors" are also employees. Both tables have the same type of info (ie. firstname, lastname, employee-number, etc.) Is there a way to add a field to my employee table that identifies my instructors, and then based on that list, be able to do the things that the "instructors" table is currently doing elseware in the database.

 

[Golom]

To Add a field

ALTER TABLE Employees ADD COLUMN Instructor Bit

UPDATE Employees As E INNER JOIN Instructors As I
       ON E.EmployeeNumber = I.EmployeeNumber
SET E.Instructor = TRUE

And then

Select * From Employees
Where Instructor = True

 

[airbus2]

Thanks Golom,
Being the novice that I am, I did not quite understand what I am to do with this information. Where am I placing this code?

 

[dhookom]

Another method would be to assume you may want to manage various groups of employees (not just those who are instructors). I would do this by creating a group table like:
[tt][blue]
tblGroups
======================================
grpGrpID (autonumber primary key)
grpTitle (values like "instructor", "sunshine committee",..)
[/blue][/tt]
Then create a table for group membership:
[tt][blue]
tblEmployeeGroups
======================================
emgEmGID (autonumber primary key)
emgGrpID (links to tblGroups.grpGrpID)
emgEmpID (links to tblEmployees.empEmpID)
[/blue][/tt]
You could also add start and end dates or status fields to tblEmployeeGroups.


Duane
Hook'D on Access
MS Access MVP