Linking competancies in tables

[uselessataccess]

Hi there,

I am not too sure how to phrase this question (hence I can't find an answer via google).

So I have a training database.



4 tables. (PK indicates primary key)

• tblCourses (2 fields; CourseID (PK), Name)
• tblEmployees (3 fields; Name, Staff Number (PK), Title(linked to title in tblTitles)
• tblTitles (1 field; title(PK))
• tblTraining Record (6 fields, record no. (PK), Name (linked to Staff number), Course (linked to courseID), Course date, course expiry date, comments)

Ok, depending on the employees title there are different courses that they need to take. For instance, a "field engineer" would need say "First Aid", "Advanced Driving Course", "Technician certificate".

I would like to store the required courses linked with each title so that I can query an employee and be told what training they need for their role and if they do have some of the courses, when did they do it.

I am fairly sure I could write a query if I could just get the table/relationship structure right.

I hope this is simple enough to solve,

Appreciate any help!

Thanks,

C

 

[HiTechCoach]

Hi there,

I am not too sure how to phrase this question (hence I can't find an answer via google).

So I have a training database.



4 tables. (PK indicates primary key)

• tblCourses (2 fields; CourseID (PK), Name)
• tblEmployees (3 fields; Name, Staff Number (PK), Title(linked to title in tblTitles)
• tblTitles (1 field; title(PK))
• tblTraining Record (6 fields, record no. (PK), Name (linked to Staff number), Course (linked to courseID), Course date, course expiry date, comments)

Ok, depending on the employees title there are different courses that they need to take. For instance, a "field engineer" would need say "First Aid", "Advanced Driving Course", "Technician certificate".

I would like to store the required courses linked with each title so that I can query an employee and be told what training they need for their role and if they do have some of the courses, when did they do it.

I am fairly sure I could write a query if I could just get the table/relationship structure right.

I hope this is simple enough to solve,

Appreciate any help!

Thanks,

C

C,

First, all table need to have an auto number primary key.

Example:

tblTitles (2 field;
- TitleID (PK - autonumber)
= TitleDesc (Indexed, unique)


I would create a 5th table that is a junction table between
tblCourses and tblTitles

tblTileCourses:
= TileCoursesID - PK autonumber
- TitlesID - long integer - FK tblTitles
- CourseID - long integer - FK to tblCourses

 

[uselessataccess]

C,

First, all table need to have an auto number primary key.

Example:

tblTitles (2 field;
- TitleID (PK - autonumber)
= TitleDesc (Indexed, unique)


I would create a 5th table that is a junction table between
tblCourses and tblTitles

tblTileCourses:
= TileCoursesID - PK autonumber
- TitlesID - long integer - FK tblTitles
- CourseID - long integer - FK to tblCourses

Thanks HiTechCoach!

I'll do that.

Just to check so if I want to link a particular title with a course, I just enter a row in the junction table with the appropriate TitleID and CourseID?

 

[HiTechCoach]

Thanks HiTechCoach!

I'll do that.

Just to check so if I want to link a particular title with a course, I just enter a row in the junction table with the appropriate TitleID and CourseID?

That is correct.

I would use a sub form for viewing/editing the junction table.

For example, on the form for Titles have a subform for linking courses

 

[uselessataccess]

That is correct.

I would use a sub form for viewing/editing the junction table.

For example, on the form for Titles have a subform for linking courses

Thanks again,

So i have entered the data as described above.
Some courses people do are not required. How can I write a query that will return all courses done and indicate the required ones?

Also, coming the other way at it, select an employee and returns required courses not done/out of date.

This probably could move to query forum, but I thought since it carried on from my previous question I could post here.

you folks are really a savage resource!

 

[HiTechCoach]

Thanks again,

So i have entered the data as described above.
Some courses people do are not required. How can I write a query that will return all courses done and indicate the required ones?

Also, coming the other way at it, select an employee and returns required courses not done/out of date.

This probably could move to query forum, but I thought since it carried on from my previous question I could post here.

you folks are really a savage resource!

An unmatched query will tell you which records are missing.

How are you tracking which are the required courses?

 

[uselessataccess]

An unmatched query will tell you which records are missing.

How are you tracking which are the required courses?

I have never used unmatched queries before but I reckon i can follow the how to's that google throws up. Thanks.

I don't really get what you mean about tracking required courses? At the moment, as discussed above, I have a junction table with 3 fields, IDnumber, TitleID and CourseID. The latter two are linked the relevant tables.