Is a query on a word in a memo field possible ?

[Steve]

Hi,
For subject matter covered in my book collection, I have a field which in
table design view shows as type MEMO, into which in form view I copy/paste
keywords from a combo list of all the keywords I have in use, to ensure I
use the exact same spelling etc each time. I separate them in the large
window with commas.
There are hundreds of keywords to choose from so having a tick box type
field for each one would be impractical, also I am forever adding them.
I click in the box in form view and do a ctrl F and type the keyword, then
visually can see which books have that keyword.

If I want to create a report of these, I would need to run a query on that
field, then base the report on that query. However typing in the particular
keyword in Criteria sees speech marks surround it and running it sees nil
return. How can I call up records which have a certain keyword in that
field, and create a report of the book titles ?

Note the keyword can be a singular word, or made of two or three words with
spaces, I need to query both types.

Steve

 

[Marshall Barton]

For subject matter covered in my book collection, I have a field which in
table design view shows as type MEMO, into which in form view I copy/paste
keywords from a combo list of all the keywords I have in use, to ensure I
use the exact same spelling etc each time. I separate them in the large
window with commas.
There are hundreds of keywords to choose from so having a tick box type
field for each one would be impractical, also I am forever adding them.
I click in the box in form view and do a ctrl F and type the keyword, then
visually can see which books have that keyword.

If I want to create a report of these, I would need to run a query on that
field, then base the report on that query. However typing in the particular
keyword in Criteria sees speech marks surround it and running it sees nil
return. How can I call up records which have a certain keyword in that
field, and create a report of the book titles ?

Note the keyword can be a singular word, or made of two or three words with
spaces, I need to query both types.

Use a copy of your keyword combo box to select the keyworf
you want to search for. Then the report's (or form's)
record source query can use the memo field's criteria:

Like "*" & Forms!theform.thecombobox & "*"

BUT, I have to say that you are using the wrong approach.
Instead of using a memo field containing all of a book's
keywords, you should have a bookXkeyword table. This table
would have two fields one with the book primary key value
and the other with the keyword primary key value.

This way you can join the books table to the bookXkeyword
table and search that for the keyword pk value. Because of
indexes, this kind of search will be orders of magnitude
faster than your memo field approach.

This is also a more flexible table structure that will allow
you to do other things that would be very difficult, if not
impossible, with the memo field. (e.g. list all the books
for every keyword)

 

[Steve]

Hi Marshall,
Thanks for the advice, I shall give both methods a go, lot of books though
to redo the forms for. I shall dig out my 'guru' friend to assist with what
you describe, I cant quite picture it yet. you say dispense with the memo
field and have a link to anothetr table. <<This table

would have two fields one with the book primary key value
and the other with the keyword primary key value.

If a book has say 20 keywords, each with a PK value, I am not sure how the
second field copes with such. You say search on the keyword PK value.
Instead of searching on a word, I search on a number, my normal approach to
search on all books with say U boats in, is now a search for PK 65. Hope I
can get my head round this.

No doubt it will become clear when my guru shows me whats involved.

Thanks
Steve

 

[Marshall Barton]

What I was inadequately describing is how a Many-to-Many
relationship is represented by using a "junction" table.
This is your situation because each book can have many
keywords and each keyword can appear in many books. The
junction table then has two Many-to-One relationships, which
are easy to deal with by Joining the tables in a query.

The bookXkeyword table is the junction table and neither the
books table nor the keywords table has a foreign key. The
junction table has two foreign keys so any book/keyword
combination can locate both the book and the keyword easily.

To find all the keywords in a book, just filter the junction
table for the book pk. Your original question would be
answered by filtering the junction table on the keyword pk
(using the search keyword combo box as the criteria).

Your UI would then be a book form with a continuous subform
based on the bookXkeyword table. The subform would only
display only one control, the keywords combo box. The book
foreign key field can be bound to an invisible text box and
is the Link Child Field, so all is clean, simple and quick.

Once you wrap your head around Many-to-Many relationships,
this is all very quick and easy and you may also want to
create a keyword form with a continuous subform that
displays all the books using the keyword.

If you have a ton of memo fields to reenter, you may want to
write a procedure that parses the keywords out of the memo
fields and populate the junction table.

 

[Steve]

Marshall.
Now that makes more sense to me, and will assist greatly with the creation
of this solution.
May be just what I have been in need of but unaware of.

All will become clear as we do it.
Many Thanks for this indeed, greatly appreciated.
Could be useful to me for my other databases.
I have other situations where. > ...each book can have many

keywords and each keyword can appear in many books. but for other

instances.

Steve

What I was inadequately describing is how a Many-to-Many
relationship is represented by using a "junction" table.
This is your situation because each book can have many
keywords and each keyword can appear in many books. The
junction table then has two Many-to-One relationships, which
are easy to deal with by Joining the tables in a query.

The bookXkeyword table is the junction table and neither the
books table nor the keywords table has a foreign key. The
junction table has two foreign keys so any book/keyword
combination can locate both the book and the keyword easily.

To find all the keywords in a book, just filter the junction
table for the book pk. Your original question would be
answered by filtering the junction table on the keyword pk
(using the search keyword combo box as the criteria).

Your UI would then be a book form with a continuous subform
based on the bookXkeyword table. The subform would only
display only one control, the keywords combo box. The book
foreign key field can be bound to an invisible text box and
is the Link Child Field, so all is clean, simple and quick.

Once you wrap your head around Many-to-Many relationships,
this is all very quick and easy and you may also want to
create a keyword form with a continuous subform that
displays all the books using the keyword.

If you have a ton of memo fields to reenter, you may want to
write a procedure that parses the keywords out of the memo
fields and populate the junction table.
--
Marsh
MVP [MS Access]

Thanks for the advice, I shall give both methods a go, lot of books though
to redo the forms for. I shall dig out my 'guru' friend to assist with what
you describe, I cant quite picture it yet. you say dispense with the memo
field and have a link to anothetr table. <<This table
If a book has say 20 keywords, each with a PK value, I am not sure how the
second field copes with such. You say search on the keyword PK value.
Instead of searching on a word, I search on a number, my normal approach to
search on all books with say U boats in, is now a search for PK 65. Hope I
can get my head round this.



"Marshall Barton"wrote

 

[Michael Gramelspacher]

Hi,
For subject matter covered in my book collection, I have a field which in
table design view shows as type MEMO, into which in form view I copy/paste
keywords from a combo list of all the keywords I have in use, to ensure I
use the exact same spelling etc each time. I separate them in the large
window with commas.
There are hundreds of keywords to choose from so having a tick box type
field for each one would be impractical, also I am forever adding them.
I click in the box in form view and do a ctrl F and type the keyword, then
visually can see which books have that keyword.

If I want to create a report of these, I would need to run a query on that
field, then base the report on that query. However typing in the particular
keyword in Criteria sees speech marks surround it and running it sees nil
return. How can I call up records which have a certain keyword in that
field, and create a report of the book titles ?

Note the keyword can be a singular word, or made of two or three words with
spaces, I need to query both types.

Steve

I have a death notice/obituary database with about 4,000 records. The text of
the obituary is in a memo field. Using a routine by Ken Sheridan I indexed
every word that was four or more characters long. My keyword index
is more than 200,000 rows. Searching 2 or 3 words is under a second against
the index. Using a wildcard search with the same 2 or 3 words is nearly as
fast. I dropped the keyword search, because a wildcard search is almost as
fast with this small database, and I really need to search for parts of words.
Maybe you have thousands of books and need a keyword index.
I encourage you to continue with your keyword index. My comments are only for
general information.

 

[Steve]

Many thanks,
Noted.
Where might I find the Sheridan routine, sounds useful.
Steve

 

[Michael Gramelspacher]

Many thanks,
Noted.
Where might I find the Sheridan routine, sounds useful.
Steve

Google Groups for Access Sheridan Extracting Keywords

 

[Marshall Barton]

(e-mail address removed) says...

Isn't that only useful when you have the whole text of what
you want to search?

My understanding of Steve's situation is that he has book
titles and a list of keywords he assigned to each book so
searching for something like "*happy bithday*" is not a
meaningful operation.

 

[Michael Gramelspacher]

Isn't that only useful when you have the whole text of what
you want to search?

My understanding of Steve's situation is that he has book
titles and a list of keywords he assigned to each book so
searching for something like "*happy bithday*" is not a
meaningful operation.

Searching for the phrase "happy birthday" as keywords happy and birthday may or
may not be a meaningful search. To me it depends on whether the OP must have
only records with those two words together in that order. If having to select
from a list of records containing the two words in any position will not cut
it, then it is not meaningful. If my suggestion will not work for the OP, then
the OP can just cast it off and move on.

 

[Marshall Barton]

Searching for the phrase "happy birthday" as keywords happy and birthday may or
may not be a meaningful search. To me it depends on whether the OP must have
only records with those two words together in that order. If having to select
from a list of records containing the two words in any position will not cut
it, then it is not meaningful. If my suggestion will not work for the OP, then
the OP can just cast it off and move on.

But, isn't the point that the data records must contain the
text to be searched. The way I'm looking at this is if
there is only a list of assigned keywords, then wildcard
searching is not a viable option beause the text to search
is in a bookcase.

 

[Michael Gramelspacher]

But, isn't the point that the data records must contain the
text to be searched. The way I'm looking at this is if
there is only a list of assigned keywords, then wildcard
searching is not a viable option beause the text to search
is in a bookcase.

You are correct. If you search for "happy birthday", then you want all records
containing "happy birthday" and no records not containing "happy birthday". If
that is what the OP wants, then so be it.