Unmatched query problem

[uselessataccess]

Hi there,

I have already been asking about other aspects of this db on the table forum, I think I am just a complete numb skull at this. I have tried to get this to work but I am struggling.

So I have a training database.


5 tables. (PK indicates primary key etc)

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

What I want to do is design a Query that takes in an Employee name and returns all of the courses that his title requires and whether he has done them or not. It would then be nice to dress that up in a report in somewway.
I tried using an unmatched query but I either got no results or only the courses that employee did that were not required. As opposed to the courses required that they haven't done. And this was before I even tried adding the criteria of the employee on.
TBH, I'm somewhat tearing my hair out with this one!

Thanks,

Chris

 

[rainman89]

I was able to do this using a few queries.

First query I got the courses required with the staffID, TitleID, and CourseIDs

SELECT tblStaff.StaffNumber, tblStaff.StaffName, tblTitles.titleName, tblTitleCourses.tblCourse, tblCourse.CourseName
FROM tblCourse INNER JOIN ((tblTitles INNER JOIN tblTitleCourses ON tblTitles.titleID = tblTitleCourses.tblTitles) INNER JOIN tblStaff ON tblTitles.titleID = tblStaff.StaffTitle) ON tblCourse.CourseID = tblTitleCourses.tblCourse;

2nd I got the Courses Taken using staffNumber, courseID, courseDate, expiryDate and comments

SELECT tblTrainingRecords.staffNumber, tblCourse.CourseID, tblTrainingRecords.courseDate, tblTrainingRecords.courseExpiryDate, tblTrainingRecords.comments
FROM (tblCourse INNER JOIN ((tblTitles INNER JOIN tblTitleCourses ON tblTitles.titleID = tblTitleCourses.tblTitles) INNER JOIN tblStaff ON tblTitles.titleID = tblStaff.StaffTitle) ON tblCourse.CourseID = tblTitleCourses.tblCourse) INNER JOIN tblTrainingRecords ON (tblStaff.StaffNumber = tblTrainingRecords.staffNumber) AND (tblCourse.CourseID = tblTrainingRecords.course);

then I tied the 2 together using left joins on the courses required query and was able to get it to show required and taken in the same query

SELECT qryCoursesRequired.StaffNumber, qryCoursesRequired.StaffName, qryCoursesRequired.titleName, qryCoursesRequired.tblCourse, qryCoursesRequired.CourseName, qryCoursesTaken.courseDate
FROM qryCoursesRequired LEFT JOIN qryCoursesTaken ON (qryCoursesRequired.tblCourse = qryCoursesTaken.CourseID) AND (qryCoursesRequired.StaffNumber = qryCoursesTaken.staffNumber);

I renamed some fields in your tables, because Name is a reserved word in access

Give those a look over and see if you can see what im doing!

 

[uselessataccess]

I was able to do this using a few queries.

First query I got the courses required with the staffID, TitleID, and CourseIDs

SELECT tblStaff.StaffNumber, tblStaff.StaffName, tblTitles.titleName, tblTitleCourses.tblCourse, tblCourse.CourseName
FROM tblCourse INNER JOIN ((tblTitles INNER JOIN tblTitleCourses ON tblTitles.titleID = tblTitleCourses.tblTitles) INNER JOIN tblStaff ON tblTitles.titleID = tblStaff.StaffTitle) ON tblCourse.CourseID = tblTitleCourses.tblCourse;

2nd I got the Courses Taken using staffNumber, courseID, courseDate, expiryDate and comments

SELECT tblTrainingRecords.staffNumber, tblCourse.CourseID, tblTrainingRecords.courseDate, tblTrainingRecords.courseExpiryDate, tblTrainingRecords.comments
FROM (tblCourse INNER JOIN ((tblTitles INNER JOIN tblTitleCourses ON tblTitles.titleID = tblTitleCourses.tblTitles) INNER JOIN tblStaff ON tblTitles.titleID = tblStaff.StaffTitle) ON tblCourse.CourseID = tblTitleCourses.tblCourse) INNER JOIN tblTrainingRecords ON (tblStaff.StaffNumber = tblTrainingRecords.staffNumber) AND (tblCourse.CourseID = tblTrainingRecords.course);

then I tied the 2 together using left joins on the courses required query and was able to get it to show required and taken in the same query

SELECT qryCoursesRequired.StaffNumber, qryCoursesRequired.StaffName, qryCoursesRequired.titleName, qryCoursesRequired.tblCourse, qryCoursesRequired.CourseName, qryCoursesTaken.courseDate
FROM qryCoursesRequired LEFT JOIN qryCoursesTaken ON (qryCoursesRequired.tblCourse = qryCoursesTaken.CourseID) AND (qryCoursesRequired.StaffNumber = qryCoursesTaken.staffNumber);

I renamed some fields in your tables, because Name is a reserved word in access

Give those a look over and see if you can see what im doing!

First of all, thanks a million for the help.

I have to level with you though, I don't really follow what you are doing. On the upside, I got it to work.

Thanks a million.

One last thing, if I wanted to query for a particular staff member, how would I add that in?

 

[rainman89]

What exactly are you not following? Maybe i can explain it alittle better

In the last query you would add a where statement

so something like

where qryCoursesTaken.staffnumber= some number