problem setting up relationships

[k_c_morrissey]

I have a table which contains a list of names and the trades that each one is qualified for and a second table for a project which requires a number of different trades to fill specific roles. How do I set up a proper relationship between the two tables.

Tbl_employee.
empl_ID| Empl_Name | Discipline
001 | Mathew | Construction Manager
002 | Mark | Safety officer
003 | Luke | Construction Manager
004 | John | Architect

Tbl_Proj.
Project_ID
Project_name
Project_Architect
Project Manager
Health & Safety

Each employee could have more than one role on each project, one role on each project, or no roles on a project.

I have a form that shows a complete record for each project and permits me to select who fills what roles from a combo box for each role.

Using the relationship editor I try to create relationships between the following:

Discipline - Project Manager
Discipline - Project_Architect
Discipline - Health & Safety

I then set up a SELECT in the rowsource of each combo box on the form WHERE the Discipline is whichever is required by the combobox

The raltionship editor allows the creation of a relationship between Discipline and the (multiple) various roles on the project, but if I try to 'Enforce Referential Integrity' It refuses to work.

Do I need 'Referential Integrity' and if so How do I make it work?

Thx,
Kev.

 

[Pat Hartman]

Yes, you need Referential Integrity. Your schema is not quite right. I've included a more comprehensive one that will allow for more flexibility.

It is also possible for an individual to have more than one skill. You need several tables to implement this relationship.

tblEmployee
EmployeeID (autonumber primary key)
FirstName
LastName
Etc.

tblSkill
SkillID (autonumber primary key)
SkillDesc
Etc.

tblEmployeeSkills
EmployeeID (primary key fld1, foreign key to tblEmployee)
SkillID (primary key fld2, foreign key to tblSkill)
SkillAcquiredDt
Etc.

tblProject
ProjectID (autonumber primary key)
ProjectName
Etc.

tblProjectSkills
ProjectID (primary key fld1, foreign key to tblProject)
SkillID (primary key fld2, foreign key to tblEmployeeSkills)
EmployeeID (primary key fld3, foreign key to tblEmployeeSkills)