Applying a weighting to results.

[InventoryQueryGuy]

Is there a way in which I can weight my results. I would like a query based
on input from a form. In this form there is a choice of 1. Product / Service
2. Skill / Competency 3. Therapeutic Area. Each of these has a rating and
separate queries to pull a result above the rating value and supply a Name
output. I have created combo boxes beside each of these for a weighting in
order to query all three at once and then return the ranked results as a
Name. I think I may need a query inside a query in order to perform the
calculation that is, multiplying the rating by the weighting. Does anyone
have any ideas as to how to approach this? There is a separate table for each
of the 3 choices and I can't change this. I have attached my SQL, but I think
I have a critical problem with the setup.

SELECT [Employee List].Name, [Employee List].Title, [Employee
List].Department, [Employee List].[Office Location]
FROM (([Employee List] INNER JOIN [Prod_Serv data] ON [Employee List].ID =
[Prod_Serv data].Name) INNER JOIN [Skill_Comp data] ON ([Prod_Serv data].ID =
[Skill_Comp data].ID) AND ([Employee List].ID = [Skill_Comp data].Name))
INNER JOIN [Thera data] ON ([Prod_Serv data].ID = [Thera data].ID) AND
([Employee List].ID = [Thera data].Name)
WHERE ((([Prod_Serv data].[Product /
Service])=[Forms]![ProSer_SkiCom_Thera]![Prod_Serv]) AND (([Prod_Serv
data].Rating)>=[Forms]![ProSer_SkiCom_Thera]![ProdServRating]) AND (([Thera
data].[Therapeutic Area])=[Forms]![ProSer_SkiCom_Thera]![Thera]) AND (([Thera
data].Rating)>=[Forms]![ProSer_SkiCom_Thera]![TherapyRating]) AND
(([Skill_Comp data].[Skill /
Competency])=[Forms]![ProSer_SkiCom_Thera]![SkillComp]) AND (([Skill_Comp
data].Rating)>=[Forms]![ProSer_SkiCom_Thera]![SkillCompRating]));

 

[KARL DEWEY]

I do not see any fields in your query that would contain data that weighting
would be applied.

I see [Skill_Comp data].[Skill / Competency] and [Skill_Comp data].Rating
that you are comparing to data entered in a form.

Did you build a weighting table for each skill that would be mutiplied by
rating?
Such as --
Typing - .25
Stocking - 1.5
Xyz - 1

Post some sample data from the tables.

 

[InventoryQueryGuy]

This is how my tables look:

Prod_Serv data
Name Product / Service Rating
Joe management 5
Sam management 4
Ann management 7
Joe selling 4
Sam selling 8
Ann selling 6

Skill_Comp data
Name Skill / Competency Rating

Thera data
Name Therapeutic Rating


I need to take the rating, multiply it by the rating and return the ranked
result. Is this as simple as adding a new field to each table for weight and
have my query run a calculation in this field? If so, can you provide me with
a skeleton SQL for this?

Cheers.

 

[KARL DEWEY]

I need to take the rating, multiply it by the rating and return the ranked
You list three tables, each with a rating. So what is multiplied?

Are you saying that you want to multiply the three ratings to produce a rank?

You and I seem not to be using the same meaning of weighting. To me you
can have time weighted or weighting of importance. If an employee is rated
at 5 covering a three month period it will not have the same weight as a
rating of 5 for one year. The same for importance of skills such as
juggling would have a weighting factor of .35 versus knife throwing having
factor of 1.25 because of the latter being more important for the employee
job position.

 

[InventoryQueryGuy]

For example I might choose "selling" as a service, "presentation" as a skill,
and "flu" as a therapeutic. Then I would apply weighting in the order of
importance: Service 0.5, Skill 0.3, Therapeutic 0.2.
Prod_Serv table
And so the returned ranked result would give
1 Sam 7.9
2 Ann 4.7
3 Joe 4.1

 

[KARL DEWEY]

I recommend you use a single table with this structure --
Name -
PerfType - Service, Skill, Therapeutic
Activity - Selling, presentation, Flu
Rating -
Use a union query to consolidate the tables into one.

For now as you have the data I would do it this way --

SELECT [Employee List].Name, [Employee List].Title, [Employee
List].Department, [Employee List].[Office Location],
Sum(([Prod_Serv].[Rating]*0.5)+([Skill_Comp].[Rating]*0.3)+([Thera].[Rating]*0.2)) AS Rank
FROM (([Employee List] LEFT JOIN Prod_Serv ON [Employee List].ID =
Prod_Serv.ID) LEFT JOIN Skill_Comp ON [Employee List].ID = Skill_Comp.ID)
LEFT JOIN Thera ON [Employee List].ID = Thera.ID
GROUP BY [Employee List].Name, [Employee List].Title, [Employee
List].Department, [Employee List].[Office Location]
ORDER BY
Sum(([Prod_Serv].[Rating]*0.5)+([Skill_Comp].[Rating]*0.3)+([Thera].[Rating]*0.2)) DESC;

 

[InventoryQueryGuy]

Awesome! Thanks Karl.

I think I will have to consolidate the tables. My only concern is that I
won't be managing this data in the future and will have to ensure user
friendliness! Should be easy enough though with a few built in queries and
forms.

Cheers.