How to create a complicated query with four different keyword fields? (1 Viewer)

ekje

Registered User.
Local time
Today, 16:13
Joined
Jan 8, 2014
Messages
34
Hi,

My question is about a complicated Access query.
The knowledge base table has four different keyword fields and other fields.
When entering a new knowledge item, I usually enter 2-4 keywords, one keyword in each keyword field.

If I do a query with four keywords, the structure should be:

Keyword1 AND/OR Keyword2 AND/OR Keyword3 AND/OR Keyword4

The combination of four keywords and AND/OR choices produces a rather complicated query.
The order of the keywords I type could be different compared with the order of the keyword fields.
When I do a query, my first keyword could match e.g. in the field "keyword2" and my second keyword
could match e.g. in the field "keyword1".

How could this kind of query be designed?
Thank you for the answer!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:13
Joined
Oct 29, 2018
Messages
21,358
Hi. To make your life easier, you might consider "normalizing" your table structure. You should split the table into two. The main table should have a child table of keywords, rather than put them in separate columns. What happens if you needed a fifth or sixth, etc. keyword?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:13
Joined
May 21, 2018
Messages
8,463
Sounds like your DB is not normalized making this more complicated then it needs. Keywords should be in a child table related back to the main table.

I would first do a normalizing query.
Select ID, Keyword1 as Keyword From Sometable
Union Select ID, Keyword2 as Keyword from sometable.

now use that query in another query.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:13
Joined
May 21, 2018
Messages
8,463
DBguy beat me. I agree to fix it, but you can normalize using a union query in the mean time.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:13
Joined
May 7, 2009
Messages
19,169
what do you mean by "first" and "second" keyword?
does it mean on Parameter box, you type 1 to 4 consecutive words (separated by space or another delimiter) for your query to find.
you need a special function for that then call in in your query:
Code:
Public Function udfIsKeyWord(keySearch As Variant, key1 As Variant, key2 As Variant, key3 As Variant, key4 As Variant) As Boolean
Dim varArray As Variant
Dim var As Variant
Dim col As New Collection
Dim strToSearch As String
Dim i As Integer
keySearch = keySearch & ""
If Len(keySearch) < 1 Then Exit Function
strToSearch = ("/" + key1 + "/") & ("/" + key2 "/") & ("/" + key3 + "/") & ("/" + key4 + "/") & ""
If Len(strToSearch) < 1 Then Exit Function
strToSearch = Replace(strToSearch, "//", "/")
varArray = Split(keySearch, " ") 'supposing the delimiter is space char
For Each var In varArray
    If Trim(var) <> "" Then
        col.Add Trim(var)
    End If
Next
For i = 1 To col.Count
    If Instr(strToSearch, col(i)) > 0 Then
        udfIsKeyWord = True
        Exit For
    End If
Next
Set col = Nothing
End Function

you then construct your query:
Code:
Select tblKeys.KeyWord1, tblKeys.Keyword2, tblKeys.Keyword3, tblKeys.Keyword4
From tblKeys
Where
udfIsKeyWord([Enter Keyword], blKeys.KeyWord1, tblKeys.Keyword2, tblKeys.Keyword3, tblKeys.Keyword4) = True
 

ekje

Registered User.
Local time
Today, 16:13
Joined
Jan 8, 2014
Messages
34
Hi arnelgp,

Thank you for your good and quick reply!

Attached is the structure of the table "KFM_Knowledgebase".

This table contains information about business accounting and taxation I enter.
The following example shows what I mean.

If there are only two keywords I would enter then the structure of the query would be:

The first keyword I would enter in this example is: "Sales, 25%"

The second keyword I would enter in this example is "Purchases, 25%"

First alternative of the query:

Keyword1_Finnish AND Keyword2_Finnish; this part of the query matches all the records containing both keywords (Sales, 25%", Purchases, 25%)

The result of the query would contain all the records with the value of "Sales,25%" in the field "Keyword1_Finnish" and with the value of Purchases, 25%" in the field "Keyword2_Finnish"

But the data could have been entered on the contrary in some records! The field "Keyword1_Finnish" could contain "Purchases,25%" and "Keyword2_Finnish" could contain "Sales 25%"

So the query structure must take notice of this possible reverse order between the earlier entered keywords in the table and now entered keywords.

And the query structure must take notice of the possibility that only either the field "Keyword1_Finnish" or the field "Keyword2_Finnish contains "Sales 25" or "Purchases 25%".

The keyword3 and keyword4 are missing which makes the query more complicated.

I suppose the function udfIsKeyWord() is able to execute these complicated queries.

Please email me your comments

By the way, is it possible to purchase programminn services for you?

Thank you for your reply.
Best regards
 

Attachments

  • tblKFM_Knowledgebase_table_structure.pdf
    29 KB · Views: 138

ekje

Registered User.
Local time
Today, 16:13
Joined
Jan 8, 2014
Messages
34
Hi arnelgp,

Just a correction to the last line of my email I sent today: the preposition should be FROM, not for!:)

I suppose the function udfIsKeyWord() is able to execute these complicated queries.

Please email me your comments

By the way, is it possible to purchase programming services FROM you?

Thank you for your reply.
Best regards
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:13
Joined
May 7, 2009
Messages
19,169
here is a sample db with the Query (query1) that uses the function. you need to use ; as separator of keywords.

yes, i am available. pm me so i will send you my email.
 

Attachments

  • ComplicatedQuery.zip
    19.8 KB · Views: 136

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:13
Joined
Feb 28, 2001
Messages
27,001
@ekje,

Arnel will probably tell you this or at least suggest it to you, but the "right" way to do this requires table restructuring in Access. If you are not familiar with "Normalization" then you should read about it. Search this forum for "Normalization" or search the Internet for "Database Normalization." If you search the Internet, do your first readings from .EDU sites, because the .COM sites usually have something to sell you. Your KeyWord1, KeyWord2, ... fields are what is called a "repeating group" and this something that should have its own child table.

The other way to do this is to build a union query after which you can run a search against that query.

Code:
SELECT <other information>, KeyWord1 AS KWD FROM <mytable> WHERE NZ( KeyWord1, "" ) <> ""
UNION
SELECT <other information>, KeyWord2 AS KWD FROM <mytable> WHERE NZ( KeyWord2, "" ) <> ""
UNION
SELECT <other information>, KeyWord3 AS KWD FROM <mytable> WHERE NZ( KeyWord3, "" ) <> ""
UNION ...

Then you can search THIS query for KWD equal to whatever you want. You mentioned that there were cases where you wanted two specific keywords but you implied that you wanted to see records that had only one of the two keywords. This would do it.
 

Users who are viewing this thread

Top Bottom