如何在Access-VBA函数中的SQL Join过程中保持唯一性？ [英] How to maintain uniqueness during SQL Join within Access-VBA function?

问题描述

我目前具有以下Access VBA函数：

I currently have the following Access VBA function:

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

    sqlJoinQuery = "SELECT [" + tableNameTemp + "].[Field1], " & _
                   "[" + tableNameTemp + "].[Field2], " & _
                   "[" + tableNameTemp + "].[Field3], " & _
                   "[" + tableNameTemp + "].[Field4], " & _
                   "[" + tableName + "].*" & _
                   " INTO " + newTableName & _
                   " FROM [" + tableNameTemp & _
                   "] INNER JOIN [" + tableName & _
                   "] ON [" + tableNameTemp + "].[" + commonField + "] = [" + tableName + "].[" + commonField + "];"
    Debug.Print sqlJoinQuery
    CurrentDb.Execute sqlJoinQuery

End Function

这是在使用 tableName 表，并使用 newTableName 表的<$ c内部联接$ c> Field1 到 commonField 上的 Field4 。请注意， Field4 与 commonField 相同，因为需要选择它来执行联接。

What this is doing is taking the tableName table and inner joining with the newTableName table's Field1 to Field4 on commonField. Note that Field4 will be the same as commonField, since it needs to be selected to perform the join.

为了传达预期的行为，我必须说明 tableNameTemp 字段的结构。下表是从 tableNameTemp 中提取的某些字段的示例，就像它们会出现在 tableNameTemp 表。

In order to convey my intended behavior, I must explain how tableNameTemp's fields are structured. The table immediately below is an example of some of the fields that will be pulled from tableNameTemp, as they would appear in the tableNameTemp table.

╔════════════════════════╦════════╦════════╦════════╗
║ Field4 AKA commonField ║ Field1 ║ Field2 ║ Field3 ║
╠════════════════════════╬════════╬════════╬════════╣
║  SA12                  ║  No    ║  No    ║  No    ║
╠════════════════════════╬════════╬════════╬════════╣
║  CY84                  ║  No    ║  No    ║  No    ║
╠════════════════════════╬════════╬════════╬════════╣
║  CY84                  ║  Yes   ║  No    ║  No    ║
╠════════════════════════╬════════╬════════╬════════╣
║  CY84                  ║  No    ║  No    ║  Yes   ║
╠════════════════════════╬════════╬════════╬════════╣
║  CY84                  ║  No    ║  Yes   ║  No    ║
╠════════════════════════╬════════╬════════╬════════╣
║  EH09                  ║  Yes   ║  No    ║  No    ║
╠════════════════════════╬════════╬════════╬════════╣
║  EH09                  ║  No    ║  No    ║  No    ║
╚════════════════════════╩════════╩════════╩════════╝

如上所示， tableNameTemp 表没有唯一的commonField / Field4值。但是，将与之联接的表 tableName ，确实具有唯一的commonField / Field4值。我打算做的是使 Field1 - Field3 中的每个字段（如果有记录） 是，然后将是映射到 tableName <中相关记录的同一字段上/ code>。这样， tableName 可以保持其 commonField 的唯一性。

As you can see above, the tableNameTemp table does not have unique commonField/Field4 values. However, the table which it will be joined with, tableName, does have unique commonField/Field4 values. What I intend to do is make it so that for each field in Field1 - Field3, if any of the records have a yes, then map a yes onto the same field in the related record in tableName. That way, tableName can maintain the uniqueness of it's commonField. How might I be able to achieve this?

因此，以上表中的示例 tableNameTemp 值为例，下表显示了这些值将如何映射到 tableName 表

So, given the example tableNameTemp values in the table above, the table below shows how those values would be mapped onto the tableName table

╔════════════════════════╦════════╦════════╦════════╗
║ Field4 AKA commonField ║ Field1 ║ Field2 ║ Field3 ║
╠════════════════════════╬════════╬════════╬════════╣
║  SA12                  ║  No    ║  No    ║  No    ║
╠════════════════════════╬════════╬════════╬════════╣
║  CY84                  ║  Yes   ║  Yes   ║  Yes   ║
╠════════════════════════╬════════╬════════╬════════╣
║  EH09                  ║  Yes   ║  No    ║  No    ║
╚════════════════════════╩════════╩════════╩════════╝

请注意，两个表和 Field1 - Field4 不是 tableName 和 tableNameTemp 的唯一字段。

Note that there is no primary key in either of the tables and Field1 -Field4 are not the only fields in both tableName and tableNameTemp.

推荐答案

我想知道除field1-4之外的所有其他字段是否具有值YES或NO。但是，从上面的数据集中，您可以尝试一下。

I was wondering if you have all other fields besides field1-4 to have values YES or NO. But, from the dataset above you can try this.

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

这篇关于如何在Access-VBA函数中的SQL Join过程中保持唯一性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！