Solved Get a value from a Table based on multiple fields criteria

[mrk777]

I have a set of Skills in a Skills Table named "tbl_Skill_Groups". And I have Team Details in a table called "TMT_TeamDetails".
There is a Junction table created (tbl_TeamSkills_Junction") to track the scores of each Employee for each Skill.

Now I want to create a form where I would select the Team Member and I would like to show All the Skills that are available and the scores they got and if any of the skills scores are not there/updated, it should show as blank. I'm trying to build a query, but I got stuck to show them as per the requirements.

Following are the Table snapshots for your reference.

Skills Table:

Relationships maintained:

Junction Table:

Form that I would like to create with the query inbuilt:

I tried the following query which needs to be incorporated in the above-mentioned form:

Query builder: Current_Score: DLookUp("Current_Score","tbl_TeamSkills_Junction",("[tbl_Skill_Groups].[Group_Skill_DBID]= " & "[tbl_TeamSkills_Junction].[SkillsDBID]") And ("[Forms]![frm_Manager_Input_Form]![Emp_DB_ID]= " & "[tbl_TeamSkills_Junction].[EmpDBID]"))

The query is showing incorrect results, it is showing the scores for all the skills rather than showing only for the specific team member and updated skills.

Note: In brief:- I would like to show all the Skills available (from Skills Table) and get the scores of the selected Team Member if updated any (from the Junction table), in the form that I have created.

Please help!!

 

[Gasman]

Well if you want it only for one member, you have to use that data as criteria?
Why can't you use a join?
I believe you have your Forms and tblteamskills the incorrect way around as well

 

[arnelgp]

see this demo particularly the queries.
i numbered the query according to which one is created first.

 

[mrk777]

@arnelgp : Thank you so much! The demo file that you have sent is working as per the requirements and, is there any possibility of providing the capability of updating the content from the same form? Do I need to convert that into UPDATE Table?

Also, I tried using "Nz([Forms]![frm_Manager_Input_Form]![Combo4],[tbl_Skill_Groups].[Skill_Name])" as the criteria for the Skill Name field, to pick the exact field selected in the form and if that skill Combo Box field is blank, it needs to filter out all results. But it is showing the error and it is not populating the results. please help!!

 

[arnelgp]

Do I need to convert that into UPDATE Table?

if the training skills on your table are for All employees, then yes convert it to Update query.
you need to add Index (No duplicate) on EmpDBID + SkillsDBID.

 

[mrk777]

if the training skills on your table are for All employees, then yes convert it to Update query.
you need to add Index (No duplicate) on EmpDBID + SkillsDBID.

Hi @arnelgp: I tried converting the query to an update query, but it is throwing an error and not updating the records in the Form.

Also, I have tried a similar type of solution that you have provided to other criteria but I could not able to get it. Following is the scenario:

We do have Target scores for each Function and at each Grade level, which we need to bring besides the "Current Score" column for each team member.

For Example:- Emp1 from GL Accounting Team would be having a current score of 3 for Skill 1, and we do have a target in the GL Accounting team for Skill 1 with 4.

So we need to populate the data as follows:

Emp Name	Skill Name	Current Score	Target
Emp 1	Advanced VBA Macros	3	4

where the user must be able to edit only the Current Score but not the Targets.

I have attached the sample file which I'm trying with the previous logic you provided. Please help!!

Note: tbl_MSR_Targets is the table with the Target Scores in each function based on grade levels.

 

[arnelgp]

I created a temp table, Ztbl_EmpSkill.
when subform frm_1 opens, this table is updated/inserted with records.

see All tables in design view since i added/changed their Indexes so as
to make query qry_TempJunction, Updateable.

qry_TempJunction is being used in the subform.
open form1.

 

[mrk777]

Hi @arnelgp: I have found a small issue while adding the new team member records. When any new team member data has been added, it will automatically add to the Ztbl_EmpSkill table, but if there are any modifications made to the existing Team member details (like a change in the Function ID or change in the current role band) - it is doubling records in the Form because it is treating it as a new record and adding it up again.

Please help!!

 

[arnelgp]

Delete all the records from Ztbl_EmpSkill.
open form1 in design view and on the Subform Open event, replace the code with this one:

Private Sub Form_Open(Cancel As Integer)
With CurrentDb
    .QueryDefs("qry_appendToTemp").Execute
    .Execute "Update Ztbl_EmpSkill As A1 Inner Join tbl_TeamSkills_Junction As B1 " & _
            "On A1.EmpDBID =  B1.EmpDBID And A1.Group_Skill_DBID = B1.SkillsDBID " & _
            "Set A1.Trained= IIF(IsNull(A1.Trained), B1.Trained, A1.Trained), " & _
            "A1.Certified = IIF(IsNull(A1.Certified), B1.Certified, A1.Certified), " & _
            "A1.Current_Score = IIF(IsNull(A1.Current_Score), B1.Current_Score, A1.Current_Score);"
    .Execute "UPDATE Ztbl_EmpSkill AS A1 INNER JOIN TMT_TeamDetails AS B1 " & _
            "ON A1.EmpDBID = B1.EmpDBID " & _
            "SET A1.FUNCTION_ID = B1.FUNCTION_ID, A1.GRADE_LEVEL = B1.CURRENT_ROLE_BAND;"
End With
End Sub

 

[arnelgp]

sorry again, you need to Keep the record of the "lower grade" or the "other function_id" when you change this.
so i am keeping them all in Ztbl_EmpSkill (for tracking/history purpose).
what i did is add another query qry_SubForm and use it as RecordSource of the subform.
the content of qry_Subform is the "latest" data/info only from TMT_TeamDetails table.

 

[mrk777]

sorry again, you need to Keep the record of the "lower grade" or the "other function_id" when you change this.
so i am keeping them all in Ztbl_EmpSkill (for tracking/history purpose).
what i did is add another query qry_SubForm and use it as RecordSource of the subform.
the content of qry_Subform is the "latest" data/info only from TMT_TeamDetails table.

Apologies for the delayed response @arnelgp, I was in a place where I could not access my system, And it took me a bit longer to understand how you have connected the tables, and finally I was able to make it after observing your options that are modified in Indexes. It is working as per the requirements.

I was wondering, if we can add the following 2 options in the form (with the help of VBA/other queries) for the data which is being populated dynamically whenever the team member name is selected:

1. Current Skill Capability %: Sum(Current Scores)/Count(Targets which are other than 0) *4 --> which gives the percentage
2. Target Skill Capability %: Sum(Target Scores)/Count(Targets which are other than 0) *4 --> which gives the percentage

I'm very much familiar with excel in writing formulas, but when it comes to implementation in MS Access, I'm at a beginner level. Please help!!

 

[arnelgp]

i added calculation on when you select and employee on the combo.
please check/rectify if the calculation is incorrect.

 

[mrk777]

i added calculation on when you select and employee on the combo.
please check/rectify if the calculation is incorrect.

The calculation is going wrong somewhere. I have provided a brief snapshot of how the calculations need to be:

Skill Name	Current Score	Target Score
Skill 1	3	4
Skill 2	0	0
Skill 3	0	3
Skill 4	2	2
Skill 5	3	0

Now Calculation goes as below:
1. Current Skill Capability %: Sum(Current Scores)/Count(Targets which are other than 0) *4 --> which gives the percentage

Sum of Current Scores (3+0+2+3) = 8
Count of Target Scores (4,3,2,0) = 4
Capability % = 8/4*4 = 50%
Note: Skill 2 is ignored because it does not have any target or current score which means it is not legible for calculations

2. Target Skill Capability %: Sum(Target Scores)/Count(Targets which are other than 0) *4 --> which gives the percentage

Sum of Target Scores (4+3+2+0) = 9
Count of Target Scores (4,3,2,0) = 4
Capability % = 9/4*4 = 56%
Note: Skill 2 is ignored because it does not have any target or current score which means it is not legible for calculations

thank you!

 

[arnelgp]

why did you count 0 Target?

 

[mrk777]

why did you count 0 Target?

Because for Skill 3, we have the target, but the current score is 0 and for Skill 5, we have obtained the Current score even though we have no target, so I just considered it, I have mentioned them just to showcase the example.

 

[arnelgp]

check and test again.

 

[mrk777]

check and test again.

Thank you so much @arnelgp, calculations are so accurate as per the requirements.

One quick question: can I remove the option "Default value = 0" in the "Targets" table and leave it blank, if yes, will that affect the above-created calculations?

The reason why I have asked is that for some of the Departments, a few grade levels are not applicable (like IO5 and IO6 are not applicable for the FP&A department), but I would like to update the Targets for that department with IO5 & IO6 as blanks instead of 0.