Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-21-2019, 07:24 PM   #1
JVH
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
JVH is on a distinguished road
Multi word record search.

Ok, I am looking for a way to do a multi word search in an inventory database. It is very large and doing a substring search of the description field with just one word can take up to 30 sec. I built a keyword table that contains a record for every word in the description that is 3 characters or more. The table only contains the keyword and the SKU of the item in the primary table. Doing a search for pan returns all records in about 3 sec. But to do one with 3 keywords would require me to take the result set of the first query and run the second keyword against it and the third against that result set.

I am hoping there is a better way to do this.

JVH is offline   Reply With Quote
Old 08-21-2019, 07:26 PM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,586
Thanks: 50
Thanked 1,050 Times in 1,031 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Multi word record search.

Hi. Welcome to AWF! So, are you saying you want a restrictive search? Basically, it's like you want to combine the search keywords with an AND operator, correct?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-21-2019, 07:42 PM   #3
JVH
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
JVH is on a distinguished road
Re: Multi word record search.

I want to type "copper sauce pan" in the search field.
I can already parse it into KW1=copper, kw2=sauce kw3=pan.
I want a result set of SKU numbers from my keyword table.
The keyword table is 2 million records.
right now I can only get a result set from one keyword.
There may be 100 SKUs with pan in the keyword table
and 30 that have sauce in the keyword
and 700 that have copper in the keyword.
But only 3 that have all three, in no particular order.

JVH is offline   Reply With Quote
Old 08-21-2019, 07:47 PM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,395
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Multi word record search.

do you want to search that match "any" of the keywords, or "must" have all the keywords regarding of its position?
does your table keyword has corresponding sku on it?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-21-2019, 07:50 PM   #5
JVH
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
JVH is on a distinguished road
Re: Multi word record search.

I want to be able to type copper sauce pan and only get the three that match all of the keywords from the search field.

I might start out with sauce pan and get 25 matches and then to narrow it down re search with copper added to the search.
JVH is offline   Reply With Quote
Old 08-21-2019, 07:58 PM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,395
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Multi word record search.

have your search table have index on the keyword field.
you can create a query for the search.
Code:
select product.sku, product.[product name] from productTable where product.sku in 
     (select sku from keywordTable where 
          Instr(1, " " & [searchKey] & " ", " " & keywordTable.keyword & " ") > 0);
if the [searchKey] is a textbox in a Form:
Code:
select product.sku, product.[product name] from productTable where product.sku in 
     (select sku from keywordTable where 
          Instr(1, " " & [Forms]![FormName]![searchKey] & " ", " " & keywordTable.keyword & " ") > 0);
__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 08-21-2019 at 08:06 PM.
arnelgp is offline   Reply With Quote
Old 08-21-2019, 08:10 PM   #7
JVH
Newly Registered User
 
Join Date: Aug 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
JVH is on a distinguished road
Re: Multi word record search.

I already have a search that works for one keyword just fine.
The problem is combining 3 keywords.
Attached Images
File Type: jpg CSEARCH.JPG (49.9 KB, 6 views)
File Type: jpg CRESULT.jpg (99.1 KB, 3 views)

JVH is offline   Reply With Quote
Old 08-21-2019, 08:34 PM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,395
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Multi word record search.

i get it all words must be present:
create this function in a Module:
Code:
Public Function CountWords(ByVal strText As String, Optional ByVal strDelim As String = " ")
    Dim i As Integer
    Dim p As Integer
    p = InStr(1, strText, strDelim)
    While p > 0
        i = i + 1
        p = InStr(p + 1, strText, strDelim)
    Wend
    CountWords = i + 1
End Function
call the function in the query:
Code:
select sku from skuKeyTable 
where (Instr(1, " " & [searchKey] & " ", " " & skuKeyTable.keyword & " ") > 0)  
group by sku having 
Count("1")=CountWords([searchKey]," ");
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-21-2019, 09:21 PM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,105
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multi word record search.

Post 7 was moderated, I'm posting to trigger email notifications.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 08-22-2019, 12:49 AM   #10
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,601
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Multi word record search.

The only sensible way forward on this is to move the backend to SQL Server and use the Full Text Search functionality. The server does all the indexing automatically and supports contextual searching.

Queries can define the maximum distance apart the words can be to match and use grammatical variants of the words. Common words are excluded in stop lists which can be system of user defined.

Any column as well as whole documents in dozens of file types can be catalogued in indexes. File tables allow the documents to be stored in a standard Windows file share without any processing required to import them to the database.

FTS is supported in the free SQL Server Express version albeit with some caveats. Read up before embarking on the install.

Last edited by Galaxiom; 08-22-2019 at 12:57 AM.
Galaxiom is offline   Reply With Quote
Old 08-22-2019, 05:01 AM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,127
Thanks: 81
Thanked 2,009 Times in 1,957 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Multi word record search.

JHV,
Can you show us a screenshot of your tables and relationships?
What sort of PC are you using?
Have you reviewed and ensured you have fields indexed?
Even a copy of the database would be helpful.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-22-2019, 05:29 AM   #12
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,601
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Multi word record search.

Quote:
Originally Posted by jdraw View Post
Have you reviewed and ensured you have fields indexed?
Indexes don't help searching for words in a field.

What JHV has done building an catalog of the words is effective. Then index the catalog so it works efficiently.

This is exactly what a Full Text Catalog does in SQL Server. Just like a search engine.
Galaxiom is offline   Reply With Quote
Old 08-22-2019, 05:53 AM   #13
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,127
Thanks: 81
Thanked 2,009 Times in 1,957 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Multi word record search.

Words in a field -- I agree. We used to make a list of Ignore words, then parse names or descriptions to get their component parts (in one case it was Company name - legal, operational, old name, aliases). Then populate a table with companyID and namekeyword records minus any ignore words/strings.
The search was done on this table. In fact there was an algorithm that included phone number and zip/postal code and related points to assist users in identifying possible duplicates. Above a certain points threshold, manual intervention determined whether a record was new or mis-typed - could involve contacting the company for details.
I'm not against sql server express, just determining what JVH has as a working toolset at the moment.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-22-2019, 06:41 AM   #14
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,601
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Multi word record search.

Quote:
Originally Posted by jdraw View Post
I'm not against sql server express, just determining what JVH has as a working toolset at the moment.
Building catalogs in Access is like trying to reinvent the wheel and ending up with a square one.

Full Text Catalog is so easy in SQL Server. The effort converting the back end will be far less than what's required to build catalogs, code and stop lists in Access. Plus contextual search. Good luck trying to do that in VBA.

Moreover the performance in SQL Server will put anything done in VBA in the shade.
Galaxiom is offline   Reply With Quote
Old 08-22-2019, 06:53 AM   #15
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,127
Thanks: 81
Thanked 2,009 Times in 1,957 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Multi word record search.

Galaxiom,
As I said, I'm not against Sql Server Express. Are you telling JVH --"drop Access, learn and install SQL Server Express for this application; It's impossible with Access."
Or are you suggesting that Sql Server Express may be a more ideal candidate for this application.

We don't know and JVH has not identified his environment. I agree there are better options than Access, but what are the parameters and needs he is working with?

JVH if sql server express is on your plate as an option, there are several youtube videos on the Full Text Search identified with this Google search
https://www.google.com/search?q=youtube+Full+Text+Catalog+sql+server+expr ess&ie=utf-8&oe=utf-8

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi Word Search Weekleyba General 16 01-24-2018 05:44 AM
Multi-Word Type as you search box in Main Form LoreSanz Forms 6 10-26-2012 06:41 AM
multi-word search gabrielsoy Queries 4 09-23-2008 11:42 AM
Create a multi word search query Boo Queries 1 12-24-2005 06:20 PM




All times are GMT -8. The time now is 09:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World