How to create a combined query?

[ekje]

Hi,

It's about a database/knowledge base (Access database) with 4 keywords
in order to search subjects/topics in the knowledge base.
My question is how to create a combined query with 4 keywords (each keyword has own field, -> see attachment).
With a combined query I mean:
I am able to do requests with only one keyword or two keywords or three keywords or with 4 keywords. And the order of the entered keywords are free.

Please reply me as soon as possible.
Thank you very much for your quick reply

 

[MajP]

Those keywords should all be in a separate table linked by primarykey. If you cannot fix that I would build a union query of the keywords and the PK. Then join the union query to your table by the PK. Then you can type in a keyword and will find any match.

If you stored keywords in a related table, you could have as many keywords as you wanted and querying would be easy.

 

[MajP]

The union query would look like

Select PrimaryKeyAutoNumber, KeyWord1_Finnish as KeyWord from KFM_Knowledgebase
UNION
Select PrimaryKeyAutoNumber, KeyWord2_Finnish as KeyWord from KFM_Knowledgebase
UNION
Select PrimaryKeyAutoNumber, KeyWord3_Finnish as KeyWord from KFM_Knowledgebase
UNION
Select PrimaryKeyAutoNumber, KeyWord4_Finnish as KeyWord from KFM_Knowledgebase

 

[ekje]

Hi MajP,

Thank you very much for your query and the union query you built

I created a new table with all the keywords and linked the tables
(KFM_Knowledgebase and tblKeywords, see the attched file))
But how could I add the union query code (SQL codes you built)
to create a union query?

Thank you very much for your reply

 

[arnelgp]

I don't think you need another table.
do you mean any of those search words (separated by space), if there is at least 1 hit it will show the record?
if so, just concatenate the fields and split the search words.
say you have a textbox (txtSearch) in a form.
the keywords you want to search are separated by a single space:

Dim varSearchWords as variant
Dim strSearch As String
Dim strConcat As String
Dim i As Integer
strConcat = "Instr([Keyword1_Finnish] &  [Keywod2_Finnish] & [Keyword3_Finnish] &  [Keyword4_Finnish],"
varsearchWords=split([txtSearch], " ")
For i= 0 to Ubound(varSearchWords)
   If i > 1 then
      strSearch = strSearch & " OR "
    End If

    strSearch = strSearch & strConcat & """" & varSearchWords(i) & """" & ")>0"
Next
If strFilter<>"" Then
Me.Filter = strFilter
Me.FilterOn=True
Else
Me.FilterOn=False
End if

 

[Gasman]

arnelgp,

Should the filter be strSearch and not strFilter ?

 

[MajP]

I don't think you need another table.

Ekje,
Sure you can choose to leave this in a Non-normal database design. You can always jump through hoops like this to make things work. The problem is if you want to do more complicated searches, you will have to jump through more complicated hoops instead of performing simple queries. If this is all you ever plan to do fine, but if you want to do things like get the count of records matching 2 keywords, or records with 3 matching keywords, or want to add a fifth.., sixth... keyword then I would properly structure this.

 

[bastanu]

See if the attached "custom filter" would help you. It allows you to apply up to 6 "keywords" to the same field or to separate fields.

Cheers,
Vlad