Eliminate duplicate records 1

[fileman1]

I have a multiselect list box, containing words.

However my query is duplicating records where more than one words are identified in a record. I only want the record to appear once.

Dim strList1 As String, item As Variant
    strList1 = ""
    For Each item In Me.List3.ItemsSelected
    strList1 = strList1 & Me.List3.ItemData(item) & ","
    Next item
   
    MySql = "SELECT MAIN.ID1, MAIN.QUESTION, JUNCTION1.KeywordID FROM MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1"
    MySql = MySql & " WHERE JUNCTION1.KeywordID IN (" & Mid(strList1, 1) & ")"

 

[Andrzejek]

Did you try:
[tt]
SELECT [blue]DISTINCT[/blue] MAIN.ID1 ...[/tt]

Have fun.

---- Andy

 

[fileman1]

Thanks Andy, no I tried that and it didn't work. Just tried adding Group By Main.ID1 but something wrong in my syntax so just going to go back to it to try agsin

 

[Andrzejek]

Your SELECT statement will give you record(s) back.
Your fields will have names like:

[pre]
ID1 QUESTION KeywordID
[/pre]

Could you show some records you get and which ones are 'duplicates'?

Have fun.

---- Andy

 

[PHV]

To avoid duplicate MAIN records:
MySql = "SELECT DISTINCT MAIN.ID1, MAIN.QUESTION FROM MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1"

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

 

[fileman1]

Thanks.

A multi select list box contains a list of Applications, ie Access, Word, Powerpoint etc

Selecting words finds records where the application is mentioned in a question field. I am trying to stop a record being listed 3 times because all three applications are mentioned in the records question

 

[fileman1]

Sorry PHV, threads passed at the same time. That sorted out my problem, much appreciated, thanks

 

[Andrzejek]

So I will ask again:

[pre]
ID1 QUESTION KeywordID
??? ??? ???
??? ??? ???
??? ??? ???
??? ??? ???

[/pre]
Please, fill '???' with your data and point to 'duplicates'

Have fun.

---- Andy

 

[fileman1]

Thanks Andy, however PHV gave me the solution I wanted

ID1 Question KeywordID
12 ...Access....Word... 6
12 ...Word......Access 5
12 ...Word....Access....Powerpoint 7

So I was trying to stop record with PK 12 appearing in a list 3 times. All works okay now