How to maintain uniqueness during SQL. Join Access-VBA Feature?

Question

I currently have the following VBA access 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 

To do this, make the table tableName and an internal join with the table newTableName Field1 to Field4 on commonField . Note that Field4 will be the same as commonField , as it must be selected to complete the connection.

To convey my intentional behavior, I must explain how tableNameTemp fields are tableNameTemp . The table below is an example of some of the fields that will be retrieved from tableNameTemp since they 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 ║ ╚════════════════════════╩════════╩════════╩════════╝ 

As you can see above, tableNameTemp does not have unique commonField / Field4 values. However, the table with which it will be associated, tableName , has unique commonField / Field4 values. What I intend to do is make sure that for each field in Field1 Field3 , if any of the records has yes , then match yes with the same field in the corresponding record in tableName . This way tableName can preserve its commonField uniqueness. How can i achieve this?

So, given the example tableNameTemp values ​​in the table above, the table below shows how these values ​​will be displayed in the table tableName

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

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

Answer 1

I was wondering if all other fields, except fields 1-4, have YES or NO. But from the above dataset, 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 + "]"