How to SQL join tables, selecting the largest value in Access-VBA Function?

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 + "]"

Interrogate

case ... when is not supported in Access SQL. – Malfeasance

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.

Cloistered

I'm not quite sure that I understand, would I have to encode every value in Field3 as an integer, run the SQL query the same as before, then decode the values back to the original strings? If so, then I just append that code block to the end of my SQL query? – Interrogate

No, you need to build SQL query which will do all the encoding/decoding work. See updated answer – Cloistered

When I try to integrate it, I am getting a syntax error. I might be making a mistake in adding it in, but can't seem to find it (I'm not very well versed in SQL). If I updated my OP with the code, would you mind helping me sort it out? – Interrogate

Ok, I'll try to help. – Cloistered

Just remove --0001-0010 it's Sql comment at the end of line. Your generated Sql has no line breaks and it comments out all the rest text. – Cloistered

It appears that after removing the comment, it's telling me that I have a syntax error (missing operator) in the query expression between the first case and the end – Interrogate

Oops sorry for that, I answered as it was Sql-server. Access has no Case, use Switch in Access sql. You really may need one more select to keep switch simple – Cloistered

See Switch version – Cloistered

Serg: I'm do not understand how this fit's into my current query. I already have a FROM [" + tableNameTemp, so how could I piece these together? – Interrogate

I have posted the query, adapted to my function. However, I am getting a type mismatch. Do you think it would be best to put this issue in another SO question? – Interrogate

Yes, this discussion got a bit lengthy. – Cloistered

I have created another question here if you are interested in taking a look. – Interrogate

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"

Merow