Paradox Paradox - 3 months ago 12
SQL Question

How to maintain uniqueness during SQL Join within Access-VBA function?

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
.

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