如何使用SQL连接表，在Access-VBA函数中选择最大值？ [英] How to SQL join tables, selecting the largest value in Access-VBA Function?
问题描述
我目前具有以下Access VBA功能，该功能按照上一个问题（对于理解该问题非常有用）：
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
Field3 中包含yes或no的数据，在我的数据中可以包含多个值之一。为简单起见，假设这些值可以是以下字符串集中的任意一项：
（ 0 ，> ; 1,000,000 ， 0001-0010 ）
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)
在这种情况下， Max（） Access SQL函数不适用于 Field3 ，因为它们是具有用户定义层次结构的字符串。但是，我需要选择最大的价值。我将定义从最低（1）到最高（3）的值：
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):
-
0 -
>一百万 -
0001-0010
0>1 million0001-0010
以下是tableNameTemp可能如何显示的示例：
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 ║
╚════════════════════════╩════════╩════════╩══════════════════════╝
给出上面的示例tableNameTemp值，下表显示了如何将这些值映射到tableName表。请注意，它是如何选择要映射的最大值。
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 ║
╚════════════════════════╩════════╩════════╩══════════════════════╝
由于我既不具备丰富的Access SQL经验，也没有VBA经验，所以我对如何实现此目标感到困惑。我猜想这可能涉及对 Field3 的可能值列表进行硬编码，然后根据它们在列表中的位置对其进行排名（即位置3将包含该值） 0 ）。然后，以某种方式将此用作选择哪个值的键。如果有什么用，我在下面提供了代码：
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 '
编辑
根据Serg的帮助更新了代码：
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 + "]"
推荐答案
编码值，在SQL中以这种方式解码max。
Encode value, decode max this way in SQL.
当前您正在将SQL命令构建为（我将表名变量替换为任意值， temp 和 tableName ）
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]
将其构建为
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]
所以[field3]在内部查询中的max（）下编码，而在外部查询中的max解码。
So [field3] is encoded under max() in the inner query and that max is decoded in outer query.
这篇关于如何使用SQL连接表，在Access-VBA函数中选择最大值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
