问题描述
您好，在此先感谢您的帮助.
Hello and thanks in advance for any help.
我在Access中编写了一个小程序，该程序可以导入文本文件并将其放入表中.该表的布局大约有20个字段，每个字段代表一个不同的类别，该类别针对每个记录包含一个出于不同原因的数字(小时).我一直找不到任何可以使我朝正确的方向开始并需要帮助的东西.
I wrote a small program in Access that imports text files and places them into tables. The table is laid out with about 20 fields and each field represents a different category that contains a number (hours) for different reasons, for each record. I have been unable to find anything that will get me started in the right direction and need help.
我想做的是搜索每条记录，并为每条记录找到最高的五个数字，然后返回每个值以及该字段的相关标题.
What I want to do is search each record and find the highest five numbers for each record and then return each value with the associated heading for that field.
例如
PGM ASD HFR STE NHU
客户A _____ 365.4__ 255 ___ 254.6 ___ 180.1 ___ 26
Client A _____365.4__ 255___254.6___180.1___26
一旦我弄清楚了如何从其他20列中查询此信息，我的目标是建立一个表单，将该查询附加到返回这些值的按钮上.我可以将其设置为搜索每条记录，也可以搜索所有记录并找到所有客户端的前五个值.
Once I figure out how to query this info from the other 20 columns, my goal is to build a form that have this query attached to a button that returns these values. I can either set it up to search each record or search all records and find the the top five values for all clients.
再次感谢您的帮助.我不希望有人会为我提供解决方案，只是给我参考或一些材料，以使我朝着正确的方向前进.
Again, thanks for any help. I am not hoping that someone will build me a solution, just get me a reference or some material to get me heading in a direction.
推荐答案
在Access中，这将需要一个自定义函数来比较字段的值.常见的要求是从记录中找到前1个值.已经在许多站点上讨论了很多次.谷歌.找到前五名确实会增加复杂性.如果将数据结构规范化，则TOP N嵌套查询可能会提供所需的输出.
In Access, this will require a custom function that compares values of fields. Common requirement is to find the top 1 value from a record. Has been discussed many times in many sites. Google. Finding top 5 does add complication. If data structure were normalized, a TOP N nested query could probably provide desired output.
当前结构的一种解决方法是建立一个UNION查询，该查询将您的数据重新排列为规范化的结构(数据是垂直而不是水平).然后使用该查询(如表)作为TOP N嵌套查询的源.每个客户只有1条记录吗? UNION示例:
A workaround for the current structure could be to build a UNION query that rearranges your data to normalized structure (data is vertical instead of horizontal). Then use that query like a table as the source for TOP N nested query. Is there only 1 record for each client? UNION example:
SELECT Client, PGM AS Hrs, "PGM" AS Source FROM tablename
UNION SELECT Client, ASD, "ASD" FROM tablename
UNION SELECT Client, HFR, "HFR" FROM tablename
UNION SELECT Client, STE, "STE" FROM tablename
UNION SELECT Client, NHU, "NHU" FROM tablename
continue for 15 other fields;
必须在查询生成器的SQLView中键入或复制/粘贴.限制为50条SELECT行.
Must type or copy/paste in SQLView of query builder. Limit of 50 SELECT lines.
例如TOP N评论 http://allenbrowne.com/subquery-01.html #TopN .不幸的是，这种类型的嵌套查询的执行速度可能很慢.而将其置于UNION而非自然表的基础上甚至会更慢.
For example of TOP N review http://allenbrowne.com/subquery-01.html#TopN. Unfortunately, this type of nested query can be slow performer. And basing it off a UNION instead of natural table can be even slower.
这篇关于在每个记录中找到前5个字段值.返回值和列标题. MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！