访问SQL，将显示多个最大值/最小值值及其对应的情况下 [英] Access SQL that will display multiple maximum/minimum values and their corresponding case

问题描述

我想创建一个SQL查询在Microsoft Access将产生最大/最小值以下各个领域，并返回其对应的连接情况。

I am trying to create an SQL query in Microsoft Access that will produce the maximum/minimum values for each of the fields below and return with their corresponding case attached

**Force Table**
case            Flxmax   Flxmin  Frxmax  Frxmin 
hs00p16010od    582.24   666.81  796.44  -451.15    
hs00p16015od    878.7    878.7   1096.3  -500.36    
hs00p16020od    1071.95  1071.9  1281.2  -743.05    
hs00p16025od    1186.65  1186.6  1397.8  -959.36    

所需的输出

Flxmax   1186.65   hs00p16025od
Flxmin   666.81    hs00p16010od
Frxmax   1397.8    hs00p16025od
Frxmin   -959.36   hs00p16025od

目前的code（只有最大限度地提高1场，不能选择1最大值为相同的值）

Current Code (only maximizes 1 field and cannot choose 1 max for identical values)

SELECT case, [Flxmax]
FROM Force
WHERE [Flxmax] = (SELECT max([Flxmax]) FROM Force);

此外，如果有多个相同的最大/最小值，有一种方法可以选择只有一个。

In addition, if there are multiple identical max/min values is there a way to pick just one.

关于表中，有总共40字段，包括这里示出的那些需要最大/最小值来计算的。记录/病例数约为30,000。

Regarding the table, there are 40 fields in total including the ones shown here that require max/min values to be calculated. The number of records/cases is around 30,000.

我有我需要在运行一个类似的查询（时名，以相同的顺序，是常见的各方进行）14类似的表。你可以想象我想保持必要的查询，以最少的数量。

I have 14 similar tables that I will need to run a similar query on (the case names, in the same order, being common among all of them). As you can imagine I want to keep the number of queries necessary to a minimum.

如果有一个与访问SQL限制，这可能是使用VBA办？

If there is a limitation with Access SQL, is it possible to do it using VBA?

推荐答案

中查找为preadsheet。任务

Looks a task for a spreadsheet.

总之：

Select "Flxmax" As FField, [case], [Flxmax] 
From (SELECT case, [Flxmax]
    FROM Force
    WHERE [Flxmax] = (SELECT Max([Flxmax]) FROM Force))
Union All
Select "Flxmin" As FField, [case], [Flxmin] 
From (SELECT case, [Flxmin]
    FROM Force
    WHERE [Flxmin] = (SELECT Min([Flxmin]) FROM Force))
Union All    
Select "Frxmax" As FField, [case], [Frxmax] 
From (SELECT case, [Frxmax]
    FROM Force
    WHERE [Frxmax] = (SELECT Max([Frxmax]) FROM Force))
Union All    
Select "Frxmin" As FField, [case], [Frxmin] 
From (SELECT case, [Frxmin]
    FROM Force
    WHERE [Frxmin] = (SELECT Min([Frxmin]) FROM Force))

等等。

这篇关于访问SQL，将显示多个最大值/最小值值及其对应的情况下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！