Multiple Keyword search on one column

[chook]

Hi,

I am very new to access. My knowledge level is very low so far...

I am using Access 2007.

I have a bunch of records and i want to be able to do a Multiple keyword search on one particular column which is called keywords.

I have a single search query which works fine... this is the query:

SELECT Sea.ID, Sea.Category, Sea.Title, Sea.Author, Sea.Organisation, Sea.Date, Sea.Keywords, Sea.Reference
FROM Sea
WHERE (((Sea.Keywords) Like "*" & [Search by word:] & "*"));

This will find all records that have the word I am searching on and display the selected columns as well as the keywords column.

However in the keywords column each record has multiple keyword entries.. for example: (I had an image for this but i cant post it till i have ten posts ><)

ID Category Title Author Organisation Date Keywords
1 test test test test 1993 dog, cat, donkey, test Test
2 test test test test 2003 cat, budgie, rabbit
3 test test test test 2003 meow, rabbit, test, monkey
4 test test test test 2008 elephant, meow, donkey
5 test test test test 2009 giraffe, zebra, test, dolphin

I want to be able to use a multiple keyword search query where i enter say:

rabbit,test

And any record that is in the keyword column that has rabbit and/or test in it is returned.. So in the example above I would get records returned from my table:

ID Category Title Author Organisation Date Keywords
1 test test test test 1993 dog, cat, donkey, test Test
2 test test test test 2003 cat, budgie, rabbit
3 test test test test 2003 meow, rabbit, test, monkey
5 test test test test 2009 giraffe, zebra, test, dolphin

Record 4 would be omitted from the returned result as it contains neither Rabbit and/or test in the keyword column

Can anyone help me with a Where statement that would help me achieve this?

EDIT: Apologies... I cant get the column spacing to work so it represents it accurately but Keywords has the animal words... i hope you get my drift... oh i now see the sticky so i have attached a picture in zip form.. thanks.

 

[Alansidman]

Look to use the IN function in your criteria.

http://www.techonthenet.com/sql/in.php

 

[chook]

Ok thank you for the link i will have a read.

 

[chook]

Hmm not sure if that will work for me? Could i possibly use the InStr function?

 

[Galaxiom]

It would be a very simple task if you used a related table for the keywords. It would also be a much faster query.

Alternatively one way with the existing structure is to build a dynamic query string in VBA:

WHERE Sea.Keywords Like "*cat*" OR Sea.Keywords Like "*dog*" OR etc etc

 

[chook]

Can anyone explain how i would write this so it comes up with a simple pop up like my single search query.... for example i want to right click and choose open my "Multiple Keyword Search" Query... and it pops up with a simple prompt box... i can then put in "cat, dog, sheep" or even/or with no commas "cat dog sheep" ... Sorry i am having trouble getting it to prompt like the single search query i have.

for example i have:

SELECT Sea.ID, Sea.Category, Sea.Title, Sea.Author, Sea.Organisation, Sea.Date, Sea.Keywords, Sea.Reference
FROM Sea
WHERE Sea.Keywords Like "*dog*" OR Sea.Keywords Like "*cat*" OR Sea.Keywords Like "*Rabbit*"

But i want it to prompt first with a simple box so i can enter the keywords.

Ahh i think i've been working on this too long lol apologies for my noobness.

 

[chook]

Ahh maybe im going the wrong way about this and need to start over ><. Can anyone point me to a way i can link a bunch of keywords to each record and then bring up a prompt which the user will be able to enter multiple words... and these words will be checked against the keywords for that record so that if one or more of the words is linked it will display those records that match the scenario...

Single word search just isn't an option unfortunately..

Maybe a tutorial or video that will get me going in the right direction.

Thanks for everyone's time and help by the way.

 

[chook]

HI again ^^
So i found this on another thread..

I have a form that searches through Item's names based on 2 keyword boxes.
Here's the criteria in my query:

Is Null Or Like "*" & [Forms]![frmItemView]![SearchPhrase1] & "*" And Like "*" & [Forms]![frmItemView]![SearchPhrase2] & "*"

This will show all records when both keywords are blank, and filter records using the 2 keywords otherwise.

Hope this helps,
Evan

This is pretty much exactly what i am looking for however i cannot get it to work!.. I have a form with two text boxes and have set the correct values as outlined in Evans post. I then have the query set to run via a button. I run it but it will only give back records for the entry i have put in the 1st text box. This would work well for me otherwise. Can anyone see how it might be made to work.. or if it does maybe exaplin what i am doing wrong.. mine is like this:

Is NULL or Like "*" & [Forms]![Searchtable]![Key1] & "*" And Like "*" & [Forms]![Searchtable]![Key2] & "*"

Searchtable being my search form
key1 being my first text box entry
key2 being my second text box entry

 

[jdraw]

See this post and the references within. There is a sample database with keyword search. It's a long thread that has bee useful to posters.

www.access-programmers.co.uk/forums/showthread.php?t=240855

 

[chook]

Thank you I will take a looksee

 

[chook]

Jdraw that is exactly what i need.... thank you so much! I am having trouble however in specifying which column it searches... Where do i specify that?.. i am searching but cannot seem to find it. I am not very good with vb i can read some of it but not all. Thank you again.

 

[jdraw]

I have to go out for a couple of hours, but will look at this tonight.
Do you have additional info that can help pin poiint whatever issue/requirement you have?

 

[chook]

Hi, thanks Jdraw.
I have actually got it to search on the column i want now i think i had to modify:

j = 7 ' using description field only ' so it is looking at the particular column i want which falls as the 7th column.

Is this correct?

The trouble i am having now is more with the form... i want all 9 columns of the record returned and to fit correctly into the listbox. I'll try and figure this out as well in the meantime.

 

[chook]

Just want to say thanks again Jdraw id been looking for days and that was just what i needed... sorted all my other problems as well.