Tables, Froms, and SQL - Oh My!

[InventoryQueryGuy]

I have three tables: Prod_Serv data, Skill_Comp data, Thera_data.
Within each table are four fields: ID, Name, Product / Service OR Skill /
Competency OR Therapeutic Area, Rating (number).
I am trying to design a query where the user chooses Product / Service, Sill
/ Competency and Therapeutic Area each from drop down menus on a form. The
user then chooses a rating for each where only results with a rating higher
than that chosen are returned. The user will then apply a weighting (of
importance by multiplying this by the rating) to each of the three. The
returned result is a name and should be ordered according to the cumulative
ranking.

Here is my current SQL:
SELECT [Employee List].Name, [Employee List].Title, [Employee
List].Department, [Employee List].[Office Location],
Sum((Prod_Serv.Rating*[Forms]![ProSer_SkiCom_Thera]![ProdServWeight])+(Skill_Comp.Rating*[Forms]![ProSer_SkiCom_Thera]![SkillCompWeight])+(Thera_data.Rating*[Forms]![ProSer_SkiCom_Thera]![TheraWeight])) AS Rank
FROM (([Employee List] LEFT JOIN [Prod_Serv data] ON [Employee
List].ID=[Prod_Serv data].Name) LEFT JOIN [Skill_Comp data] ON [Employee
List].ID=[Skill_Comp data].Name) LEFT JOIN [Thera_data] ON [Employee
List].ID=[Thera_data].Name
WHERE ((([Prod_Serv
data].Rating)>=[Forms]![ProSer_SkiCom_Thera]![ProdServRating]) AND
(([Prod_Serv data].[Product /
Service])=[Forms]![ProSer_SkiCom_Thera]![Prod_Serv]) AND (([Skill_Comp
data].Rating)>=[Forms]![ProSer_SkiCom_Thera]![SkillCompRating]) AND
(([Skill_Comp data].[Skill /
Competency])=[Forms]![ProSer_SkiCom_Thera]![SkillComp]) AND
((Thera_data.[Therapeutic Area])=[Forms]![ProSer_SkiCom_Thera]![Thera]) AND
((Thera_data.Rating)>=[Forms]![ProSer_SkiCom_Thera]![TherapyRating]));
GROUP BY [Employee List].Name, [Employee List].Title, [Employee
List].Department, [Employee List].[Office Location]
ORDER BY
Sum((Prod_Serv.Rating*[Forms]![ProSer_SkiCom_Thera]![ProdServWeight])+(Skill_Comp.Rating*[Forms]![ProSer_SkiCom_Thera]![SkillCompWeight])+(Thera_data.Rating*[Forms]![ProSer_SkiCom_Thera]![TheraWeight])) DESC;

It looks solid, at least I think so. I keep getting an error: "The LEVEL
clause includes a reserved word or argument that is misspelled or missing, or
the punctuation is incorrect."

Help?! Please!!

 

[InventoryQueryGuy]

Turns out I had a semicolon after my WHERE statement.

Sorry for the inconvenience.

 

[John Spencer MVP]

Note that you have a semi-colon just before the GROUP BY clause. That will
cause an error. Other than that check all your parentheses to make sure they
are balanced.

SELECT [Employee List].Name
, [Employee List].Title
, [Employee List].Department
, [Employee List].[Office Location]
, Sum((Prod_Serv.Rating*[Forms]![ProSer_SkiCom_Thera]![ProdServWeight])
+(Skill_Comp.Rating*[Forms]![ProSer_SkiCom_Thera]![SkillCompWeight])+
(Thera_data.Rating*[Forms]![ProSer_SkiCom_Thera]![TheraWeight])) AS Rank
FROM (([Employee List] LEFT JOIN [Prod_Serv data]
ON [Employee List].ID=[Prod_Serv data].Name)
LEFT JOIN [Skill_Comp data] ON [Employee List].ID=[Skill_Comp data].Name)
LEFT JOIN [Thera_data] ON [Employee List].ID=[Thera_data].Name

WHERE [Prod_Serv data].Rating>= [Forms]![ProSer_SkiCom_Thera]![ProdServRating]
AND

[Prod_Serv data].[Product / Service]=
[Forms]![ProSer_SkiCom_Thera]![Prod_Serv] AND

[Skill_Comp data].Rating>= [Forms]![ProSer_SkiCom_Thera]![SkillCompRating] AND

[Skill_Comp data].[Skill / Competency]=
[Forms]![ProSer_SkiCom_Thera]![SkillComp] AND

Thera_data.[Therapeutic Area]=[Forms]![ProSer_SkiCom_Thera]![Thera] AND

Thera_data.Rating>=[Forms]![ProSer_SkiCom_Thera]![TherapyRating]

GROUP BY [Employee List].Name, [Employee List].Title
, [Employee List].Department
, [Employee List].[Office Location]

ORDER BY
Sum((Prod_Serv.Rating*[Forms]![ProSer_SkiCom_Thera]![ProdServWeight])+
(Skill_Comp.Rating*[Forms]![ProSer_SkiCom_Thera]![SkillCompWeight])+
(Thera_data.Rating*[Forms]![ProSer_SkiCom_Thera]![TheraWeight])) DESC;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have three tables: Prod_Serv data, Skill_Comp data, Thera_data.
Within each table are four fields: ID, Name, Product / Service OR Skill /
Competency OR Therapeutic Area, Rating (number).
I am trying to design a query where the user chooses Product / Service, Sill
/ Competency and Therapeutic Area each from drop down menus on a form. The
user then chooses a rating for each where only results with a rating higher
than that chosen are returned. The user will then apply a weighting (of
importance by multiplying this by the rating) to each of the three. The
returned result is a name and should be ordered according to the cumulative
ranking.

Here is my current SQL:
SELECT [Employee List].Name, [Employee List].Title, [Employee
List].Department, [Employee List].[Office Location],
Sum((Prod_Serv.Rating*[Forms]![ProSer_SkiCom_Thera]![ProdServWeight])+(Skill_Comp.Rating*[Forms]![ProSer_SkiCom_Thera]![SkillCompWeight])+(Thera_data.Rating*[Forms]![ProSer_SkiCom_Thera]![TheraWeight])) AS Rank
FROM (([Employee List] LEFT JOIN [Prod_Serv data] ON [Employee
List].ID=[Prod_Serv data].Name) LEFT JOIN [Skill_Comp data] ON [Employee
List].ID=[Skill_Comp data].Name) LEFT JOIN [Thera_data] ON [Employee
List].ID=[Thera_data].Name
WHERE ((([Prod_Serv
data].Rating)>=[Forms]![ProSer_SkiCom_Thera]![ProdServRating]) AND
(([Prod_Serv data].[Product /
Service])=[Forms]![ProSer_SkiCom_Thera]![Prod_Serv]) AND (([Skill_Comp
data].Rating)>=[Forms]![ProSer_SkiCom_Thera]![SkillCompRating]) AND
(([Skill_Comp data].[Skill /
Competency])=[Forms]![ProSer_SkiCom_Thera]![SkillComp]) AND
((Thera_data.[Therapeutic Area])=[Forms]![ProSer_SkiCom_Thera]![Thera]) AND
((Thera_data.Rating)>=[Forms]![ProSer_SkiCom_Thera]![TherapyRating]));
GROUP BY [Employee List].Name, [Employee List].Title, [Employee
List].Department, [Employee List].[Office Location]
ORDER BY
Sum((Prod_Serv.Rating*[Forms]![ProSer_SkiCom_Thera]![ProdServWeight])+(Skill_Comp.Rating*[Forms]![ProSer_SkiCom_Thera]![SkillCompWeight])+(Thera_data.Rating*[Forms]![ProSer_SkiCom_Thera]![TheraWeight])) DESC;

It looks solid, at least I think so. I keep getting an error: "The LEVEL
clause includes a reserved word or argument that is misspelled or missing, or
the punctuation is incorrect."

Help?! Please!!