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

Question

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.

Remarkable

Answer 1

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

Grapevine

One of the fields does have text values in which there is a predefined hierarchy, but I did not mention this to over-complicate the question. I figured that I would bring it up in another question if need-be. Also, thank you for your answer - I will give it a try! – Remarkable

When I run this, after removing the unnecessary quote after GROUP BY, I get the following error: Run-time erorr '3120': Cannot group on fields selected with "*" – Remarkable

Edit: This still happens even when I remove the GROUP BY tag. – Remarkable

sorry, i havent notice that you are also trying to pull the records from tableName. You have to put all the other fields missing both on SELECT and GROUP BY clause from tableName – Grapevine

Do you mean to individually select each field in tableName? – Remarkable

yes, individual fields. you can also check the update on query i had. It used subquery to get the aggregated values t. – Grapevine

I am currently working on a function that get all of the field names in tableName and turns it into the sql select strings. Does you updated query already do that? – Remarkable

The updated query does get the aggregated values from field1-4 group by commonField then merged using the SELECT clause to tableName fields which is then used to create the new table – Grapevine

Thanks, do you know why I'm now getting this error: Run-time error '3067': Query input must contain at least one table or query – Remarkable

I missed the space from "INTO " + newTableName & _ "FROM. it should be "INTO " + newTableName & _ " FROM – Grapevine

Let us continue this discussion in chat. – Grapevine

winghei: Unfortunately, my internet is filtered (not by me), so I cannot use the chat function of this site. – Remarkable

Please check the space before FROM after INTO clause. It should work fine. – Grapevine

I have posted a continuation of this question involving values other than (yes, no) if you are interested. Your previous solution worked very well, thank you. – Remarkable