Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to eliminate double work in tables

Status
Not open for further replies.

airbus2

Technical User
Apr 16, 2008
31
US
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.
 
To Add a field
Code:
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
Code:
Select * From Employees
Where Instructor = True
 
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?
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top