Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Unanswered: Text Density using a query

    Hi I wondered if there was an easy way to count the density of a text string in another field.

    My example is one field with the word "cheese" in it. I have another memo field with a load of text about "cheese" and need a query to tell me how many words are in the memo field and how many times the other field ("Cheese") appears in this memo field.

    Does any of that make sense?????

    Thanks

    Cheese - I mean Paul

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    check out the instr() function in help

    loop (& count) while you still find "cheese", stepping the start position beyond the last found "cheese" each time.

    you could repeat the process looking for spaces as a crude way to get a word count. bad luck if the memo is filled with long sequences of spaces - for that you would only count <space><nonspace> pairs

    maybe a simultaneous attack would be faster since you only need to sweep the memo once: look for spaces ...look for the first nonspace after each space (count word) ...if it is "c" or "C" ...check for "cheese" ...if it's "cheese" count it. see mid$() in help if you try this route.

    plan a coffee break if this is many records and/or many keywords and/or long memos.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Posts
    10

    Thanks

    Thanks for that reply but that sounds like its beyond my skills, how do i loop back through what would a typical expression look like that did this ?

    Thanks again

    Paul

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    declare Searchword as string
    declare WordCount as integer

    Searchword = "Cheese"

    WordCount = (Len(YourString) - Len(Replace(YourString, Searchword, '')))/Len(Searchword)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Posts
    10

    Again Thanks

    Thanks again for the reply - sorry my access skills etc are very basic - can anyone give me an example of what the expression would actually look like as i am lost....

    Thanks People

    Paul

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by maddz1973
    Thanks again for the reply - sorry my access skills etc are very basic - can anyone give me an example of what the expression would actually look like as i am lost....
    Paul I think you will want to use an Event Procedure to do this and write some code to run a process. This can be done with Visual Basic. Access has a VB editor to create code that can be associated to your form.

    I might suggest using an array to do this. Arrays can be created and iterated through very quickly. Here is a short example to get a word count based on spaces and count the number of times a word appears in the text. Its a simple example to give an idea.

    Code:
    strLngText = "Your long text from a field or dataset defined here"
    
    ArrayWords = Split(strLngText, " ", -1)
    
    nWordCount = UBound(ArrayWords)
    Debug.Print nWordCount
    
    strDefinedWord = "Cookie"
    
    For i = 0 to Ubound(ArrayWords)
    If ArrayWords(i) = strDefiendWord Then
         n = n + 1
    End If
    Next
    
    Debug.Print "The word '" & strDefinedWord & _
                "' appears in the text (" & n & ") times."
    This puts each word into its own space in the array using the 'split' function. Of course you'll still need to do some clean-up like run through the array and remove any 0 length strings before getting the count. To check for a specific word you would loop through the array and count all the matches to your defined word.


    Bill
    Last edited by savbill; 02-05-05 at 18:12.
    ~

    Bill

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    expanding a wee bit on the direction blindman was going...

    see Simple Keyword Relevance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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