Select Employee/Course combination for each course required for the a Group

[AccessPower]

Hello all,



I am creating a report and I am not quite sure how to go about doing it. Below are my relationships.



Background

Each of the employees is assigned to a Group. Each group is assigned a set of courses. So if an employee is an engineer, he/she will take all of the courses that engineers have to take. Any training done is tracked in another table.

tblEmployees - Contains employee information, including the group to which the employee belongs
tblCourses - Contains course information
tblRequiredCoursesforJobGroups - Contains information on which courses each group requires
tblEmployeeTrainingTracker - Tracks each course completed.

What I'm trying to do

I am aiming to create a query/report that will show the training done by employees, as well as the training that has not been completed by each employee based on their job group. So if Joe is an engineer, and engineer's have to take Course1, Course2, and Course4, but has only completed Course1, and Course2, the query will show the following:

Name -- Job -------- Course --- Date Completed
Joe ----- Engineer -- Course1 -- 5 Jan 17
Joe ----- Engineer -- Course2 -- 9 Feb 17
Joe ----- Engineer -- Course4 -- Incomplete

Any ideas as to how to do this?

[aytee111]

It would really help if you called fields by the same name across tables,, giving an instant recognition of the relationship structure. For instance, JobGroup on tblEmployees needs to link directly to JobGroup on tblRequiredCoursesforJobGroups.. This is not readily apparent.

There is a table missing, the table containing job groups.

The links for your query would be tblEmployee JobGroupID to tblJobGroups, to tblRequiredCoursesforJobGroups.CourseID. Then employee id plus course id to tracking table.

Don't worry about the report or what it is going to look like, first get the query with the data correct. The wizard can create the report for you based on that.

[AccessPower]

aytee111,

Thank you for your reply. I'm attempting to get the query wizard to return the proper data; however, I am getting multiple erroneous rows:

SELECT DISTINCT tblEmployees.Name, tblRequiredCoursesforJobGroups.TrainingDescription, tblRequiredCoursesforJobGroups.Course, tblEmployeeTrainingTracker.LastCompleted
FROM tblRequiredCoursesforJobGroups INNER JOIN (tblEmployeeTrainingTracker INNER JOIN tblEmployees ON tblEmployeeTrainingTracker.[EmployeeID] = tblEmployees.[EmployeeID]) ON tblRequiredCoursesforJobGroups.[TrainingDescription] = tblEmployees.[JobGroup]
WHERE (((tblRequiredCoursesforJobGroups.Required)=True))
GROUP BY tblEmployees.Name, tblRequiredCoursesforJobGroups.TrainingDescription, tblRequiredCoursesforJobGroups.Course, tblEmployeeTrainingTracker.LastCompleted;

I can get the correct Courses based on Job Group pulled up this way:

SELECT DISTINCT tblEmployees.Name, tblRequiredCoursesforJobGroups.TrainingDescription, tblRequiredCoursesforJobGroups.Course, tblRequiredCoursesforJobGroups.Required
FROM tblRequiredCoursesforJobGroups INNER JOIN (tblEmployeeTrainingTracker INNER JOIN tblEmployees ON tblEmployeeTrainingTracker.[EmployeeID] = tblEmployees.[EmployeeID]) ON tblRequiredCoursesforJobGroups.[TrainingDescription] = tblEmployees.[JobGroup]
WHERE (((tblRequiredCoursesforJobGroups.Required)=True));

But if I add the LastCompleted to this, it shows every combination of LastCompleted date and the required courses for the job group. Also, for the ones that aren't completed it doesn't come up blank. This is where I'm stuck.

[AccessPower]

It would really help if you called fields by the same name across tables,, giving an instant recognition of the relationship structure. For instance, JobGroup on tblEmployees needs to link directly to JobGroup on tblRequiredCoursesforJobGroups.. This is not readily apparent.

There is a table missing, the table containing job groups.

The links for your query would be tblEmployee JobGroupID to tblJobGroups, to tblRequiredCoursesforJobGroups.CourseID. Then employee id plus course id to tracking table.

Don't worry about the report or what it is going to look like, first get the query with the data correct. The wizard can create the report for you based on that.

If it makes things simpler I'm only looking to pull records for incomplete courses.

[aytee111]

Try not to use the query wizard for select queries, you know what you want.

Start at the lowest echelon of data: employee ID, job group, required courses
Add the max date of the required course taken by the employee.
Add the missing courses (left join required courses to tracker table and find where the tracker course id is null).

Take as many queries as you want to get here.

Now you have all the data laid out correctly. Once this is working, add in the "user" fields - names, descriptions, etc.

[AccessPower]

Try not to use the query wizard for select queries, you know what you want.

Start at the lowest echelon of data: employee ID, job group, required courses
Add the max date of the required course taken by the employee.
Add the missing courses (left join required courses to tracker table and find where the tracker course id is null).

Take as many queries as you want to get here.

Now you have all the data laid out correctly. Once this is working, add in the "user" fields - names, descriptions, etc.

I think I have it working now! It was the left join that was messing me up. Will report back with the final results.