Weighted Average Skills Analysis - First Build

[mach_oddity]

I'm brand new here, so please go easy on me. I'm a long time excel user that is trying to use access to accomplish a task with less room for error and a more user friendly "front-end". I get the basics of DBMS but I'm struggling to come up with even the most basic table structure to get what I want done. Hopefully you wonderful forum peeps can help out a noob. Here goes...



I have a questionnaire that I ask potential employees to fill out when they apply. Of course I'm collecting names, addresses, emails, etc. (currently stored in a table - "ApplicantT") but I am also asking for their experience level in about 25 different areas (these areas are currently listed in a table - "SkillsT"). The goal is to use this data to determine where in our organization a person may fit in among various positions (currently listed in a table - "JobT"). All of the various jobs utilize the listed skills in varying ways and that's where the confusing part comes in. I want to assign an "importance" (0-10) to each skill as it is used or not used for each individual "job" and then use the importance, combined with each applicant's experience level (1-4) to determine a total eligibility score. As previously mentioned, I'm an excel guy... so I know in excel it looks like this for each row containing an applicant:

=SUMPRODUCT([range_of_experiences],[range_of_importances])/SUM([range_of_importances])

However, when it comes to access, I have no clue where to enter importances for each job, nor where or how to apply them into a final weighted average. The final solution here should be the ability to query a job and get the 10 (or so) most qualified candidates.

Side note: after reading this I feel like I'm asking a bunch of strangers to write the whole doggone file for me. Any help would be appreciated, and feel free to let me know if I'm asking to get the world for a song. Thanks in advance.

[CJ_London]

Excel users struggle to adapt to access (or any database) which works in a completely different way. From your description it is not possible to determine whether or not you have 'got it'

Suggest upload a copy of your relationships showing the field names so we can get a feel for this. At the moment it sounds like you need a table between the applicant and skill tables and another between jobs and skills.

It will also be useful for understanding if you can provide some example data to illustrate the requirement. Suggest one applicant and skills experience, two jobs with the necessary skills required and the expected outcome in terms of which job the applicant is more suited to - if you have this in excel, so much the better.

[mach_oddity]

Thanks for the quick and thorough reply!!

Here's my tables thus far...

And here's a quick sampling of data...
- Importance of each skill for the 2 positions:


- Weighted Average to determine the best candidate:

[CJ_London]

OK - you are hiding C28:Y28 What is there?.And what's in B5:B27? is that the importance values? or are they in C28:Y28?

[mach_oddity]

Sorry... meant to clarify and got distracted. C28:Y28 is the exact same data as B5:B27... it's a VLOOKUP from the middle image depending on the "named position" in the final image. In other words, when the user drops down cell B2 in the last image, those numbers update to be used as the weight values in the experience score.

[CJ_London]

OK - so to confirm your calculation - just take the first 4 skills

For adam you are multiplying (3 x 6)+(3 x 0) +(2*1)+(3*1)=23 and dividing by 8 which gives you an experience score of 2.875

[mach_oddity]

Exactly. The idea is to lend more weight to the skills that are more "in demand".... so for the first 4, Adam gets more credit for the first one than the last two, and no credit for the second one

[CJ_London]

First - you need a list of all skillsets and using a left join to each of the two skill rating tables - and those rating tables need to be filtered for applicant and job respectively. Left joins will return all skills records and only those records in applicants and jobs where they exist

Evolving a query build your initial query might look something like this - at each change you can check you are getting the expected results

SELECT SkillDesc, ApplicatantID, JobID, AppskillT.exprating, JobSkillT.exprating
FROM (SkillT LEFT JOIN AppskillT ON SkillT.SkillID=AppskillT.SkillID) LEFT JOIN JobSkillT ON SkillT.SkillID=JobskillT.SkillID
WHERE (AppskillT.ApplicantID=[Enter ApplicantID] OR AppskillT.ApplicantID is null) AND (jobskillT.jobID=[Enter jobID] OR jobskillT.jobID is null)

However you only need to know the jobskill rating to sum, and you also need to calculate the product, so modify the query to. I've highlighted in red where the changes are

SELECT SkillDesc, ApplicatantID, JobID,  JobSkillT.exprating, AppskillT.exprating * JobSkillT.exprating AS Product
FROM (SkillT LEFT JOIN AppskillT ON SkillT.SkillID=AppskillT.SkillID) LEFT JOIN JobSkillT ON SkillT.SkillID=JobskillT.SkillID
WHERE (AppskillT.ApplicantID=[Enter ApplicantID] OR AppskillT.ApplicantID is null) AND (jobskillT.jobID=[Enter jobID] OR jobskillT.jobID is null)

now you need to group and sum, so the query becomes this - note that skilldesc is also removed as it is no longer relevant

SELECT ApplicatantID, JobID,  sum(JobSkillT.exprating) as Weighting, sum(AppskillT.exprating * JobSkillT.exprating) AS Product
FROM (SkillT LEFT JOIN AppskillT ON SkillT.SkillID=AppskillT.SkillID) LEFT JOIN JobSkillT ON SkillT.SkillID=JobskillT.SkillID
WHERE (AppskillT.ApplicantID=[Enter ApplicantID] OR AppskillT.ApplicantID is null) AND (jobskillT.jobID=[Enter jobID] OR jobskillT.jobID is null)
GROUP BY ApplicatantID, JobID

finally you only want the weighted average so modify the query to

SELECT ApplicatantID, JobID, sum(AppskillT.exprating * JobSkillT.exprating)/sum(JobSkillT.exprating) AS ExperienceScore
FROM (SkillT LEFT JOIN AppskillT ON SkillT.SkillID=AppskillT.SkillID) LEFT JOIN JobSkillT ON SkillT.SkillID=JobskillT.SkillID
WHERE (AppskillT.ApplicantID=[Enter ApplicantID] OR AppskillT.ApplicantID is null) AND (jobskillT.jobID=[Enter jobID] OR jobskillT.jobID is null)
GROUP BY ApplicatantID, JobID

Hope taking you through the steps helps you understand the requirements. Note this is 'aircode' so there may be the odd error in there

[mach_oddity]

Thank you for the thorough reply. I'm (so far) only questioning one thing.... it seems like you only are returning ApplicantID.... as previously stated I'm pretty green, so forgive me if this is a 'dumb' question, but should I also SELECT ApplicantName so as to generate a list of human readable results?

[CJ_London]

I would get the query done, prove it is producing the correct result then link it to the applicant table to get the name

It really depends on what your form is going to look like - one applicant, which job most suited? or one job, which applicant most suited?

[mach_oddity]

The hope is "one job, which applicant most suited"... or better yet, list of all applicants in order of best suited for selected job.

[CJ_London]

as I said - depends on what your form looks like and how it is intended to work - you might include it in the recordsource - which could be sorted, might use a dlookup in a control and sort the form. You can limit the number of records by using the TOP predicate in combination with sorting - so the last query would start with

SELECT Top 10 ApplicatantID......

and end with a new line

..... ORDER BY sum(AppskillT.exprating * JobSkillT.exprating)/sum(JobSkillT.exprating) Desc

Your question was about how to do the equivalent of sumproduct in a database for which I have shown you how this is done.

One of the most significant differences between excel and databases is excel combines data storage and presentation in one view whilst databases store data in tables and use forms and reports for presentation. Another is excel typically presents 'short and wide' whilst databases present 'tall and thin'.