Question Comparing Multiple Querys

[jasonzook]

I am using Visual Studio 2010 and Microsoft Access 2007

I am not sure of the best way to describe my problem, but here goes

I have a query (PartsQry) in access that has 6 columns (ID, KYWD1, KYWD2, KYWD3, KYWD4, KYWD5). The KYWD columns hold various keywords that are specific to the ID.

In my application I have a textbox (SearchTxt) that a user can input up to 5 keywords, seperated by a coma. When they click the search button the application is supposed to hit the PartsQry and return only results that match all the keywords the user typed in. I think I've gotten pretty close but I'm missing something because im getting extra records.

If KeywordRb.Checked Then
            Dim Keywords() As String
            Keywords = SearchTxt.Text.Split(", ")
            QryKeyword(Keywords) 
            Try
                ProductDGV.DataSource = PartsDS.Tables("KeywordList")
            Catch : End Try
            With ProductDGV 'The Data Grid View where i display the results
                .Columns("ID").Visible = False
                .Columns("KYWRD1").Visible = False
                .Columns("KYWRD2").Visible = False
                .Columns("KYWRD3").Visible = False
                .Columns("KYWRD4").Visible = False
                .Columns("KYWRD5").Visible = False
                .Columns("MVTTL").Width = 190
                .Columns("RELDT").Width = 65
                .Columns("RTNG").Width = 85
            End With
        End If

Public Sub QryKeyword(ByVal Keyword() As String)
        Dim SelectStr As String
        Try : ProductDS.Tables("KeywordList").Clear() : Catch : End Try
        For i As Integer = 0 To UBound(Keyword)
            SelectStr = "SELECT [ID], " & _
                               "[MVTTL], " & _
                               "[RELDT], " & _
                               "[RTNG], " & _
                               "[KYWRD1], " & _
                               "[KYWRD2], " & _
                               "[KYWRD3], " & _
                               "[KYWRD4], " & _
                               "[KYWRD5] FROM PartsQry WHERE (" & _
                                                "[KYWRD1] = '" & Keyword(i) & "') OR (" & _
                                                "[KYWRD2] = '" & Keyword(i) & "') OR (" & _
                                                "[KYWRD3] = '" & Keyword(i) & "') OR (" & _
                                                "[KYWRD4] = '" & Keyword(i) & "') OR (" & _
                                                "[KYWRD5] = '" & Keyword(i) & "')"
            PartsDA = New OleDb.OleDbDataAdapter(SelectStr, ConStr)
            Try : PartsDA.Fill(PartsDS, "KeywordList") : Catch : End Try
        Next
    End Sub

Say the user enters the keywords "black, loud, small" and hits the search button. The QryKeyword loops through the keywords one at a time. Plugs them into the select string then delivers the records to the PartsDS table "KeywordList".

My problem is comming from my logic because im just appending the 2nd and 3rd set of records to this table. I need ONLY the records that contain all of the keywords that are typed into the search.

I dont know how much of that made sence but hopefully someone can shed some light on this problem. Ive been spinning my wheels for about 8 hours now between playing around with the code and googling.
Thanks in advance

P.S. Incase anyone is wondering. Im declaring alot of the variables like dataset/adapters globaly and referencing them from outside the class

 

[Menthos]

That's a pretty poor database setup you're working with, and will (is) going to make you unnecessarily jump though hoops with both your VB and SQL to get the results you need.

What happens currently if the user enters the keywords in a different order to how you have them stored? are you expecting to loop through each record, with each keyword to get a match?

A much better approach would be to split out your keywords into a separate, indexed table and then either use a single field in your 'PartsQry' table tying to the index of a related keyword, or use a separate table to hold the 'PartsQry' index against keyword indexes. You could then achieve your keyword lookup with a single query.

Just an idea of course - you may have constraints that mean you have to use that db structure.

 

[jasonzook]

Well I am not against changing my database structure. Im still semi new to working with database and want to nip bad habits in the bud.
Can you go into a little more detail on your suggestion?

 

[Menthos]

Ok, well off the top of my head... three tables - Products, Keywords and a lookups

Products would have a single record for each product type, along with an index

Keywords would have the same - a single record for each unique keyword, along with an index

The lookup would tie a product to a keyword... so there could be multiple records with the same product ID, but different keyword ID's

An attempt at an example (although portraying tables through text always looks screwy)

Products

ID Name
1 Granite
2 Daffodil
3 Sandstone
4 ...

KeyWords

ID Keyword
1 Rock
2 Hard
3 Flower
4 stone

Lookups

ProductID KeyWordID
1 1
1 2
1 4
2 3

Does that make sense? You'd then get your resulting recordset based on the entered keywords with something along the lines of

SELECT DISTINCT(p.Name) FROM Lookups l 
INNER JOIN Products p ON p.ID = l.ProductID 
INNER JOIN Keywords k ON k.ID = l.keywordID
WHERE k.keyword in ('rock','stone')

Not checked that - apologies for any typos, but you should get the gist.

 

[jasonzook]

yeah i see what you are saying. I will try this when i get home from work and let you know how it goes

 

[jasonzook]

I am having problems understanding your first line of example code. It looks like your trying to select a field (Name) from a table that doesnt carry that field (Lookups). Maybe this is my inexperience talking but can you do that?

EDIT: Never mind I figured out what I was doing wrong. The only problem now is using "IN" in the WHERE clause. I need my results to have atleast ALL of the keywords entered. Im currently looking for a way to make that "OR" logic into "AND" logic. After that loose end gets tied off I think I'll be good.