You tried to execute a query that does not include the specified expression..

[rjgriffin46]

A new query I am building, after entering an expression, gave the " You tried to execute a query that does not include the specified expression.." error. The new query is based on and uses expressions from another query. The error message listed the expressions from the query upon which this query was based. Copying in all the fields referenced in the error message did not help.



Here is the SQL code, the offending expression in red:

SELECT [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber, Count([Test Grade Results].[Test Number]) AS [CountOfTest Number], Avg([Test Grade Results].[41Grade]) AS AvgOf41Grade, Sum([Test Grade Results].Wghtd41) AS SumOfWghtd41, [GPA]=Sum([Test Grade Results]![Wghtd41])/[Test Grade Results]![CuumWeightDivisor] AS Expr1

FROM [Test Grade Results] INNER JOIN [Test Parameters] ON [Test Grade Results].[Test Number] = [Test Parameters].TestNumber

GROUP BY [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber;

Wghtd41 is the expession that is included in the error message.

I've read several other posts on this subject and it seems including the expression is needed, but I think I have done that.

Help is appreciated.

Thanks

[June7]

The [GPA]= makes no sense in an aggregate query. This will evaluate to True or False Boolean.

Also can't have a Sum() calculated with an individual record value. Could:

Sum(Wghtd41/CuumWeightDivisor) AS Expr1

or

Sum(Wghtd41)/Sum(CuumWeightDivisor) AS Expr1

[rjgriffin46]

This got rid of the error. Thank you very much. Now if only I can get the formula correct in the expression....
Here is the revised query that eliminated the error:

SELECT [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber, Count([Test Grade Results].[Test Number]) AS [CountOfTest Number], Avg([Test Grade Results].[41Grade]) AS AvgOf41Grade, Sum([Test Grade Results].Wghtd41) AS SumOfWghtd41, Sum([Test Grade Results]![Wghtd41]*[Test Grade Results]![CuumWeightDivisor]) AS Expr1
FROM [Test Grade Results] INNER JOIN [Test Parameters] ON [Test Grade Results].[Test Number] = [Test Parameters].TestNumber
GROUP BY [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber;

[June7]

What is the calculation you are trying to do?

[rjgriffin46]

What is the calculation you are trying to do?

I am trying to calculate a weighted Grade Point Average where the weights for each test are in a Test Parameters table and the results for each student for each test are in a Test Results table.

I did resolve the initial problem thanks to a prior response. I am now working on getting the correct calculation in a Totals/Group-By query. I believe, but have not yet tried, that the average of the weighted test scores divided by the weighted average maximum possible score (calculated from the weight in Test Parameters for each test) multiplied by the maximum possible GPA will give the correct result.

If it doesn't give the correct result or if I encounter other problems I will be back to the forum!

Thanks for your interest.

[rjgriffin46]

With your 2nd example I was able to come up with an expression that appears to be working:

(Avg([Test Grade Results]![Wghtd41])/Avg([Test Grade Results]![MaxPossibleWeighted41]))*4 AS Expr1

Note the change of the field doing the dividing (dividend? - forget my grammer school math terms). But your real help was just in getting formatting of the expression correct.

This was a second major breakthrough in less than 1 week.

Thanks so much!

The [GPA]= makes no sense in an aggregate query. This will evaluate to True or False Boolean.

Also can't have a Sum() calculated with an individual record value. Could:

Sum(Wghtd41/CuumWeightDivisor) AS Expr1

or

Sum(Wghtd41)/Sum(CuumWeightDivisor) AS Expr1