Data Hierarchy

[anirbas]

Hello,

I have a database that manages employee training and documents. As part of my data structure, employees are assigned a job function which contains a set of procedures they are required to train on. Each employee may be assigned many job functions (there is the company core, department core, and specific job details). Some of the job functions may have overlap of procedures.

I would like to be able to run a report that compares the employee current progress against the job functions they have been assigned, but only show each requirement once even if it appears multiple times.

The tables involved in this are:

tblEmployee
EmployeeID
FirstName
LastName

tblJobDetail
ID
JobID
ProcedureID
TargetProf

tblJob
JobID
JobTitle
Dept

tblITR
ID
JobID
EmplyeeID

All help is appreciated. Thank you!

 

[ranman256]

1. you will need an outer join to show ALL Job functions and join to the employee to show what they have done.
2. sometimes setting the query property UNIQUE VALUES = YES will stop duplicates. but it depends on the query.

 

[anirbas]

Unique values won't help me here because they are all unique - they are tied to different Jobs. I would like a list that shows each procedure only once even if it has been assigned to an employee 10 different times.