How to SQL join tables, selecting the largest valu

Question

I currently have the following Access VBA function, which operates as explained in a previous question (very useful for understanding this question):

Private Function MapFields(tableNameTemp As String, tableName As String, commonField As String, newTableName)

    Dim tableNameFieldCount As Integer
    tableNameFieldCount = GetFieldCount(tableName)
    Dim tableNameFieldsArray() As String
    ReDim tableNameFieldsArray(0 To tableNameFieldCount) As String ' since you cannot Dim against a variable size
    Call GetFields(tableName, tableNameFieldsArray)    

    sqlJoinQuery = "SELECT tbl_grp_by.*, [" + tableName + "].* " & _
    "INTO " + newTableName & _
    " FROM (SELECT Max([" + tableNameTemp + "].[Field1]) as [Field1], " & _
    "Max([" + tableNameTemp + "].[Field2]) as [Field2], " & _
    "Max([" + tableNameTemp + "].[Field3]) as [Field3], " & _
    "[" + tableNameTemp + "].[Field4] as [Field4] " & _
    "FROM [" + tableNameTemp & _
    "] INNER JOIN [" + tableName & _
    "] ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "] " & _
    "GROUP BY [" + tableNameTemp + "].[" + commonField + "]) as tbl_grp_by " & _
    "INNER JOIN [" + tableName & _
    "] ON [" + tableName + "].[" + commonField + "] = tbl_grp_by.[" + commonField + "]"


    Debug.Print sqlJoinQuery
    CurrentDb.Execute sqlJoinQuery

End Function

However, instead of Field3 containing either yes or no, in my data it can contain one of several values. For simplicity, let's say that these values can be any one item from the following set of strings:
(0, >1 million, 0001-0010)

In this case, the Max() Access SQL function will not work on Field3 since they're strings with a user-defined hierarchy. Yet, I need the largest value to still be selected. I will define the values from lowest (1) to highest (3):

1. 0
2. >1 million
3. 0001-0010

Here is an example of how tableNameTemp may appear:

╔════════════════════════╦════════╦════════╦══════════════════════╗
║ Field4 AKA commonField ║ Field1 ║ Field2 ║ Field3               ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  SA12                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  Yes   ║  No    ║  0001-0010           ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  No    ║  >1 million          ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  No    ║  Yes   ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  Yes   ║  No    ║  >1 million          ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  No    ║  No    ║  >1 million          ║
╚════════════════════════╩════════╩════════╩══════════════════════╝

And given the example tableNameTemp values above, the table below shows how those values would be mapped onto the tableName table. Notice how it picks the largest value to map.

╔════════════════════════╦════════╦════════╦══════════════════════╗
║ Field4 AKA commonField ║ Field1 ║ Field2 ║ Field3               ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  SA12                  ║  No    ║  No    ║  0                   ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  CY84                  ║  Yes   ║  Yes   ║  0001-0010           ║
╠════════════════════════╬════════╬════════╬══════════════════════╣
║  EH09                  ║  Yes   ║  No    ║  >1 million          ║
╚════════════════════════╩════════╩════════╩══════════════════════╝

As I am neither highly experienced with Access SQL nor VBA, I am at a loss as to how I can make this happen. I'm guessing it may involve hard-coding a list of the possible values for Field3, then ranking them based on their position in the list (i.e. position 3 would contain the value 0). Then, somehow using this as a key for which value to pick. If this is of any use, I have provided the code for it below:

Dim hierarchy(0 to 2) As String
hierarchy(0) = "0001-0010" ' highest value '
hierarchy(1) = ">1 million"
hierarchy(2) = "0"         ' lowest value  '

EDIT
Updated code, as per Serg's help:

sqlJoinQuery = "SELECT tbl_grp_by.[Field1],tbl_grp_by.[Field2], " & _
                        "Switch( " & _
                            "tbl_grp_by.[maxfield3] = 0, '0', " & _
                            "tbl_grp_by.[maxfield3] = 1, '>1 million', " & _
                            "tbl_grp_by.[maxfield3] = 2 '0001-0010' " & _
                        ") as [Field3], " & _
                        "tbl_grp_by.[Field4], " & _
                    "[" + tableName + "].* " & _
                    "INTO [" + newTableName + "] FROM (" & _
                        "SELECT Max([" + tableNameTemp + "].[Field1]) as [Field1], " & _
                            "Max([" + tableNameTemp + "].[Field2]) as [Field2], " & _
                            "Max(Switch( " * _
                                "[" + tableNameTemp + "].[Field3] = '0' , 0, " & _
                                "[" + tableNameTemp + "].[Field3] = '>1 million' , 1, " & _
                                "[" + tableNameTemp + "].[Field3] = '0001-0010', 2 " & _
                            "))as [maxField3], " * _
                            "[" + tableNameTemp + "].[Field4] as [Field4] " * _
                        "FROM [" + tableNameTemp + "] " & _
                        "INNER JOIN [" + tableName + "] " & _
                            "ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "] " & _
                        "GROUP BY [" + tableNameTemp + "].[" + commonField + "] " & _
                    ") as tbl_grp_by " & _
                    "INNER JOIN [" + tableName + "] " & _
                        "ON [" + tableName + "].[" + commonField + "] = tbl_grp_by.[" + commonField + "]"

Answer 1

Encode value, decode max this way in SQL.

Currently you are building SQL command as (i replaced table name variables with arbitrary values, temp and tableName )

SELECT tbl_grp_by.*, [tableName].*  
INTO newTableName 
FROM (
    SELECT Max([temp].[Field1]) as [Field1], 
        Max([temp].[Field2]) as [Field2],  
        Max([temp].[Field3]) as [maxField3], 
        [temp].[Field4] as [Field4]  
    FROM [temp]
    INNER JOIN [tableName ]
       ON [temp].[commonField] = [tableName].[commonField] 
    GROUP BY [temp].[commonField]
 ) as tbl_grp_by  
INNER JOIN [tableName]
  ON [tableName].[commonField] = tbl_grp_by.[commonField]

Build it as

SELECT tbl_grp_by.[Field1],tbl_grp_by.[Field2],
    Switch( 
        tbl_grp_by.[maxfield3] = 0, '0',
        tbl_grp_by.[maxfield3] = 1, '>1 million',
        tbl_grp_by.[maxfield3] = 2 '0001-0010' 
    ) as [Field3],   
    tbl_grp_by.[Field4],
[tableName].*  
INTO newTableName 
FROM (
    SELECT Max([temp].[Field1]) as [Field1], 
        Max([temp].[Field2]) as [Field2],  
        Max(Switch(  
            [temp].[field3] = '0' , 0,
            [temp].[field3] = '>1 million' , 1,
            [temp].[field3] = '0001-0010', 2  
         ))as [maxField3], 
        [temp].[Field4] as [Field4]  
    FROM [temp]
    INNER JOIN [tableName ]
       ON [temp].[commonField] = [tableName].[commonField] 
    GROUP BY [temp].[commonField]
 ) as tbl_grp_by  
INNER JOIN [tableName]
  ON [tableName].[commonField] = tbl_grp_by.[commonField]   

So [field3] is encoded under max() in the inner query and that max is decoded in outer query.

Answer 2

I would consider creating a reference table with value field as it easier to maintain specially when the values change overtime.

CREATE TABLE tblReference (field_txt text, val Integer);

Get the field_txt with highest value and unique field then left join(inner join) to your current dataset.

qry_field3_max = "SELECT [Field3],[commonField] FROM tblReference INNER JOIN (SELECT [commonField], MAX(val) as val FROM tblReference INNER JOIN tblNameTemp on tblReference.[field_txt]=tblNameTemp.[Field3] Group By [commonField]) as tbl_max_fields on tblReference.val=tbl_max_fields.val"