Criteria query

[Naz]

Hi

I have 3 tables as follows

*tbl_employees*
empID
Name
JobRole


*tbl_Coursestaken*
empID
CourseTitle
Date

*Profile*
JobRole
CourseTitle1
CourseTitle2
CourseTitle3
CourseTitle4
CourseTitle5

The Profile table lists the madatory courses for each JobRole. I want to
create a query that for each job role tells me who has not been on which
mandatory course. So ideally i want name and mandatory course still to be
attended.
I know that an Unmatched query can do something similiar but i can't figure
out how to get it to work for more than 1 course (criteria).

Can anyone help.

Naz

 

[Jerry Whittle]

The trouble is with your Profile table in that you have CourseTitle columns.
Let me ask this: what happens to your reports, forms, and queries when you
need to add CourseTitle6 then CourseTitle7, etc.

The Profile table needs to look more like:
JobRole CourseTitleID

Then you need a CourseTitle table with fields like:

CourseTitleID CourseTitleName CourseTitleDescription etc.

 

[Dale Fye]

Jerry is right, with the structure of your Profile table, this is a little
problematic, although not impossible.

1. First, you need to create a query that normallizes your Profile table.
It will look something like:

qry_RequiredCourses:

SELECT JobRole, CourseTitle1 as CourseTitle FROM Profile WHERE CourseTitle1
IS NOT NULL
UNION ALL
SELECT JobRole, CourseTitle2 as CourseTitle FROM Profile WHERE CourseTitle2
IS NOT NULL
....
UNION ALL
SELECT JobRole, CourseTitle5 as CourseTitle FROM Profile WHERE CourseTitle5
IS NOT NULL

2. Now, you need to create the query that joins the other tables to this
query. The first part of it will look like the query below. Save this as
qry_EmpReqCourses.

SELECT tbl_Employees.EmpID,
tbl_Employees.Name,
qry_RequiredCourses.CourseTitle
FROM tbl_Employees
INNER JOIN qry_RequiredCourses
ON tbl_Employees.JobRole = qry_RequiredCourses.JobRole

3. Now create a new query that has qry_EmpReqCourses and tbl_CoursesTaken,
something like:

SELECT qry_EmpReqCourses.EmpID,
qry_EmpReqCourses.Name,
qry_EmpReqCourses.CourseTitle
FROM qry_EmpReqCourses
LEFT JOIN tbl_CoursesTaken
ON qry_EmpReqCourses.EmpID = tbl_CoursesTaken.EmpID
AND qry_EmpReqCourses.CourseTitle = tbl_CoursesTaken.CourseTitle
WHERE tbl_CoursesTaken.EmpID IS NULL

HTH
Dale

 

[Naz]

Hi Jerry...thanks for your response

Can you clarify a bit more for me....are you saying that my profile table
should only have Job_Role and CourseID in it?
If that is the case how do i create an input form....(newbie-using the
wizard) won't i just end up with a form with just 1 field for for mandatory
course...just being able to choose 1 course per jobrole or is there a
different way to set up the form.
I get the gist of what your saying but not able to see, but just miss a bit.


Many thanks

 

[Jerry Whittle]

Keeping your head above water? Sounds like the Thames is about to top the
London Eye Ferris Wheel!

If I'm seeing things correctly, you have a many-to-many relationship as a
JobRole can have many Courses and a Course can have many JobRoles. To break
up the M-M join you need a linking or bridging table to split it into two 1-M
joins. That's where the Profile table comes in.

You want the primary key from the JobRole and Courses table in the Profile
table to link the two tables together. This is often done by creating a form
based on the Profile table. It would have two combo boxes with their record
source the primary keys of the other two tables. That way you pick one from
each side to join them together.