UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Count Number Of Consecutive Words Between 2 Strings, Access 2013    
 
   
SoTrue
post Jan 11 2017, 11:04 AM
Post#1



Posts: 311
Joined: 17-April 12



Hi All,

My journey moves on and i am now working on a new part of analysis.

I have 2 strings, e.g EventDesc and FailureDescription

I want to split the EventDesc string then loop through adding a new word each time to see if it is contained within FailureDesc. So for example

CODE
EventDesc = "VCU on remote end in own unit faulty "

SplitCount = Split(EventDesc, " ")

Count = 0

For Counter = 0 to Ubound (SplitCount) //Loop through SplitCount increasing words each time.

    //Do something with result
   If Word does not exist goto next

   Else if word does exist count = count +1
  
  Add next word to string

Next



If anyone could maybe make some suggestions as to the best way to do it, that would be most appreciated. I hope I've made sense?

Thank you all once again.

Regards
Donna
Go to the top of the page
 
jwhite
post Jan 11 2017, 11:19 AM
Post#2


UtterAccess VIP
Posts: 5,801
Joined: 31-August 06
From: North Carolina, USA


Here is a demo that will return count of 3 based on given strings:
CODE
Public Function WordCountInAnotherString(Optional strEventDesc As String = "", Optional strFailureDesc As String = "") As Integer
    Dim a As Integer
    Dim intCount As Integer
    Dim aryEventDesc() As String
    
    If strEventDesc = "" Then strEventDesc = "This is a remote unit VCU"
    
    'Space before/after added to insure whole word matching
    If strFailureDesc = "" Then
        strFailureDesc = " VCU on remote end in own unit faulty "
    Else
        strFailureDesc = " " & strFailureDesc & " "
    End If
    
    aryEventDesc() = Split(strEventDesc, " ")
    
    For a = 0 To UBound(aryEventDesc())
        If InStr(strFailureDesc, " " & aryEventDesc(a) & " ") > 0 Then
            intCount = intCount + 1
        End If
    Next
    
    WordCountInAnotherString = intCount
    
End Function

--------------------
Go to the top of the page
 
BruceM
post Jan 11 2017, 02:10 PM
Post#3


UtterAccess VIP
Posts: 6,663
Joined: 24-May 10
From: Downeast Maine


One possibility may be to compare the length of the string to the length of the string with the word replaced by "".

Len(MyString) - Len(Replace(MyString,"Test",""))

If the difference is 8, the word appears twice. However, it would count "testing" as being the word "test".

I don't quite understand what you're trying to do, but just wanted to mention this in case it helps.
Go to the top of the page
 
SoTrue
post Jan 16 2017, 08:11 AM
Post#4



Posts: 311
Joined: 17-April 12



Hi Guys,

Sorry for the delayed response, I have been working on something else. Anyway, I have created a loop using the first code snippet my jWhite (thank you):

CODE
strEventDesc = rs4.Fields("EventDesc")
                        strFailureDesc = rs3.Fields("Problem-Incident-Description")
                        intCount = 0
                        
                        If strEventDesc = "" Then strEventDesc = rs4.Fields("EventDesc")
                        
                        'Space before/after added to insure whole word matching
                        If strFailureDesc = "" Then
                            strFailureDesc = rs3.Fields("Problem-Incident-Description")
                        Else
                            strFailureDesc = " " & strFailureDesc & " "
                        End If
                                            
                        aryEventDesc() = Split(strEventDesc, " ")
    
                        For a = 0 To UBound(aryEventDesc())
                            If InStr(strFailureDesc, " " & aryEventDesc(a) & " ") > 0 Then
                                intCount = intCount + 1
                            End If
                        Next


Which works brilliantly, what I would like to do is modify it slightly so rather than simply moving on to the second word, it adds the second word to the loop, then the third word....etc. For example:

If strEventDesc = Cab has been coupled and strFailureDesc = Cab Door Fault then on the first loop aryEventDesc (a) = Cab, on the second loop areEventDesc (a) = Cab has, Third loop = Cab has been..... and so on.

I'm not sure how to proceed with this, so any more pointers would be most appreciated.

Regards
Donna
Go to the top of the page
 
BruceM
post Jan 16 2017, 10:07 AM
Post#5


UtterAccess VIP
Posts: 6,663
Joined: 24-May 10
From: Downeast Maine


I'm puzzled by something. You have:

strEventDesc = rs4.Fields("EventDesc")

A few lines later:

If strEventDesc = "" Then strEventDesc = rs4.Fields("EventDesc")

If the EventDesc field contains a zero-length string (ZLS) the first time you assign a value to strEventDesc, what is the reason for re-assigning the same value to the same string? Also, note as a general observation that null and ZLS are not the same.

I'm not sure I understand what you are trying to do in the loop. It looks as if when you want to include the whole string after a key word is located. Is that correct?

Also, I'm not sure the test of aryEventDesc will work properly if the word being sought is the first or last word in the string, because you are including both leading and trailing spaces in the InStr expression.
Go to the top of the page
 
SoTrue
post Jan 17 2017, 06:05 AM
Post#6



Posts: 311
Joined: 17-April 12



Hi BruceM,

I've been working on the code soince then. All I want to do is build up to the full string word by word and count the highest number of consecutive words. I have modified the code to:

CODE
intCount = 0
                        EventLoop = ""
                        TotalWords = 0
                        MatchWords = 0
                        
                        If strEventDesc = "" Then strEventDesc = rs4.Fields("EventDesc")
                        
                        'Space before/after added to insure whole word matching
                        If strFailureDesc = "" Then
                            strFailureDesc = rs3.Fields("Problem-Incident-Description")
                        Else
                            strFailureDesc = " " & strFailureDesc & " "
                        End If
                        
                                            
                        aryEventDesc() = Split(strEventDesc, " ")

    
                        For a = 0 To UBound(aryEventDesc())
                        
                            If a = 0 Then
                                EventLoop = "LIKE *" & EventLoop & " " & aryEventDesc(a)
                            Else
                                EventLoop = " " & EventLoop & " " & aryEventDesc(a) & " "
                            End If
                            
                            If InStr(strFailureDesc, EventLoop) > 0 Then
                                intCount = intCount + 1
                            End If
                            
                            TotalWords = TotalWords + 1
                        Next
                        
                        MatchWords = MatchWords + intCount
                    
                        Debug.Print strEventDesc, strFailureDesc, "MatchWords = " & MatchWords
                        Debug.Print "IntCount = " & intCount, "TotalWords = " & TotalWords


As you said there may be issues if the word is at the beginning so I have added LIKE into the loop, but this does not seem to be working. I'll keep fiddling with it and see where i get.

Regards
Donna
Go to the top of the page
 
BruceM
post Jan 17 2017, 07:43 AM
Post#7


UtterAccess VIP
Posts: 6,663
Joined: 24-May 10
From: Downeast Maine


QUOTE
If InStr(strFailureDesc, EventLoop) > 0 Then

Since EventLoop always starts with "LIKE *" you are looking for the presence within strFailureDesc of a literal text string starting with "LIKE *". Is that really how the text appears in Problem-Incident-Description?

Perhaps you should assemble the EventLoop string without the "LIKE *". You could add it when you are done, but I don't see the need since it isn't being used as a Where condition for either SQL or a domain expression such as DCount.

Instead, rather than the "If a = 0" condition, replace that If block with:

EventLoop = " " & EventLoop & " " & aryEventDesc(a) & " "

If you want to count the number of words in EventLoop you can count the number of spaces, and add LIKE to the beginning:

intCount = Len(EventLoop) - Len(Replace(EventLoop," ","")

Based on the code you have posted MatchWords is always the same as intCount and TotalWords is always 1. I assume there is more code that fills in those details, but of course I can't comment on that one way or the other.

In an earlier posting you wrote:

QUOTE
If strEventDesc = "Cab has been coupled" and strFailureDesc = "Cab Door Fault" then on the first loop aryEventDesc (a) = Cab, on the second loop areEventDesc (a) = Cab has, Third loop = Cab has been..... and so on.

Are you saying that if the word "Cab" appears in strFailureDesc you want strEventDesc from that word onward? If so, what if the two strings share more than one word? It is a little difficult to picture the desired end result.
Go to the top of the page
 
SoTrue
post Jan 17 2017, 08:05 AM
Post#8



Posts: 311
Joined: 17-April 12



BruceM,

Basically, I have standard text strings and a user input field. Thus i am trying to see if the standard text field (or part of it) is found within the user inputted field. I have already created some code which checks for the presence of the entire standard text string (EventDesc) as a whole, so what i need to do is check for consecutive parts of the standard text string within the user input field as the user may not always use the exact text as the EventDesc. For example:

EventDesc = "Cab has been coupled"
strFailureDesc = "This cab has failed to aux on"

So, the first cycle of the loop EventLoop = "cab" and eventMatch will = 1 as the first word was found.
The second cycle EventLoop = "cab has" and eventMatch will = 2 as both the first word and second word was found,
The third cycle EventLoop = "cab has been" and eventMatch = 2, as there are still only 2 consecutive words and so on until the end of the loop, as there are not any more consecutive words.

In hindsight, I will need to check for multiple copies of the first word and loop through again if it occurs more than once, thank you for pointing that out.

I'm clutching at straws utilising various methods of string matching to try and correlate user input failure information to standard error reported by a system. I have already used Levenstein distance and another string matching, as well as checking for the presence of the entire EventDesc. So now I am trying to look for parts of the EventDesc so I may nee to create another loop surrounding this loop that changes the first word to the second then checks again, then the second to the third.

Regards
Donna
Go to the top of the page
 
BruceM
post Jan 17 2017, 08:29 AM
Post#9


UtterAccess VIP
Posts: 6,663
Joined: 24-May 10
From: Downeast Maine


Are you saying that since "Cab has" appears in both strings the result is 2?

I'm not sure I have any more suggestions. To be honest, I have no real understanding of why you are trying to do this, or what use you will make of the result. I'm not saying it is not valid, but only that I still don't understand.

However, I still think that counting the number of spaces (or spaces plus 1 if there is no leading or trailing space) may be a more reliable way of counting the number of matching words than trying to manage an incremented integer.

I am not very familiar with regular expressions, but I wonder if it is a tool that could help in this situation.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th July 2017 - 03:41 PM