I am not sure if it is possible, but I am trying to nest queries. First I create the following 2 queries:
MyQuery = "Select * from " & "T1"
MyQuery2 = "Select * from " & "T2"
Then I'd like to compare both queries and return the differences in the following way:
mkQry = "SELECT (" & MyQuery & ").*" _
& "FROM (" & MyQuery & ") LEFT JOIN (" & MyQuery2 & ") ON " _
& "(" & MyQuery & ".F1) = " & MyQuery2 & ".F1) AND " _
& "(" & MyQuery & ".F2 = " & MyQuery2 & ".F2) AND " _
& "(" & MyQuery & ".F3 = " & MyQuery2 & ".F3) AND " _
& "(" & MyQuery & ".F4 = " & MyQuery2 & ".F4) AND " _
& "(" & MyQuery & ".F5 = " & MyQuery2 & ".F5)" _
& "WHERE (((" & MyQuery2 & ".F5) Is Null))"
Set MyRecordset99 = MyConnection2.Execute(mkQry)
Worksheets("TST").Range("A1").CopyFromRecordset MyRecordset99
However, mkQry
contains a syntax error
and I am not sure how to solve this.
My question is:
Is it possible to nest queries, more or less, in the way I am trying to do and if so, how can I update the syntax to make it work.
You should alias each of the sub-queries, then use the aliases throughout.
mkQry = "SELECT x.* " _
& "FROM (" & MyQuery & ") x LEFT JOIN (" & MyQuery2 & ") y ON " _
& "(x.F1 = y.F1) AND " _
...
& "(x.F5 = y.F5) " _
& "WHERE (((y.F5) Is Null))"