What skills are required?

[elillis]

I have a Training database with 2 queries:
qryA contains fields 'Projs', 'Roles', and 'Skills';
qryB contains fields 'Projs', 'Roles', 'Skills', and 'people'.

QryA is setup to contain all the skills required for each Role for each
project.
QryB is setup to contain all the people and their skills, and what Role they
play on a project, if any.

I did a successful cross tab query to acquire a 'spreadsheet' like display
of all projects, roles, skills, and people, but this is not all I want
because I do not want to have to count each empty cell.

How can I tell what skills are required by each person to satisfy the Roles
they have been assigned on a project?
I want to know how many people will require training in each skill for the
whole company.

 

[KARL DEWEY]

You need to start with the company employee list and their position -ROLE -
manager, file cleck, typist, mechanic, electrician.

The manager will need supervisory, budgeting, planning, etc.

The electriacian will need journeyman electrician, safety, first aid, CPR,
tower climbing certification, etc.

This information must be gleaned from the position information in HR and
their supervisors - all electricians will not need climbing certification.

 

[Van T. Dinh]

* I think the Field names you posted may be misleading here. The Field
"Skills" in QryA is the "Required Skill" for the Role in the Project. The
same Field name in the QryB is the "Achieved Skill" (by Person).

* Description of the Queries is probably not enough info. Please post you
relevant Table Structure including their relationships and described in
words what you need to return by the Query. Also, state whether a Person
has exactly one Role or can have multiple Roles in same / different Projects
(at the same time).

* From your description, I am not 100% sure that you want the Query returns,
says, only Skills that a Person *needs to be trained* to perform a Role in a
Project, or simply all the Skills that a Person *needs to possess* to
perform a Role in a Project, i.e. including Skills that he/she already
achieved .

 

[elillis]

I want the query to output what skills are missing for the role/project a
person is assigned.
1. Your understanding of the field name issue is correct.
2. There are 9 tables: Projects, Roles, Skills, People, ProjRoles,
ProjPeople, RoleSkills, PeolpeSkills, ProjRolesPeople.
Each of the first 4 tables 'feed' 2 or more of the remaining tables.
(ProjRoles and People 'feed' ProjRolesPeople)
3. A person can have 2 or more roles. A project can have 2 or more roles.
A person can be on more than one project.

Thanks
Ed

 

[Van T. Dinh]

I was afraid of this. At least you have the correct Table Structure.

Let me think for a while to see which one is the easiest way and come back
later.

 

[Van T. Dinh]

* qrySkillsNeeded:

This Query returns rows of PersonID and SkillID the Person needs to perform
various Roles he/she has been assigned. Assigned Roles may have duplicate
required Skills and therefore the keyword DISTINCT. SQL String is something
like:

SELECT DISTINCT PRP.frg_PersonID, RS.frg_SkillID
FROM ProjRolePeople AS PRP INNER JOIN
RoleSkills AS RS
ON PRP.frg_RoleID = RS.frg_RoleID


* qrySkillsPossessed:

This Query returns rows of PersonID and SkillID that the Person already
possessed. SQL is someting like:

SELECT PS.frg_PersonID, PS.frg_SkillID
FROM PeopleSkills AS PS


* qrySkillsTrainingRequired

Use a "frustrated Outer Join" from qrySkillsNeeded to qrySkillsPossessed
should give you the Skills that each Person needs. The SQL should be
something like:

SELECT QSN.frg_PersonID, QSN.frg_SkillID
FROM qrySkillsNeeded AS QSN LEFT JOIN
qrySkillsPossessed AS QSP
ON (QSN.frg_PersonID = QSP.frg_PersonID)
AND (QSN.frg_SkillID = QSP.frg_SkillID)
WHERE (QSP.frg_PersonID Is Null)

In fact, you don't need the qrySkillsPossesed. You can use the Table
PeopleSkills directly. I used the Query to rename for more meaningful name
(in this context) to illustrate the idea.

You can combine all into one Query like:

* qrySkillsTraningRequired_Combined:

SELECT DISTINCT PRP.frg_PersonID, RS.frg_SkillID
FROM
( ProjRolePeople AS PRP
INNER JOIN RoleSkills AS RS
ON PRP.frg_RoleID = RS.frg_RoleID
)
LEFT JOIN PeopleSkills AS PS
ON (PRP.frg_PersonID = PS.frg_PersonID)
AND (RS.frg_SkillID = PS.frg_SkillID)
WHERE (PS.frg_PersonID Is Null)

If you need a Cross-Tab, use either of the last 2 as the Data Source for the
Cross-Tab Query.

Looking at your Table Structure again, Table ProjPeople is redundant since
Project and People are already related (indirectly) via Table ProjRoles and
ProjectRolesPeople. In fact having the Table ProjPeople may cause
inconsistencies in the data stored.

Note: all SQL Strings are untested. Hopefully, you understand the idea and
adjust the SQL as needed.

 

[elillis]

Thanks,
I never heard of a "frustrated Outer Join" before, but I will study your
solution.

You were right about the redundant table. I will have to re-think that
because I only want to assign a role to a person already assigned to the
project.
--Ed