Many to Many relationship

[Marlie]

Hi

I have a database with 5 tables for tracking employee training. The tables are

Employee Info. Primary key is autonumbered and table is normalized
Positions "


Courses "
Departments "
Courses Required for Position. This only has four fields. The id which is autonumbered, the position number, course number and months valid.

Competency. This is a table which records which courses have been completed by each employee. It has the employee id, position id, course id, date completed, score. I don't have a primary key for this one yet.
I have created relationships from the first four tables to other tables where info is repeated (one to many)

My problem is, I need info from the last two tables in one view. I want to create a query that will show ALL of the courses required for each employee (that I can do) with the courses completed marked as complete (that I can't do), so for example if an employee is required to take 50 courses, I want to see all fify in the result with a field that marks which ones are complete and which are not. I need to combine the info from both the courses requried and the competency tables and I can't seem to do that. I don't know if it is a relationship issue or what. These tables have a many to many relationship. I have them both related to the other tables, but I can't seem to get the results.

I would appreciate any help I can get.

Marlie

[matthewspatrick]

Marlie,

You're on the right track. I'd go with something like:

tblEmployees
---------------------------------------------------
EmpID (PK)
PosID (FK)
EmpLName
EmpFName
<other employee attributes>

tblPositions
---------------------------------------------------
PosID (PK)
PosTitle
DeptID (FK)
<other position attributes

tblCourses
---------------------------------------------------
CourseID (PK)
CourseTitle
<other course attributes>

tblDepartments
---------------------------------------------------
DeptID (PK)
DeptName
<other dept attributes>

tblCoursesReqd
---------------------------------------------------
PosID (PK, FK)
CourseID (PK, FK)
DaysValid
PassingScore

tblCoursesTaken
---------------------------------------------------
EmpID (PK, FK)
CourseID (PK, FK)
DateCompleted
EmpScore


The changes/assumptions I made compared to your original structure:

1) On some tables I suggest a multi-field primary key

2) I am explicitly tying the position to the department, so if 2+ departments have similar positions, they need two records in the position table. This may not be quite what you want.

3) I removed position number from your competency table: if the employee table indicates the employee's position, then there is no need to duplicate that data on the competency table (that would, indeed, break the normalization rules)

4) I changed the interval for how long a course is from months to days, as days are easier to calculate and less prone to ambiguity

5) I added a concept of a "passing score"



Once you've got a db with some data in it, if you post the mdb file I could see about generating the SQL to return the results you're looking for.

[Marlie]

Thanks

I really appreciate this help.

How do I post a file in here.? I don't see any attachment icons or instructions. Would you prefer I send it to you in email?

Marlie

[matthewspatrick]

Marlie,

I'd prefer that you upload the file to a publicly accessible site; that way, other people reading the question might benefit. If you do not have web space of your own, you may want to get free web space at www.geocities.com.

[Marlie]

Thanks

Marlie

[Marlie]

Hi

The database is already populated with thousands of records. It is confidential so I won't be posting it. I know I could create bogus data, but I don't have the time, so I will do what I can with it and hope for the best.

Thanks for your time

Marlie

[matthewspatrick]

Marlie,

In that case, if you send me a slimmed down db privately, if I have time next week I can work on a query or two.