Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching Memo Fields (2000)

    I am creating a database of technical articles and will need to offer the users a way of searching the synopses to find key words. I cannot guarantee that the sysnopses will be less than 255 characters so I am going to have to put them into a memo field, like the articles themselves. Memo fields cannot be indexed (and I'm not sure that indexing will help that much when the searches will all use the Like "*keyword*" syntax anyway).

    Can anybody recommend any strategies to adopt? Is there something better than Like "*keyword*" ? The database will be accessed over the Web and I wonder how people like Google do it.

    Ian

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Searching Memo Fields (2000)

    In the case of Google and similar products, they use a complex search engine to create a pointer to web pages. MS Office also does something similar but much less sophisticated in the background so you can do searches of text documents on your local workstation and not take forever.

    In your case, I think you are probably stuck with creating keyword entries manually if you are going to try to use a keyword approach to speed things up. You could do that by creating a keyword table with a linking table to indicate that keyword A can be found in article B. If you have a few hundred articles, that approach is probably workable, but if it's several thousand it could be out of the question. And I don't really think an index on the synopses would help much with searches. There is an alternative that will let you use indexes on larger fields - SQL Server 2000 (and the latest version of the MSDE) supports text fields up to 8000 bytes, and they can be indexed. When you link to such a table, Access still treats it as a memo field, so some weird things happen if you start using aggregate queries and the like, but it can be a a useful trick to have in your kit.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching Memo Fields (2000)

    The separate ketword tables idea sounds good. Do you mean:

    Table A: Keyword (text), KeywordID (Autonumber)
    TableB: KeywordID(Long), ArticleID (Long)

    If so, wouln't this be scaleable to cope with several thousand articles? The keyword table (A) would eventually stablise at a few hunded, maybe a few thousand records. If the synopses were 30 to 60 words each, probably half of these could be ignored (and, if, but etc.) leaving 20 to 30 keywords. This would mean 20,000 to 30,000 records in table B for each 1000 articles. These would be very small records (two Long fields) so would not take up much space. Am I right in thinking that Access is very good at searching on indesed Long fields?

    Ian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Searching Memo Fields (2000)

    It would work very well, Access has no problem at all with tables of this size. But filling table B would be a huge effort.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Searching Memo Fields (2000)

    Hans said it right - there would be lots of work in selecting the keywords. If you are dealing with technical words, you could conceivably pick the set of keywords in advance, presuming you know many of the terms people will be looking for, and run a set of queries on the synopses to build the initial links. Then you could let people review those and add additional keywords as well. That approach should work even with memo fields. Still a good deal of work however.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •