Employee Training

[Glenda]

I have 3 tables;
Employee (name, jobtitle, etc)
TrainingTitle (trainingtitle, jobtitle)
Training History (date, trainingtitle, employeename)

I'm not sure how to link these tables to create a report that has the
employee name, and the trainingtitle ONLY if it is required for that
employee's jobtitle.

thank you for your help

 

[Rick]

I have 3 tables;
Employee (name, jobtitle, etc)
TrainingTitle (trainingtitle, jobtitle)
Training History (date, trainingtitle, employeename)

I'm not sure how to link these tables to create a report that has the
employee name, and the trainingtitle ONLY if it is required for that
employee's jobtitle.  

thank you for your help

First you would need to add a field to TrainingTitle table
(required=yes/no)

Then fill in that field for all jobtitles, trainingtitle combinations.

Then you can work on your report.

 

[erdalllamine]

I have 3 tables;
Employee (name, jobtitle, etc)
TrainingTitle (trainingtitle, jobtitle)
Training History (date, trainingtitle, employeename)

I'm not sure how to link these tables to create a report that has the
employee name, and the trainingtitle ONLY if it is required for that
employee's jobtitle.

thank you for your help

 

[Jeff Boyce]

Uhmmm ... what happens when you have two employees named "John Smith"?

Using "[EmployeeName]" as a primary key, or even just to join two tables, is
risky.

By the way, your first table mentions "name" -- if that's the actual title
of your fieldname, change it! Microsoft Access treats that word as a
reserved word, so what YOU mean by it and what ACCESS means by it may not
always match!

(and as suggested elsethread, unless you have an indicator in one of the
tables that a particular training is REQUIRED for a particular employee,
there's no amount of joining/linking that will make it magically appear.)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Steve]

You need the following tables:

TblJobTitle
JobTitleID
JobTitle

TblEmployee
EmployeeID
Firstname
LastName
JobTitleID
<other Employee fields>

TblTrainingTitle
TrainingTitleID
TrainingTitle

TblTrainingTitleForJobTitle
TrainingTitleForJobTitleID
JobTitleID
TrainingTitleID

TblTrainingHistory
TrainingHistoryID
EmployeeID
TrainingTitleID
TrainingDate

For your report, start with a query based on TblEmployee, TblTrainingHistory
and TblTrainingTitle. Include TrainingTitleID from TblTrainingHistory,
Concatenate FirstName and LastName to get the employee name and include
TrainingTitle from TblTrainingTitle.

Next create an unmatched query based on TblTrainingTitleForJobTitle. You
want to find TrainingTitleID in TblTrainingTitleForJobTitle that is not in
the above query. These will be the training the employee has not received.
The unmatched query wizard will ask you what fields to display and you need
to say EmployeeName and TrainingTitle.

Make the unmatched query the recordsource of your report.

Steve
(e-mail address removed)