ranking query question

[peciam]

Hi,

I want to know how I can update a rank field with the results of my ranking query.

Here's my query statement and it works great when displaying the query.

ranking: (select count(*) from Tab_tc where [percent_L3_L4] >[emp1].[percent_L3_L4]+1

 

[jrbarnett]

The storing of calculated data (such as your rank) breaks the relational database normalisation rules, because of the fact that such data can easily get outdated.

Having said that, there are a number of reasons for storing calculated data, notably for auditing and historical purposes.

Your rank is currently a calculated field, because it is based on the value of an expression. If you have need to store this, create an empty field in one of the tables of the correct data type then run the following SQL:

UPDATE table SET rank = (select count(*) from Tab_tc where [percent_L3_L4] >[emp1].[percent_L3_L4]+1

replacing "table" and "rank" with the name of your table and query respectively.

John