Access SQL that will display multiple maximum/minimum values and their corresponding case

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

Desired Output

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

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.

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.

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.

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

Solution

Looks a task for a spreadsheet.

Anyway:

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))

and so on.