D
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.
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.