SQL Change 1

[fileman1]

I am trying to modify this SQL for a different database, the function being the same for grouping on Keywords, but wanting different data for the list that it's the rows source.

The Query:

MySql = "SELECT A.* FROM MAIN A INNER JOIN " _
          & "(SELECT M.ID1 FROM MAIN M INNER JOIN KEYWORD K ON M.ID1 = K.ID1" _
          & " WHERE K.KeywordName IN (" & Mid(strList1, 2) & ")" _
          & " GROUP BY M.ID1 HAVING Count(*)=" & Me!LIST3.ItemsSelected.Count _
          & ") B ON A.ID1=B.ID1"

The fields wanted:

   ' MySql = "SELECT MAIN.ID1, MAIN.[Brief Issue Summary], MAIN.Status FROM MAIN;"

Tried various guessed permutations but tied up in knots

 

[fileman1]

Oh Dear, egg on face. It was all there, just had not enough columns set in list box. Now works fine

 

[PHV]

So, you wanted this ?

MySql = "SELECT A.ID1, A.[Brief Issue Summary], A.Status FROM MAIN A INNER JOIN " _
          & "(SELECT M.ID1 FROM MAIN M INNER JOIN KEYWORD K ON M.ID1 = K.ID1" _
          & " WHERE K.KeywordName IN (" & Mid(strList1, 2) & ")" _
          & " GROUP BY M.ID1 HAVING Count(*)=" & Me!LIST3.ItemsSelected.Count _
          & ") B ON A.ID1=B.ID1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

Thankyou PHV, yes that's correct. Now the part A.* makes sense as being * all fields in A. Regards