how to get query to show "empty values", in my Training Matrix

[i-Zapp]

I've stumped all my local experts, and searching for this seems tough, so forgive me if this is overly simple...

I am building a Training Matrix that will identify which "skills" certain technicians possess. One table is the list of ALL of the skills a technician is expected to have, and the other table is where the technician is linked to some of those skills. Simple enough.

Ultimately, the objective is to show when a technician has been trained on certain skills. An image of the expected output is shown below (faked).

The difficulty that I'm having is getting the query to show the skills he's NOT YET been trained on, so that it presents like a checklist or to-do list. My REAL query/report only shows those tasks for which he IS trained.



How do I do that? (I've attached my super simple database)

[pbaldy]

See if this gets you started:

SELECT [training matrix].technician, [training matrix].skill, [training matrix].approved, skills.Field1
FROM skills LEFT JOIN [training matrix] ON skills.ID = [training matrix].skill
WHERE ((([training matrix].technician)="bob" Or ([training matrix].technician) Is Null));

[ArviLaanemets]

Let's assume you have tables like:
tblTechnicians: TechnicianID, Forename, LastName, ...;
tblSkills: SkillID, SkillDescription, ...;
tblTechicianSkillSet: SkillSetID, TechnicianID, SkillID;
tblTechnicianTrainings: TechnicianTrainingID, SkillSetID, TrainingDate, TrainingApproved (TrainingApproved may have e.g. values TRUE/FALSE, or 1/0).

Let's continue step-wise.
To get the list of all technicians:

SELECT  tech.TechnicianID, tech.ForeName & " " & tech.LastName As FullName FROM tblTechnicians tech

To get requested skillsets for technicians:

SELECT  tech.TechnicianID, tech.ForeName & " " & tech.LastName As FullName, tss.SkillID, ski.SkillDescription FROM (tblTechnicians tech INNER JOIN tblTechNicianSkillSet tss ON tss.TechnicianID = tech.TechnicianID) INNER JOIN tblSkills ski ON ski.SkillID = tss.SkillID

To get the results of trainings (To make it easier, I assumed that for every skill was a single training, or none. When non-approved trainings are repeated, the easiest way is to create a saved query which returns the result of latest training, and to use this query instead of trainings table.):

SELECT  tech.TechnicianID, tech.ForeName & " " & tech.LastName As FullName, tss.SkillID, ski.SkillDescription, ttr.TrainingDate, ttr.TrainingApproved FROM ((tblTechnicians tech INNER JOIN tblTechNicianSkillSet tss ON tss.TechnicianID = tech.TechnicianID) INNER JOIN tblSkills ski ON ski.SkillID = tss.SkillID) LEFT JOIN tblTechnicianTrainings ttr ON ttr.SkillSetID = tss.SkillSetID

Adding the WHERE clause to final query allows to get:
With 'WHERE ttr.TrainingApproved = TRUE', or 'WHERE ttr.TrainingAproved = 1', or whatever you have for approved- a list of all approved trainings for all technicians;
With 'WHERE ttr.TrainingApproved = FALSE', or 'WHERE ttr.TrainingAproved = 0', or whatever you have for non-approved- a list of all non-approved trainings for all technicians;
With 'WHERE Nz(ttr.TrainingApproved, FALSE) = FALSE', or 'WHERE Nz(ttr.TrainingAproved,0) = 0', or whatever you have for non-approved- a list of all non-approved or missing trainings for all technicians;
With 'WHERE ttr.TrainingApproved Is Null' - a list of all missing trainings for all technicians.

[i-Zapp]

Thanks for the replies, but it seems a simple answer it's not! Although I'm pretty good at Excel VBA, I'm just getting started in Access, so my understanding of WHERE and how to utilize the code you've shown is pretty weak.

Is there a chance I can get you to modify the simple database I've attached? (it's saved in an older format to maximize compatibility, but i'm running Access 365)

[June7]

All the code shown is SQL. You use it in Access query builder.

Advise not to use space in naming convention of objects. Better would be TrainingMatrix or Training_Matrix. Should save ID keys as foreign key in matrix, not descriptive text of skills and technicians.

To get a list of training technicians have not taken, need to start with a dataset of all possible technician/training pairs. You didn't show a table for technicians but assuming there is one, that dataset can be created with a Cartesian query. There is no JOIN clause so every record of each table associates with every record of other table.

SELECT Technicians.TechnicianID, Skills.SkillsID FROM Technicians, Skills;

Now do a compound join of that dataset to training matrix table. Include Technicians and Skills tables to pull related descriptive info.

SELECT TrainingMatrix.*, Query1.*, Skills.Field1, Technicians.TechName
FROM ((Query1 INNER JOIN Technicians ON Query1.TechID = Technicians.TechID) INNER JOIN Skills ON Query1.SkillID = Skills.SkillID) LEFT JOIN TrainingMatrix ON (Query1.SkillID = TrainingMatrix.SkillsID_FK) AND (Query1.TechID = TrainingMatrix.TechID_FK);



Also advise against building lookup fields in table.

[mike60smart]

Hi
As Avril has already said you need tables as follows:-

tblTechnicians: TechnicianID, Forename, LastName, ...;
tblSkills: SkillID, SkillDescription, ...;
tblTechicianSkillSet: SkillSetID, TechnicianID, SkillID;
tblTechnicianTrainings: TechnicianTrainingID, SkillSetID, TrainingDate, TrainingApproved (TrainingApproved may have e.g. values TRUE/FALSE, or 1/0).

If you can update your tables and then follow the steps indicated.

[ArviLaanemets]

As an update to my post# 3, at first for trainings was meant the table tblTechnicianSkillTrainings (tblTechnicianSkillTrainings: TechnicianSkillTraainingID, TechnicianID, SkillID, TrainingDate, TrainingApproved. When writing down querystrings, for some reason it looked a good idea to have tblTechnicianskillTrainings instead. It wasn't really, as this means the trainings are made for full skillsets instead for specific skills as it looks to be a case for you. When specific skills are trained, the last query must be something like

SELECT  tech.TechnicianID, tech.ForeName & " " & tech.LastName As FullName, tss.SkillID, ski.SkillDescription, ttr.TrainingDate, ttr.TrainingApproved FROM ((tblTechnicians tech INNER JOIN tblTechNicianSkillSet tss ON tss.TechnicianID = tech.TechnicianID) INNER JOIN tblSkills ski ON ski.SkillID = tss.SkillID) LEFT JOIN tblTechnicianSkillTrainings tst ON tst.SkillID = tss.SkillD AND tst.TechnicianID = tech.TechnicianID

[i-Zapp]

As an update to my post# 3, at first for trainings was meant the table tblTechnicianSkillTrainings (tblTechnicianSkillTrainings: TechnicianSkillTraainingID, TechnicianID, SkillID, TrainingDate, TrainingApproved. When writing down querystrings, for some reason it looked a good idea to have tblTechnicianskillTrainings instead. It wasn't really, as this means the trainings are made for full skillsets instead for specific skills as it looks to be a case for you. When specific skills are trained, the last query must be something like...

Hi Arvil - well it seems i have to jump into SQL programming with both feet, so this might get a little frustrating for you. I hope you can bear with me a bit!

First off, it seems you've envisioned an extra table for my database. I only have three: the technicians, the skills they need, and the skills they have ("training matrix"). Since i'm very new to SQL programming (I only now figured out where/how to modify/insert the code), it's difficult for me to interpret and apply your example to my actual database.

I've added the first couple lines of SQL code, but adding any additional code results in an error. I've attached an updated file that I'd be hugely grateful for you to edit so i can see how this works.

[ArviLaanemets]

From tblTrainingMatrix, I removed lookups from fields for training and technician. Lookup fields in tables are a source of various problems, and I think are added by MS for users who want to enter directly into tables instead using forms for this (which is really a bad practice, as much of Access functionality is lost doing so). The best practice is to avoid editing data directly in tables at all - this must be done in forms (I myself always hide the Navigation Pane from users). And in forms is, where you can have combos to read/store foreign key ID's to tables, and display meaningful info (like technician names or skill descriptions or whatever) instead.
I renamed all fields named as 'ID' with names indicating the specific table. E.g. let's imagine in some query you want both ID's for technicians and for skills to be returned. You cant have 2 separate fields with same name in query, so you have to rename them there. Why do you want this trouble every time when you design some query, instead avoiding this entirely?
I removed the field 'Approved' from tblTrainingsMatrix, as having ApprovedDate filled is enough to decide, that training was approved. In case you want to keep it, consider having it as smallint field (values like 1 for approved, and 0 for unapproved), because in case somewhere in future you want to use some other DB system (e.g. SQL Server) as your Backend DB, you don't need to update those fields (in different systems boolean fields may be defined differently).

As example, I designed 2 queries which display trainings technicians were having. One contains all ID's involved, the other only the linked info. In case the training was not approved, the DateApproved is empty. And your 'technician query' started to work properly without any changes there, after table lookups were removed.

About the extra table - you need it, when you want to define, which skills are totally needed for every technician, and are there some skills, the technician hasn't trained at all. The tblSkills has the list of all skills, but not the list of all skills a specific technician must have (unless all technicians must have exactly same skillset - but your starting post indicated differently).

[June7]

Did you read post #5?

[i-Zapp]

From tblTrainingMatrix, I removed lookups from fields for training and technician. Lookup fields in tables are a source of various problems, and I think are added by MS for users who want to enter directly into tables instead using forms for this (which is really a bad practice, as much of Access functionality is lost doing so). The best practice is to avoid editing data directly in tables at all - this must be done in forms (I myself always hide the Navigation Pane from users). And in forms is, where you can have combos to read/store foreign key ID's to tables, and display meaningful info (like technician names or skill descriptions or whatever) instead.

thanks. since you did not create a form as an example, i will need to figure out how to populate a combo in a form

I renamed all fields named as 'ID' with names indicating the specific table. E.g. let's imagine in some query you want both ID's for technicians and for skills to be returned. You cant have 2 separate fields with same name in query, so you have to rename them there. Why do you want this trouble every time when you design some query, instead avoiding this entirely?

makes senses, thanks!

I removed the field 'Approved' from tblTrainingsMatrix, as having ApprovedDate filled is enough to decide, that training was approved. In case you want to keep it, consider having it as smallint field (values like 1 for approved, and 0 for unapproved), because in case somewhere in future you want to use some other DB system (e.g. SQL Server) as your Backend DB, you don't need to update those fields (in different systems boolean fields may be defined differently).

i thought it might simplify and help, but i agree that it's redundant. thanks

As example, I designed 2 queries which display trainings technicians were having. One contains all ID's involved, the other only the linked info. In case the training was not approved, the DateApproved is empty. And your 'technician query' started to work properly without any changes there, after table lookups were removed.

i see three queries in your db, that each return 3 records. but each record is associated with training. my objective is to also show what training the technician does and does NOT have (as shown in my original post).

About the extra table - you need it, when you want to define, which skills are totally needed for every technician, and are there some skills, the technician hasn't trained at all. The tblSkills has the list of all skills, but not the list of all skills a specific technician must have (unless all technicians must have exactly same skillset - but your starting post indicated differently).

i'm struggling with understanding this. and you did not add this extra table into your db. again, i apologize for being thick. i'm very much a novice Access programmer or even db builder.

[mike60smart]

Hi
I think you need a simple form as shown in the attached screenshot, which allows you to select a Technician in a Main Form
Then in a Subform enter all the Skills required together with the dates approved.

It is then easy to produce the required report as shown.

[madpiet]

simple training matrix 2 (1).mdbSkills he's not yet been trained on...

You already have this:

Technician(TechID,...)
Skill(SkillID,...)
Training(TechID, SkillID, TrainDate)

all possible combinations of TechID,SkillID is done like this:

AllSkills:
SELECT TechID, SkillID
FROM Technician CROSS JOIN Skills

then to get the skills the technician is missing, you just subtract. (AllSkills MINUS Training), so

SELECT A.TechID, A.SkillID
FROM AllSkills a LEFT JOIN Training t ON (a.TechID = t.TechID AND a.SkillID = t.SkillID)
WHERE t.EarnDate IS NULL

[i-Zapp]

Thank you everyone for the assistance. I am still trying to make this work...

[i-Zapp]

I am trying to do this using the SQL suggestions provided, and have the following results:

Using pbaldy's suggestion, I'm able to get what LOOKS correct (RH image), however there's several records missing (3 techs x 6 skills = 18 possible training records, not just 8)

SELECT tblTrainingMatrix.TechnicianID, tblSkills.skill, tblTrainingMatrix.Approved, tblTrainingMatrix.approvedDate 
FROM tblSkills LEFT JOIN tblTrainingMatrix ON tblSkills.skillID = tblTrainingMatrix.skillID;

And using madpiet's suggestion (subtract the training matrix from the combination/cross-join), I get all 18 variants (LH image), but no indication of the correct approvals.

SELECT qryCrossJoinTechsSkills.TechID, qryCrossJoinTechsSkills.skillID, tblTrainingMatrix.Approved
FROM qryCrossJoinTechsSkills LEFT JOIN tblTrainingMatrix ON (qryCrossJoinTechsSkills.SkillID =tblTrainingMatrix.SkillID) 
AND (qryCrossJoinTechsSkills.TechID =tblTrainingMatrix.TechnicianID)
WHERE tblTrainingMatrix.Approved IS NULL;