Help comparing a string to criteria in another table (1 Viewer)

[cnstarz]

In my database, I use TextStream.ReadLine to read a .txt file line by line and store pertinent parts of each line into specific fields in a table. One of these fields is called "Remarks", which is basically a descriptive paragraph of text explaining a task. Everything works great so far.

However, my leadership would like a condensed version of the "Remarks" field. The only way to really do this right now is for someone to manually read each "Remarks" field and create their own like condensed version of it. Let me give you an example...

Remarks: "Conduct Project Delta tests in association with IBS/SCADA systems and CIKR (Critical Infrastructure/Key Resource) cyberspace terrain, develop CPT certification processes and checklist."

Condensed Version: "Project Delta for IBS/SCADA and CIKR"

I've played with using Select Case to automatically create a condensed version of the Remarks field:

Select Case True
   Case InStr(strRemarks, "SCADA") > 0 and _
      InStr(strRemarks, "Project Delta") > 0:
         !Condensed = "Project Delta for IBS/SCADA and CIKR"
End Select

However, this is too much VBA maintenance for each different thing that needs condensed. If a new tasking comes out, then I'll have to go into VBA and custom create a new Case for it. Multiply that by 10-20x each week.

Instead, I would like a form where my users can specify the criteria themselves. The Remarks field would be compared against the criteria to create a new condensed version of the Remarks field. I'm not sure how to go about this though. What I'm envisioning is this...

So each Remarks string would get compared against each criteria. If the Remarks string contains the words "SCADA" and "Project Delta", then the condensed version would be "Project Delta for IBS/SCADA and CIKR". If the Remarks string contains "OPSEC Assessment" then the condensed version would be "OPSEC Assessment". In the pic above, the form is based of another table that contains those fields in the form. I'm not sure if this is the most efficient method for my goal or not. Either way, I don't know how I would compare the Remarks string to records in this new table in order to create a condensed version.

I hope this makes sense. I'm lost at this point and don't know how to do this or how to implement my intended goal in a more efficient way. Thanks for reading and thanks in advanced for your help!

 

[vbaInet]

However, my leadership would like a condensed version of the "Remarks" field. The only way to really do this right now is for someone to manually read each "Remarks" field and create their own like condensed version of it. Let me give you an example...

1. Has leadership sat down with and gone through a varied range of remarks and told you how they would like it condensed?
2. Can you not get the condensed parts from the source?

In my database, I use TextStream.ReadLine to read a .txt file line by line and store pertinent parts of each line into specific fields in a table.

By the way, how many rows are you reading off on average?

 

[spikepl]

Last year or the previous one there was some teen that got gazillions for code to summarise articles

Beware of "courageous" management ideas (to paraphrase a certain Sir Humphrey) - I once worked on a million project where the management had the idea that we could make an electronic crystal ball to predict the future state of some extremely complex mechanism. They forgot that the ground troops must feed the crystal ball with data and must find the ball useful and believable. So the millions went down the drain

 

[cnstarz]

1. Has leadership sat down with and gone through a varied range of remarks and told you how they would like it condensed?

Nope, they're really too busy with a boatload of other things to sit down and scrutinize exactly what they want seen for each thing. So they put their trust in us to figure something out. If they don't like it, then they let us know.

2. Can you not get the condensed parts from the source?

Sometimes the condensed version can be just small phrase or a few consecutive words from the Remarks string, but 99% of the time it will have to be condensed via human interpretation and analyzing.

By the way, how many rows are you reading off on average?

The .txt file itself can range from 400-1000 lines. It's not very big at all (roughly 25kb in size), and usually the first half of it is stuff that is unusable anyways so it's skipped over until a TextStream.Readline = "//". Once it starts getting to the parsable data, then it creates anywhere from 40-100 records in my table. Not very big.

Edit: My database is on a another network. It's small enough to where I can create a copy right quick by hand on this network and upload it for you guys to see what I'm talking about...

 

[vbaInet]

You guys need to sit down, look through a whole bunch of remarks and work out a business logic... which parts you want to include in the summary. It is from there you would know how best to extract the relevant parts and whether there's a pattern you can use. Right now, you're doing things in reverse.

Alright, I thought it was huge otherwise I would have told you that a TextStream is slower than exporting it using TransferText.

 

[cnstarz]

Here's my database with a sample .txt file. Line 18 is where you specify the location of the .txt file. Once you've edited that, just run the sub and you'll see the data populated in tblTasks.

You guys need to sit down, look through a whole bunch of remarks and work out a business logic... which parts you want to include in the summary. It is from there you would know how best to extract the relevant parts and whether there's a pattern you can use. Right now, you're doing things in reverse.

Alright, I thought it was huge otherwise I would have told you that a TextStream is slower than exporting it using TransferText.

I wish that was an option, but that would require a lot of coordination amoung multiple divisions in our squadron and would require changes in processes and procedures and a lot of time to accomplish.

I know there's no intelligent way to programmatically VBA condense the Remarks field on its own, that's why I was suggesting this other Criteria form that is manually driven by the users; all fields in tblCriteria is manually entered by the user from frmCriteria. If the Remarks field contains all the criteria in a record from tblCriteria, then it would copy the contents of the Result field (from tblCriteria) to the Condensed field (in tblTasks).

 

[vbaInet]

Like I mentioned, doing it shouldn't be much of a problem, the big question is what should be included in the summary for each and every record. That is what you need to work out. Once you've worked that then we can help with your strategy.

 

[cnstarz]

Like I mentioned, doing it shouldn't be much of a problem, the big question is what should be included in the summary for each and every record. That is what you need to work out. Once you've worked that then we can help with your strategy.

The summary will be manually inputted by the user using the Criteria form. The user decides the criteria and the summary.

 

[jdraw]

Since the user is inputting the Summary, can you describe again exactly what the issue is?

 

[vbaInet]

Ok, but I would imagine Leadership don't want to have to enter keywords to be searched for. They want you guys to produce a summary for them. Have you brought this to their attention?

 

[cnstarz]

Since the user is inputting the Summary, can you describe again exactly what the issue is?

What I need is a way to programmatically see if the Remarks string contains all of the text from the two criteria fields for each record in tblCriteria. If there is a record in tblCriteria where the Remarks string contains all the text in that record's two criteria fields, then it will populate the Condensed field in tblTasks with the text from the corresponding Result field from tblCriteria.
Step-by-step example: Let's pretend that strRemarks = "Conduct OPSEC Monitoring on IBC/email systems at Los Angeles, CA; msn number 13-051."

strRemarks is compared to the first record (ID 1) in tblCriteria to see if the string contains all of the text from the Criteria1 ("SCADA") and the Criteria2 field ("Project Delta"). strRemarks does not contain "SCADA" or "Project Delta", so it moves on to the second record (ID 2) in tblCriteria where Criteria1 and Criteria2 are "OPSEC Monitoring" and "Mission", respectively. strRemarks does contain "OPSEC Monitoring" but not "Mission", so it moves on to the third record (ID 3) in tblCriteria where Criteria1 and Criteria2 are "OPSEC Monitoring" and "Msn", respectively. strRemarks does contain "OPSEC Monitoring" and it also contains "Msn". Great! It will now copy the text from ID 3's "Result" field ("OPSEC Msn # <parsed from ReadLine>") from tblCriteria to the "Condensed" field for the new record that is created for that task in tblTasks.

I don't know if I can be any clearer than this lol. The reason why this is important is because there are lots of OPSEC Monitoring tasks with different mission numbers (the mission numbers can be parsed easily, so no worries there). Unfortunately the verbiage is not standardized, so sometimes the tasks will have the word "Mission" while others will be abbreviated as "Msn".

Ok, but I would imagine Leadership don't want to have to enter keywords to be searched for. They want you guys to produce a summary for them. Have you brought this to their attention?

Leadership won't be using this database, my crew will be using it. I am the developer and a user; my coworkers will be the users. I'm making this database to make our jobs easier. Right now the CTO (a document that tasks our subordinate organizations to do stuff) is published on a weekly basis as a PDF file by another division in our squadron. My division is tasked to create a "Report Card" for the CTO... basically an Excel Spreadsheet that lists all the tasks and also annotates which tasks we require a Report on from the subordinate unit completing the task. This Report Card does not contain the entire Remarks section for each task - instead, it contains an abbreviated (Condensed) version of the Remarks section of each task that we have to manually analyze and interpret for each task. Again, many tasks are OPSEC Monitoring tasks. So instead of having to analyze the Remarks for those tasks to see what they are and manually typing in "OPSEC Monitoring, Msn # 99-999" for each of those, I'd like Access to do that work for me.

 

[jdraw]

I agree with vbaInet in that a discussion of the task and the related issues with the current approach should be set up. What you need is to have Leadership be more explicit on their needs and some suggestions re the logic/effort/approach to simplify the process.
This isn't the first time we've heard of someone trying to match basically free-form text records with no standard vocabulary.

 

[vbaInet]

In that case you've got a big task ahead of you. This is how I envision it:

[Remark]  ||||  FROM {} TO  ||  FROM {} TO  ||  FROM {} TO  |||  [Summary]  |||  Preview / Copy / Paste / Delete

1. The full remark will be on the left.
2. Followed by a bunch of From and To textboxes. This will allow you to enter a range of text you would like to extract. The curly brace could be one of the following:
a. a check box for specifying whether it should extract the text between From/To (i.e. when ticked) or whether it should return the exact text specified (i.e. unchecked).
b. a textbox in between From/To that will specify the text that should be displayed between those matching keywords.
c. a combo box with common words like "for, is, and"... etc for specifying what value should be between the From/To keywords. You would also include "<-between->" and "<-blank->" to specify whether it should extract what is between the From/To keywords or to ignore it.
3. Followed by a textbox for previewing the summary.
4. Followed by a couple of buttons; Preview, Copy, Paste, Clear. The buttons should be self-explanatory but for the sake of clarity, Preview will allow you to preview the summary, Copy and Paste for copying the set of conditions from one row to another, Clear to clear all the From,To textboxes.

If you want to take it further:
5. Have command buttons in the Header section of the form that will allow you to Preview / Paste / Clear all the rows.
6. Have Tick boxes that will allow you to select which rows should be affected by the buttons mentioned in 5.

Basically you're building a rule engine. So you could get inspiration from Outlook.

Using your example from your original post, here's how the rule can be written:

From: "Project Delta" {"for"} To: "IBS/SCADA", From: "CIKR"

Remarks: "Conduct Project Delta tests in association with IBS/SCADA systems and CIKR (Critical Infrastructure/Key Resource) cyberspace terrain, develop CPT certification processes and checklist."

Condensed Version: "Project Delta for IBS/SCADA and CIKR"

 

[vbaInet]

I agree with vbaInet in that a discussion of the task and the related issues with the current approach should be set up. What you need is to have Leadership be more explicit on their needs and some suggestions re the logic/effort/approach to simplify the process.
This isn't the first time we've heard of someone trying to match basically free-form text records with no standard vocabulary.

I concur! Leadership never have time but there are ways to get them to hear you out and give you direction in what they actually want as a summary.

 

[cnstarz]

In that case you've got a big task ahead of you. This is how I envision it:

[Remark]  ||||  FROM {} TO  ||  FROM {} TO  ||  FROM {} TO  |||  [Summary]  |||  Preview / Copy / Paste / Delete

1. The full remark will be on the left.
2. Followed by a bunch of From and To textboxes. This will allow you to enter a range of text you would like to extract. The curly brace could be one of the following:
a. a check box for specifying whether it should extract the text between From/To (i.e. when ticked) or whether it should return the exact text specified (i.e. unchecked).
b. a textbox in between From/To that will specify the text that should be displayed between those matching keywords.
c. a combo box with common words like "for, is, and"... etc for specifying what value should be between the From/To keywords. You would also include "<-between->" and "<-blank->" to specify whether it should extract what is between the From/To keywords or to ignore it.
3. Followed by a textbox for previewing the summary.
4. Followed by a couple of buttons; Preview, Copy, Paste, Clear. The buttons should be self-explanatory but for the sake of clarity, Preview will allow you to preview the summary, Copy and Paste for copying the set of conditions from one row to another, Clear to clear all the From,To textboxes.

If you want to take it further:
5. Have command buttons in the Header section of the form that will allow you to Preview / Paste / Clear all the rows.
6. Have Tick boxes that will allow you to select which rows should be affected by the buttons mentioned in 5.

Basically you're building a rule engine. So you could get inspiration from Outlook.

Using your example from your original post, here's how the rule can be written:

From: "Project Delta" {"for"} To: "IBS/SCADA", From: "CIKR"

Remarks: "Conduct Project Delta tests in association with IBS/SCADA systems and CIKR (Critical Infrastructure/Key Resource) cyberspace terrain, develop CPT certification processes and checklist."

Condensed Version: "Project Delta for IBS/SCADA and CIKR"

that looks to be a little more complicated and manual that I'm trying to accomplish. I don't know if I'm not being specific enough, but I would like the Condensed version to be created during the ParseCTO sub. Here's the sub that I have in my attached database from a few posts earlier...

Public Sub ParseCTO()
On Error GoTo Err_Handler

        Dim FSO As FileSystemObject
        Dim ts As TextStream
        Dim strTaskNum As String
        Dim strWindow As String
        Dim strRemarks As String
        Dim db As DAO.Database
        Dim rstTasks As DAO.Recordset
        'Dim rstCrit As DAO.Recordset
        
        Set FSO = CreateObject("Scripting.FileSystemObject")
    'Location of .txt file to read
        Set ts = FSO.OpenTextFile("\\path\to\Document.txt")
        
        Set db = CurrentDb()
        Set rstTasks = db.OpenRecordset("TblTasks")
        'Set rstCrit = db.OpenRecordset("tblCriteria")
        
        Do Until ts.AtEndOfStream
            Do Until ts.ReadLine = "// "
                ts.SkipLine
            Loop
            
            strTaskNum = ts.ReadLine
            strTaskNum = Left(strTaskNum, InStr(1, strTaskNum, "/") - 1)
            strWindow = ts.ReadLine
            strWindow = Right(strWindow, Len(strWindow) - InStr(strWindow, "//") - 1)
            ts.SkipLine
            strRemarks = ts.ReadLine
            
            rstTasks.AddNew
            
            With rstTasks
                !TaskNum = strTaskNum
                !StartDTG = Left(strWindow, 2) & "/" & Mid(strWindow, 8, 3) & "/" & _
                    Mid(strWindow, InStr(strWindow, "-") - 4, 4) & " " & _
                    Format(Mid(strWindow, 3, 2) & ":" & Mid(strWindow, 5, 2), "hh:nn")
                !EndDTG = Mid(strWindow, InStr(strWindow, "-") + 1, 2) & "/" & _
                    Mid(strWindow, InStr(strWindow, "-") + 8, 3) & "/" & _
                    Mid(strWindow, InStr(strWindow, "-") + 11, 4) & " " & _
                    Format(Mid(strWindow, InStr(strWindow, "-") + 3, 2) & ":" & _
                    Mid(strWindow, InStr(strWindow, "-") + 5, 2), "hh:nn")
                !Remarks = strRemarks
                
[COLOR=Red]                Select Case True
                    Case InStr(strRemarks, "scada") > 0 And _
                        InStr(strRemarks, "project delta") > 0:
                        !Condensed = "Project Delta for IBS/SCADA and CIKR"
                    Case InStr(strRemarks, "opsec monitoring") > 0 And _
                        InStr(strRemarks, "mission") > 0:
                        !Condensed = "OPSEC Mission # " & Mid(strRemarks, _
                            InStr(strRemarks, "mission # ") + 10, 6)
                    Case InStr(strRemarks, "opsec monitoring") > 0 And _
                        InStr(strRemarks, "msn") > 0:
                        !Condensed = "OPSEC Mission # " & Mid(strRemarks, _
                            InStr(strRemarks, "msn number ") + 11, 6)
                End Select[/COLOR]
                .Update
                
            End With
        Loop
        
        Set rstTasks = Nothing
        rstTasks.Close
            
Err_Handler:
    If Err.Number = 62 Then 'Reached end of file
        Exit Sub
    End If

End Sub

As you can see in red, I have to rely on the Select Case to create a Condensed Version of the remarks field. What I would like is something like this in blue...

Public Sub ParseCTO()
On Error GoTo Err_Handler

        Dim FSO As FileSystemObject
        Dim ts As TextStream
        Dim strTaskNum As String
        Dim strWindow As String
        Dim strRemarks As String
        Dim db As DAO.Database
        Dim rstTasks As DAO.Recordset
        'Dim rstCrit As DAO.Recordset
        
        Set FSO = CreateObject("Scripting.FileSystemObject")
    'Location of .txt file to read
        Set ts = FSO.OpenTextFile("\\path\to\Document.txt")
        
        Set db = CurrentDb()
        Set rstTasks = db.OpenRecordset("TblTasks")
        'Set rstCrit = db.OpenRecordset("tblCriteria")
        
        Do Until ts.AtEndOfStream
            Do Until ts.ReadLine = "// "
                ts.SkipLine
            Loop
            
            strTaskNum = ts.ReadLine
            strTaskNum = Left(strTaskNum, InStr(1, strTaskNum, "/") - 1)
            strWindow = ts.ReadLine
            strWindow = Right(strWindow, Len(strWindow) - InStr(strWindow, "//") - 1)
            ts.SkipLine
            strRemarks = ts.ReadLine
            
            rstTasks.AddNew
            
            With rstTasks
                !TaskNum = strTaskNum
                !StartDTG = Left(strWindow, 2) & "/" & Mid(strWindow, 8, 3) & "/" & _
                    Mid(strWindow, InStr(strWindow, "-") - 4, 4) & " " & _
                    Format(Mid(strWindow, 3, 2) & ":" & Mid(strWindow, 5, 2), "hh:nn")
                !EndDTG = Mid(strWindow, InStr(strWindow, "-") + 1, 2) & "/" & _
                    Mid(strWindow, InStr(strWindow, "-") + 8, 3) & "/" & _
                    Mid(strWindow, InStr(strWindow, "-") + 11, 4) & " " & _
                    Format(Mid(strWindow, InStr(strWindow, "-") + 3, 2) & ":" & _
                    Mid(strWindow, InStr(strWindow, "-") + 5, 2), "hh:nn")
                !Remarks = strRemarks
                
[COLOR=Blue]                For Each record In tblCriteria
                    If InStr(strRemarks, tblCriteria.Criteria1) > 0 And _
                        InStr(strRemarks, tblCriteria.Criteria2) > 0 Then
                        !Condensed = tblCriteria.Result
                    End If
                Next record[/COLOR]
                            
                .Update
                
            End With
        Loop
        
        Set rstTasks = Nothing
        rstTasks.Close
            
Err_Handler:
    If Err.Number = 62 Then 'Reached end of file
        Exit Sub
    End If

End Sub

I just don't know how to get the blue stuff working. I know it may not be the most efficient thing ever, but I need something quick and dirty that will work for right now until I'm able to fix the root of the problem.

 

[vbaInet]

Project Delta, Ospec monitoring, Opsec mission, Msn

* How many of these different combinations do you currently have?
* And how many of these combinations would you have in the future?
* And would VBA know to put "for" between "Project Delta" and "IBS/SCADA"?

I'm sure the answer to the first two questions is there could be many. So at the end of the day you're building a rule engine and someone needs to type in what rules the system should work with and how the entire sentence should be formed. You simply cannot write a bunch of Select Case statements to cover every scenario. What I've written in detail is the way to do it and I mentioned that you should look at Outlook's Rules for inspiration. It's not going to be any easier.