Select records that meet criteria, then evaluate results.

[Alhassani]

Good afternoon,



I am trying to write a SQL statement that will evaluate column "DCN" and return only those records that say "5". Then, this statement (or possibly a second statement or embeded statement) will look at the "TFi_RM" column and perform an IIF. If the row is equal to 1, then return "MVP". If the row is equal to 0.9965, then return "All-Star". Otherwise, if neither criteria is met either return "No" or just don't select the column. I used the design builder for SQL with the expression builder and came up with the SQL statement below.

The purpose is to identify employees who received either no errors (MVP) or only had a single error (All-Star) during the week. It is a recognition program that we run internally.

When I run the statement, the error message says "You tried to execute a query that does not include the specified expression 'TFi_RM' as part of an aggregate function."

SELECT Weekly_Scores_RM.RMs_X, Weekly_Scores_RM.Week_x, Weekly_Scores_RM.DCN_x, Weekly_Scores_RM.TFi_RM
FROM Weekly_Scores_RM
GROUP BY Weekly_Scores_RM.RMs_X, Weekly_Scores_RM.Week_x, Weekly_Scores_RM.DCN_x
HAVING (((Weekly_Scores_RM.DCN_x)=5) AND ((Weekly_Scores_RM.TFi_RM)=IIf([TFi_RM]=1,"MVP","No"))) OR (((Weekly_Scores_RM.TFi_RM)=IIf([TFi_RM]=0.9965,"All-Star","No")));

[June7]

There are no expressions in the query that summarize (aggregate) data. Don't use GROUP BY.

Maybe:

SELECT Weekly_Scores_RM.RMs_X, Weekly_Scores_RM.Week_x, Weekly_Scores_RM.DCN_x, Weekly_Scores_RM.TFi_RM,
IIf(Weekly_Scores_RM.TFi_RM=1, "MVP", IIf(Weekly_Scores_RM.TFi_RM=0.9965, "All-Star", Null)) AS Rating
FROM Weekly_Scores_RM
WHERE Weekly_Scores_RM.DCN_x=5;

[Alhassani]

Oh wow, that perfectly fixed it. Thank you.

EDIT: It works for the MVP part, but not the All-Star. Is it not able to filter down like that?

EDIT Again: forgot that the percentage is rounded. Changed it to ">0.9964" and it worked fine.

[Alhassani]

So, now I'm trying to take the returned values (MVP & All-Star) and count how many there were per week. I'm not sure if I should be using COUNT or SUM here. Working with SUM gets a data type mismatch error, but then again so does count.

SELECT Sum(Stars_Qry_RM.Rating) AS MVP, Stars_Qry_RM.Week_x
FROM Stars_Qry_RM
WHERE (((Stars_Qry_RM.Rating)="MVP"))
GROUP BY Stars_Qry_RM.Week_x;

[June7]

Use Count. Can't Sum text.

[Alhassani]

So, I half-way figured this out. Problem is that the data is not being displayed as intended. I had a query working that counted each entry correctly, but it was not in a useable format and would have required another (redundant) query to display correctly.

Here is the code:

SELECT Stars_Qry_RM.Week_x, Count([Stars_Qry_RM].[Rating]="MVP") AS MVP, Count([Stars_Qry_RM].[Rating]="All-Star") AS [All-Star]
FROM Stars_Qry_RM
GROUP BY Stars_Qry_RM.Week_x;

It doesn't return how many entries resulted in MVP for the week, it returns the total amount of entries. For example, there are 9 MVPs and 1 All-Star for week 2714. This query returns that there were 10 MVPs in week 2714 instead of the correct 9. The All-Star expression returns the same numbers as the MVP expression instead of the correct 1 All-Star.

[June7]

Try IIf().

Sum(IIf([Stars_Qry_RM].[Rating]="MVP", 1, 0))