创建排名顺序 [英] Creating a rank order

问题描述

我正在尝试运行追加查询并运行RND函数从大约2500中拉出150个分支。但是，我试图从1到150分配一个数值。如何分配这150个结果中的每一个排名从1到150？

解决方案

Hi Supermansteel。在Access查询中，Rnd在这里没有帮助，因为它在每次查询运行中只被评估一次。因此，尽管Rnd确实生成了不同的伪随机值，但在查询中使用它时，对于查询的每一行都将是相同的值，直到下次运行查询时，将选择另一个随机值但重复所有行。


生成包含150个随机选择值的表的最简单方法也许就是使用Excel来生成一个包含1到2500范围内随机数的表。这就是我自己做这种一次性选择的方式（例如，当做一个学生群体的随机样本时）。使用Excel的美妙之处在于每次打开工作表时数字都会有所不同（如果您通过按F9强制重新计算，或者进行任何其他会重新计算单元格的更改，您实际上可以在打开的工作表中再次更改它们。） br />

-Stewart

...但是有一些方法和手段可以强制Access为每一行评估随机数，而不仅仅是一次。灯泡亮了！


以下公共函数会这样做，如果您根据基础查询计算表达式并包含任何字段的名称：

展开 | 选择 | Wrap | 行号

展开 | 选择 | Wrap | 行号< /跨度>

I am trying to run an append query and running a RND function to pull 150 branches out of about 2500. However, I am trying to assign it a numerical value from 1 thru 150. How can I assign each of these 150 results a Rank from 1 to 150?

解决方案

Hi Supermansteel. In an Access query, Rnd won''t help you here, because it is evaluated just once in each query run. So although Rnd does indeed generate different pseudo-random values, when used in a query it will be the same value for every row of the query involved until the next time the query is run, when another random value will be chosen but repeated down all rows.

Perhaps the simplest way to generate a table of 150 randomly-chosen values is to use Excel to do so, generating a table of random numbers in the range 1 to 2500. It''s the way I do such one-off selections myself (when doing random samples of a student population for instance). The beauty of using Excel is that the numbers will be different each time you open the worksheet (and indeed you can change them again in the open worksheet if you force recalculation by pressing F9, or make any other change that will recalculate cells).

-Stewart

...but then there are ways and means to force Access to evaluate random for every row, not just once. Lightbulb came on!

The following public function does so, if you evaluate the expression and include the name of any field at all from your underlying query:

Expand|Select|Wrap|Line Numbers

Expand|Select|Wrap|Line Numbers

这篇关于创建排名顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！