SQL Query not working 1

[fileman1]

I have two related tables

Table 1 - MAIN Table 2 - Keyword
ID1 Primary Key ID1 Foreign Key
etc ID2 Primary Key
KeywordName (Text)

I have a form with a List Box - Multi Select - List3

The following is not working, I think the output from the list selction is okay, but query opens a parameter box every time?

I am trying to get a list of records from table one (Main) that contain the combination of words selected, e.g If one word is selected, then all records are displayed where single word is linked, if two words are selected then only records get displayed where both words are related etc etc.

strList1 = ""
    For Each item In Me.List3.ItemsSelected
        strList1 = strList1 & Me.List3.ItemData(item) & ","
    Next item
   
   
    MySql = "SELECT DISTINCT MAIN.*, KEYWORD.KeywordName, KEYWORD.ID1 FROM MAIN INNER JOIN KEYWORD ON MAIN.ID1 = KEYWORD.ID1"
    MySql = MySql & " WHERE KEYWORD.KeywordName IN (" & Mid(strList1, 1) & ")"

 

[Andrzejek]

If you would do:
[tt]Debug.Print MySql [/tt]
You would probably see:
[tt]
SELECT DISTINCT MAIN.*, KEYWORD.KeywordName, KEYWORD.ID1
FROM MAIN INNER JOIN KEYWORD ON MAIN.ID1 = KEYWORD.ID1
WHERE KEYWORD.KeywordName IN (ABC,XYZ,TYE)
[/tt]

Hint: no quotes around [red]'[/red]ABC[red]'[/red],[red]'[/red]XYZ[red]'[/red],[red]'[/red]TYE[red]'[/red]








Have fun.

---- Andy

 

[fileman1]

Thanks Andy, I had done a debug.print on it, and found what you say. I did not realise it needed '' bits. I had pasted it in from another query, which now I understand was passing numeric ID numbers.

I will see how to add in quotes. Its then the tricky bit of showing records only having combinations of words. Regards, thanks

 

[Andrzejek]

You meed quotes around it if KeywordName is text. No need to have quotes if it is a number.

You may try something like this:

For Each item In Me.List3.ItemsSelected
    strList1 = strList1 & "[red]'[/red]" & Me.List3.ItemData(item) & "[red]'[/red],"
Next item

Have fun.

---- Andy

 

[fileman1]

Thanks Andy, that now creates a list. All I need to do now is find out how I only get records where a combination of keywords is related to those records. At the moment it produces multiple records for each word. Maybe this cannot be done in SQL?

 

[PHV]

Maybe this cannot be done in SQL?
I'm pretty sure this can be done ...
What is your actual SQL code ?
What is the primary key of MAIN ?

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

 

[fileman1]

Thanks PHV.

The Main table primary key is ID1

My SQL is:

MySql = "SELECT DISTINCT MAIN.*, KEYWORD.KeywordName, KEYWORD.ID1 FROM MAIN INNER JOIN KEYWORD ON MAIN.ID1 = KEYWORD.ID1"
    MySql = MySql & " WHERE KEYWORD.KeywordName IN (" & Mid(strList1, 1) & ")"

 

[PHV]

Mid(strList1, 1)
Really ?
What is your actual code building strList1 ?

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

 

[fileman1]

Set db = CurrentDb
    Set rs1 = db.OpenRecordset("MAIN", dbOpenDynaset)
   
 strList1 = ""
      
    For Each item In Me.List3.ItemsSelected
    strList1 = strList1 & "'" & Me.List3.ItemData(item) & "',"
    Next item
  
    MySql = "SELECT DISTINCT MAIN.*, KEYWORD.KeywordName, KEYWORD.ID1 FROM MAIN INNER JOIN KEYWORD ON MAIN.ID1 = KEYWORD.ID1"
    MySql = MySql & " WHERE KEYWORD.KeywordName IN (" & Mid(strList1, 1) & ")"
        
    Me.List0.RowSource = MySql

Thanks again

 

[PHV]

The Main table primary key is ID1
FROM MAIN INNER JOIN KEYWORD ON MAIN.ID1 = KEYWORD.ID1
Sorry, I don't understand the relationships betwenn MAIN and KEYWORD

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

 

[fileman1]

Sorry PHV.

Table 1 - MAIN
ID1 - Primary Key
etc
etc

Table 2 - Keyword
ID1 - Foreign Key
ID1 Primary Key
KeywordName Text (Word)

One to many - Table 1 to Table 2

Hope thats clearer Regards

 

[PHV]

Please, reread your last post.
What are the keys involved in the one to many relationship ?

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

 

[fileman1]

It's the end of the day of too much stress, Table 2, ID2 is Primary Key.

 

[PHV]

What about this ?

For Each Item In Me!List3.ItemsSelected
  strList1 = strList1 & ",'" & Me!List3.ItemData(Item) & "'"
Next Item

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"

Me!List0.RowSource = MySql

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

 

[fileman1]

Many many thanks PHV, yes that does exactly what I wanted. Perfect