Stuck on SQL query 1

[fileman1]

I have a query that fills a list box with records that contain words selected in a simple multiselect list box.

It works okay, however I am struggling to find how I can avoid duplication of records where more than 1 word is contained. I have tried re arranging the code to try and use the Distinct word, but have spent so long with so many permutations not getting it right. Help!! Thanks

Dim strList1 As String, item As Variant

    strList1 = ""
    For Each item In Me.KeySearch.ItemsSelected
        strList1 = strList1 & ",'" & Replace(Me.KeySearch.ItemData(item), "'", "''") & "'"
    Next item

    MySql = "SELECT Keywords.KeywordID, Keywords.Keyword, KEYWORD.KeywordID, MAIN.IDNumber, MAIN.ID1"
    MySql = MySql & " FROM (Keywords INNER JOIN KEYWORD ON Keywords.KeywordID = KEYWORD.KeywordID)"
    MySql = MySql & " INNER JOIN (MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1) ON KEYWORD.ID2 = JUNCTION1.ID2"
    MySql = MySql & " WHERE Keywords.Keyword IN (" & Mid(strList1, 2) & ")"

    Me.MM.RowSource = MySql

 

[ClulessChris]

SELECT DISTINCTROW

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[fileman1]

Hi Chris, thanks.
I tried re arranging again, using DistinctRow but ID1 still duplicates itself.

    MySql = "SELECT DISTINCTROW MAIN.ID1, Keywords.Keyword, Keywords.KeywordID"
    MySql = MySql & " FROM (Keywords INNER JOIN KEYWORD ON Keywords.KeywordID = KEYWORD.KeywordID)"
    MySql = MySql & " INNER JOIN (MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1) ON KEYWORD.ID2 = JUNCTION1.ID2"
    MySql = MySql & " WHERE Keywords.Keyword IN (" & Mid(strList1, 2) & ")"

Probably due to Keywords.Keyword and Keyword.Keywords.ID being more than one (different) in the query. ??

 

[ClulessChris]

Probably due to Keywords.Keyword and Keyword.Keywords.ID being more than one (different) in the query.

yes that would make sense. you say that SELECT DISTICT also produces duplicates? could this be due to the data? e.g. do some entries have leading or trailing spaces?

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[fileman1]

Thanks. No, there are no spaces. I think I might resort to writing it all in longhand (recordset loops), at least I can then see whats happening instead of the language of SQL which blinds me. Think I will make a coffee and do it the long way. There are not thousands of records so it shouldn't be slow.
Thanks for your help

 

[PHV]

What about this ?
MySql = "SELECT MAIN.ID1, First(Keywords.Keyword) AS firstKeyword, First(Keywords.KeywordID) AS firstKeywordID"
MySql = MySql & " FROM (Keywords INNER JOIN KEYWORD ON Keywords.KeywordID = KEYWORD.KeywordID)"
MySql = MySql & " INNER JOIN (MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1) ON KEYWORD.ID2 = JUNCTION1.ID2"
MySql = MySql & " WHERE Keywords.Keyword IN (" & Mid(strList1, 2) & ")"
MySql = MySql & " GROUP BY MAIN.ID1"

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

 

[fileman1]

Perfect, I didn't even finish my coffee! Works exactly as I wanted it to, thank you very much.

 

[fileman1]

Can I ask, but how can I add other items that would be in the table MAIN. Example, want to add Main.Customer

I tried adding but got no output. It's just something I might need to do later. Thanks

 

[PHV]

MySql = "SELECT MAIN.ID1, Main.Customer, First(Keywords.Keyword) AS firstKeyword, First(Keywords.KeywordID) AS firstKeywordID"
...
MySql = MySql & " GROUP BY MAIN.ID1, Main.Customer"

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

 

[fileman1]

Thanks again. You make it look so logical, yet I would have never ever got there. Have a good day

 

[Andrzejek]

...and to eliminate:
[tt]
IN (" & [blue]Mid([/blue]strList1[blue], 2)[/blue] & ")"
[/tt]
in your MySql, consider:

strList1 = ""
For Each item In Me.KeySearch.ItemsSelected
    If strList1 = "" Then
        strList1 = Replace(Me.KeySearch.ItemData(item), "'", "''") & "'"
    Else
        strList1 = strList1 & ",'" & Replace(Me.KeySearch.ItemData(item), "'", "''") & "'"
    End If
Next item

Just a suggestion....

Have fun.

---- Andy