identify employees that have/haven't taken certain training course

[NewAccessUser]

I'm new to Microsoft Access and my boss recently asked me to create a
'training database' for our department.

The company I work for offers hundreds of training programs and your labor
grade depends on which courses you've taken.

I have three tables:
1) A table containing employee information (Employee #, Employee Name,
Department)

2) A table containing all of the training records. (Employee #, Employee
Name, Course Code, Course Title, Needed to Achieve labor grade, Date
Completed). This table can have more than one entry per course and more than
one entry and more than one entry per person (because one person can take
multiple courses)

3) A table containing a list of all the courses available (Course Code,
Course Title, Needed To Achieve Labor Grade).

An employee can't achieve the next labor grade until s/he completes all of
the courses in the previous labor grade.

The purpose of the database is to be able to pull up a report outlining
which courses are needed for any given employee to move up a labor grade.
Ideally, you would be able to search by employee number (or name) and a list
of all the courses available would come up outlining whether or not they have
completed that course. The column headings would be: Course Code, Course
Title, Completed (YES/NO), Date Completed.

I've tried thousands of queries, but I can't seem to get it. Any help
offered on this database would be greatly appreciated.

 

[Arvin Meyer [MVP]]

You need a query with a left join (also called an outer join). After joining
your courses table to employee course table, right-click on the join and
choose "Join Properties" Now pick all of the Employee Course and only those
courses that match. You should see a small arrow pointing at the end of your
join. In the CourseID column, use a criteria of "Is Null"

 

[Ken Sheridan]

Firstly you should normalize your tables. Normalization is the process of
removing redundancy so that each 'fact' is stored only once in the database.
Otherwise it is at risk of inconsistent data being entered. At present your
Training Records table repeats the employee name for each course an employee
has taken. You only need Employee # column in this table as the name is
already in the Employees table. Similarly you don't need the Course Title or
Needed to Achieve labor grade columns in the Training Records table, only the
Course Code and Date Completed columns, so delete the Employee Name, Course
Title and Needed to Achieve labor grade columns. Your tables will then be
properly normalized. To get these values you join the tables in queries,
which will be guaranteed to always return the correct values as by
eliminating the redundancy you've eliminated the possibility of inconsistent
values being entered.

Now, to answer your question, you'll need to use a subquery to determine
which courses an employee not taken. A LEFT OUTER JOIN will not work here as
you want to restrict the results on a column in the Training Records table
(Employee #). A restriction on a column in a table on the outer side of an
OUTER JOIN turns it into a normal INNER JOIN, so you'd not get the courses
not completed by the employee. An OUTER JOIN would only tell you which
courses no employee had completed. A subquery, as its name suggests, is a
query within another query. It can be used to return a value or, as here, to
restrict the rows returned by the outer query.

The subquery will return the courses not attended, but you can return the
courses attended with a simple INNER JOIN, so to combine both you tack them
together with a UNION ALL operation.

This does mean you'll have to write some SQL rather than creating the query
in design view. To do this switch to SQL view in the query designer. The
SQL will be along these lines, though you'll probably need to change table
and/or column names to your actual names:

SELECT [Courses].[Course Code], [Courses].[Course Title],
"Yes" AS Completed, [Training Records].[Date Completed]
FROM [Courses] INNER JOIN [Training Records]
ON [Training Records].[Course Code] = [Courses].[Course Code]
WHERE [Training Records].[Employee #] = [Enter Employee Number:]
UNION ALL
SELECT [Courses].[Course Code], [Courses].[Course Title],"No", NULL
FROM [Courses]
WHERE NOT EXISTS(
SELECT *
FROM [Training Records]
WHERE [Training Records].[Course Code] = [Courses].[Course Code]
AND [Training Records].[Employee #] = [Enter Employee Number:])
ORDER BY [Courses].[Course Title];

The first part of the UNION ALL operation is a simple query using an INNER
JOIN. Because this only returns rows where there is a match between the
tables, i.e. where a course has been completed, you can use a constant "Yes"
for the Completed column. The query prompts for the employee # as a
parameter at runtime, so you can use the same query for any employee,
entering the appropriate employee number when prompted each time its run.
Don't use names as a parameter; they can be duplicated – I once worked with
two Maggie Taylors in the same office!

The second part of the operation is more complex as it uses a subquery. The
subquery returns rows from the Training Records table if there is a match
with the current course code and the employee # entered at the parameter
prompt. The outer query uses the NOT EXISTS predicate against this subquery.
This simply means that the outer query will only return a row where the
subquery returns no rows which match that particular row, i.e. where the
employee has not completed that course. In this case you can use a constant
"No" for the Completed column and a NULL for the date Completed column as
there is obviously no date for a course not completed.

Finally the ORDER BY clause sorts the result set by the courses title. If
you are using the query as the RecordSource for a report, however, don't
include an ORDER BY clause in the query. Instead use the reports own
internal 'sorting and grouping' mechanism to order the report.

Once you've got this working you can make it a lot more user friendly by
creating a dialogue form with a combo box to select an employee from rather
than the user having to enter their number each time. A button on the form
can open the query; or better still a report based on the query (or you
could have two buttons, one to preview and one to print the report).
However, that's something we can come back to once you have the query
operating successfully.

Ken Sheridan
Stafford, England