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
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.
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 ║ ╚════════════════════════╩════════╩════════╩════════╝
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?
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 ║ ╚════════════════════════╩════════╩════════╩════════╝
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.
Advertisement
Answer
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 + "]"