Max function in a calculated query

[world33]

Hello,

I have a query problem and I have no idea how to solve it.

In the query there are 4 fields:

1) ID 2) PageRank 3) PageRankNormalized 4) PageRankValue


1) ID is the Primary Key

2) In the PageRank field there are values from 1 to 10

3) In the PageRankNormalized there is an expression PageRankNormalized: (3^[PageRank])

4) In the PageRankValue there is another expression PageRankValue: ((3^[PageRank])*100)/ Max ( [PageRankNormalized] )


Now when I run the query I get the following error message:

You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function. (Error 3122)

What I am trying to do is to calculate the PageRankValue (4) through an expression that divide ((3^[PageRank])*100) by the maximum value shown in the calculated field PageRankNormalized (3)

------------------SQL------------------------

SELECT [UNIVERSITIES by name].ID, [UNIVERSITIES by name].PageRank, (3^[PageRank]) AS PageRankNormalized, ((3^[PageRank])*100)/Max([PageRankNormalized]) AS PageRankValue
FROM [UNIVERSITIES by name];

---------------------------------------------


I am stucked and I am not sure how to use this Max function in a calculated query.

Thanks for your help !!

FF

 

[llkhoutx]

You have to compute the Max first, you can't do it "on the fly" the way you are.