CODEWITHSUNDEEP
sqlms-accessvba
Tom Bouchardin
Tom Bouchardin

Reputation: 15

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?

Upvotes: 0

Views: 1577

Answers (2)

C02
C02

Reputation: 155

Inside an SQL query, this test can be done on-the-fly with a user-defined function.

SELECT Case, Flxmax, Flxmin, Frxmax, Frxmin, 
    MAXPOSITIONz([Flxmax],[Flxmin],[Frxmax],[Frxmin]) As FieldPos,
    Choose([FieldPos],"Flxmax","Flxmin","Frxmax","Frxmin") As Choose1,
    Choose([FieldPos],[Flxmax],[Flxmin],[Frxmax],[Frxmin]) As Choose2
FROM MyTable

Result:

Case  Flxmax  Flxmin  Frxmax  Frxmin  FieldPos  Choose1  Choose2
aaaa  100000  100000  100000  100000         1   Flxmax   100000
bbbb  100000  100009  100008  100007         2   Flxmin   100009
cccc  100000  100001  100099  100099         3   Frxmax   100099
dddd  100001  100002  100003  100004         4   Frxmin   100004

The function:

'This function combines the features of Excel MAX() and Access Nz() to find Max, and allows Null or String'
Public Function MAXPOSITIONz(ParamArray ParameterData() As Variant) As Currency
    Dim Result As Currency: Result = 0
    Dim p As Variant
    Dim i as Long
    Dim ArrayLength as Long: ArrayLength = UBound(ParameterData) - LBound(ParameterData)
    Dim Position as Long: Position = 1
    
    If ArrayLength < 1 Then Return 1
    
    For i = 0 to ( UBound(ParameterData) - LBound(ParameterData) )
        If IsNumeric(Nz(ParameterData(i), "")) Then
            If CCur(ParameterData(i)) > Result Then
                Result = CCur(p)
                Position = i
            End If
        End If
    Next i
    
    MAXPOSITIONz = Position
End Function

It is also possible to use a generic function that only returns the Max value:

            MAXz([Field01],[Field02], etc ...)     'Decimal Numbers
Int(        MAXz([Field01],[Field02], etc ...) )   'Whole numbers
CDate(      MAXz([Field01],[Field02], etc ...) )   'Date with time
CDate( Int( MAXz([Field01],[Field02], etc ...) ) ) 'Date without time

The generic function:

''This function combines the features of Excel MAX() and Access Nz() to find Max,
''and allows Null or String
Public Function MAXz(ParamArray ParameterData() As Variant) As Currency
    Dim Result As Currency: Result = 0
    Dim p As Variant
    
    For Each p In ParameterData
        If IsNumeric(Nz(p, "")) Then _
            If CCur(p) > Result Then _
                Result = CCur(p)
    Next p
    
    MAXz = Result
End Function

"As Currency" returns decimal numbers. If you don't want decimal, you can adjust the code specifically for Date or Long.

Upvotes: 1

Gustav
Gustav

Reputation: 55856

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.

Upvotes: 1

Related Questions