one simple criteria input box - google style

[djc]

I am looking for guidance on ways to accomplish implementing one simple
input box for user criteria (like www.google.com) that will be used to
search multiple fields for matches. I am currently thinking of 2 different
approaches. Please note my question lies in how to structure the
tables/query, not getting and parsing the input. (Although I may find myself
back here again when I get to that, hehe) Whichever way I wind up going I
will have a seperate function to parse the user input in a way similar to
google/yahoo. User will type in multiple keywords seperated with spaces.
Spaces will represent logical ANDs etc...

Example1: say a I have tblMain with fields like:

MainID
Title
Detail
Keyword1
Keyword2
Keyword3


After parsing input into a bunch of seperate keywords I would construct an
SQL statement something like this:
SELECT * FROM tblMain
WHERE (parsedKW1 Like Title OR parsedKW1 Like Keyword1 OR parsedKW1 Like
Keyword2 OR parsedKW1 Like Keyword3) AND (parsedKW2 Like Title OR parsedKW2
Like Keyword1 OR parsedKW2 Like Keyword2 OR parsedKW2 Like Keyword3) etc...

parsedKW1 and parsedKW2 being obtained from the users input from the one
input box. Two keywords seperated with a space.

I think this would work but I would be limited to the number of keywords I
put in this table. This is the first way I thought of. I don't know if this
is a good way or not.

Example2: two tables with this idea:

tblMain:
MainID
Title
Detail

tblKeywords:
MainID
Keyword

This table structure would allow me to enter as many keywords as I want
which can link to the main record via the MainID field. I have just started
thinking about this one... would I be able to use an SQL statement to
acheive this way or would I need to use VBA/ADO style looping procedures to
loop through recordset comparing keywords to keyword entries?

any input is appreciated. Other examples would be appreciated as well.

 

[[MVP] S.Clark]

I would not structure any table, that is needed for a query, with a
repeating group(such as you have done with Keyword1-3). Instead, it should
be created a child table.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[djc]

yes, I was assuming that would be the preferred method. Just to clarify, you
are refering to how I have it in example2 right?

any input on the actual query options doing it this way?

thanks for the reply.