SQL Show records 1

[fileman1]

I have a procedure for selecting records if certain words are in the record. This works okay, producing a distinct list.

Now I need to show records that contain one or more words selected from the list, if records do not contain the words selected in the list, then they are not listed. Hope that's clear.

Existing code

    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 DISTINCT MAIN.ID1, MAIN.QUESTION FROM MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1"
    MySql = MySql & " WHERE JUNCTION1.KeywordID IN (" & Mid(strList1, 1) & ")"
      
    Me.List0.RowSource = MySql

 

[SkipVought]

Hi,

What do you mean by "in the record?"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fileman1]

Thanks.

The "strList1" contains ID numbers of Keywords, Keywords being names of applications, ie Powerpoint, Access, Word etc

My Sql line WHERE JUNCTION1.KeywordID IN (" & Mid(strList1, 1) & ")" then picks records where the KeywordID is present in the table Main wich is joined to a Junction table JUNCTION1

The code at present fills a Distinct list of KeywordID matches, however I want to be able to list records where Access and Word are there, or Access and Powerpoint or Word on it's own etc. So records are only put in my selection list where the permutations KeywordID's are matched. Hope that clarifies. Thanks again

 

[SkipVought]

So these values are in the KeywordID FIELD in the record, yes?

Now you used the word "contain" to describe the match in KeywordID. Does this mean that others characters or words might be in any particular KeywordID value INCLUDING a word in your list? Or will KeywordID contain only these distinct values in your list?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fileman1]

I have 3 tables, Main, Junction1 and Keyword. In the query I am only using the first 2 tables.

The "Main" Table
ID1 PK
Question
etc

The Junction1 Table
ID1 (relates to Main Table
ID2 (relates to Keyword Table)
KeywordID

When a record is saved in the Main table, word splitting is carried out to enable picking out Keywords and lodging their KeywordID in the Junction1 Table. KeywordID is unique to each Keyword.

Therefore in my query, I have a list box (multi/Simple) which contains a list of KeywordID's.

So I am wanting to produce a list of Main table records relating to KeywordID's selected in the list. So I am comparing my list selection of KeywordID's to those found in the Junction1 table which then provide me with the ID1 primary keys of the Main table. Hope that makes sense.

In the end I only want to get Main table records that match the selections of KeywordID's.

 

[PHV]

So, you wanted simply this ?
MySql = "SELECT DISTINCT MAIN.[!]*[/!] FROM MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1"
MySql = MySql & " WHERE JUNCTION1.KeywordID IN (" & Mid(strList1, 1) & ")"

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

 

[fileman1]

Geronimo, that's it. Many thanks