How does SQL join tables choosing the highest value in an Access-VBA function?

Question

I currently have the following VBA accessor function that works as described in the 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 yes or no, it may contain one of several values ​​in my data. For simplicity, suppose these values ​​can be any one element of the following set of strings:
( 0 , >1 million , 0001-0010 )

In this case, the Max() function of Access SQL will not work on Field3 , since they are rows with a user-defined hierarchy. However, I need to still have the highest value selected. I will determine the values ​​from the smallest (1) to the highest (3):

• 0
• >1 million
• 0001-0010

Here is an example of what tableNameTemp might look like:

 ╔════════════════════════╦════════╦════════╦══════════════════════╗ ║ 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 above examples of tableNameTemp, the table below shows how these values ​​will be displayed in the tableName table. Notice how it selects the highest value to display.

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

Since I am not very good at Access SQL and VBA, I don’t understand how to do this. I suggest that this may include hard coding the list of possible values ​​for Field3 , and then ranking them based on their position in the list (i.e., Position 3 will contain a value of 0 ). Then, somehow using this as the key for which you want to select a value. If this is useful, I 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, according to Serg 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.

You are currently creating the SQL command as (I replaced the 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 like

 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 internal request and that max is decoded in the external request.

Answer 2

I would consider creating a reference table with a field value, since it is easier to maintain when the values ​​change overtime.

CREATE TABLE tblReference (field_txt text, val Integer);

Get the field_txt with the highest value and a unique field, and then the left join (inner join) with 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"