CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
barker
barker

Reputation: 1055

Select highest value in row with microsoft access sql

So my table format is as follows: 

Project | type1 | type2 | type3 | type4  
a | 0 | 1 | 1 | 2  
b | 1 | 0 | 1 | 1  
c | 0 | 0 | 0 | 0

I need the max of each row, as follows: 

Project | max  
a | 2  
b | 1  
c | 0

I cannot use the switch() function in access because I have too many variables and it gives the "too complex" error. any ideas?

SELECT Switch(
[MaxOfBudget Trigger] = 2, "Critical",
[MaxOfSchedule Trigger] = 2, "Critical",
[MaxOfSubmittals Trigger] = 2, "Critical",
[MaxOfSafety Trigger] = 2, "Critical",
[MaxOfChange Orders Trigger] = 2, "Critical",
[MaxOfContingency Trigger] = 2, "Critical",
[MaxOfRFIs Trigger] = 2, "Critical",
[MaxOfBudget Trigger] = 1, "At Risk",
[MaxOfSchedule Trigger] = 1, "At Risk",
[MaxOfSubmittals Trigger] = 1, "At Risk",
[MaxOfSafety Trigger] = 1, "At Risk",
[MaxOfChange Orders Trigger] = 1, "At Risk",
[MaxOfContingency Trigger] = 1, "At Risk",
[MaxOfRFIs Trigger] = 1, "At Risk",
[MaxOfBudget Trigger] = 0, "Okay",
[MaxOfSchedule Trigger] = 0, "Okay",
[MaxOfSubmittals Trigger] = 0, "Okay",
[MaxOfSafety Trigger] = 0, "Okay",
[MaxOfChange Orders Trigger] = 0, "Okay",
[MaxOfContingency Trigger] = 0, "Okay",
[MaxOfRFIs Trigger] = 0, "Okay", )
AS test, [Project Triggers].[Project Number]
FROM [Project Triggers];

Upvotes: 1

Views: 1590

Answers (2)

GolezTrol
GolezTrol

Reputation: 116100

I completely agree with Daniël's answer, and I hope you are able to modify the data model. But if you can't, you might declare a function that can return the largest of a list of numbers:

Function MaxOfList(ParamArray varValues()) As Variant
    Dim i As Integer        'Loop controller.
    Dim varMax As Variant   'Largest value found so far.

    varMax = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
      If varMax >= varValues(i) Then
        'do nothing
      Else
        varMax = varValues(i)
      End If
    Next

    MaxOfList = varMax
End Function

A slightly more advanced version, as well as its Min counterpart can be found at
Allen Browne's Access Tips: MinOfList() and MaxOfList() functions

Upvotes: 2

Daniel
Daniel

Reputation: 4539

Your database isn't normalized the way it should be. You should have a type table, with the values 1,2,3,4 in it, and then a join table to your project table.

|project | type_id | value |
+--------+---------+-------+
|a       | 1       | 0     |
|a       | 2       | 1     |
|a       | 3       | 1     |
|a       | 4       | 2     |
|b       | 1       | 1     |
|b       | 2       | 0     |
|b       | 3       | 1     |
|b       | 4       | 1     |
|c       | 1       | 0     |
|c       | 2       | 0     |
|c       | 3       | 0     |
|c       | 4       | 0     |

Then you can do something like SELECT project, max(value) FROM project_type_values GROUP BY project; to get your results.

Upvotes: 2

Related Questions