How to create a combined query? (1 Viewer)

ekje

Registered User.
Local time
Today, 04:07
Joined
Jan 8, 2014
Messages
34
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
 

Attachments

  • Query_4_Keywords.pdf
    30.8 KB · Views: 118

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:07
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 07:07
Joined
May 21, 2018
Messages
8,463
The union query would look like
Code:
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

Registered User.
Local time
Today, 04:07
Joined
Jan 8, 2014
Messages
34
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:)
 

Attachments

  • How_to_Create_Union_Query.pdf
    43.1 KB · Views: 115

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,169
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:
Code:
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

Enthusiastic Amateur
Local time
Today, 11:07
Joined
Sep 21, 2011
Messages
14,038
arnelgp,

Should the filter be strSearch and not strFilter ?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:07
Joined
May 21, 2018
Messages
8,463
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

AWF VIP
Local time
Today, 04:07
Joined
Apr 13, 2010
Messages
1,401
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
 

Attachments

  • FBA_CustomFilter.accdb
    992 KB · Views: 120

Users who are viewing this thread

Top Bottom