Matching data

[johnbornsworth]

Hello! I'm attempting to scour data out of survey submissions. We have around 2,000 open ended public submissions form a survey we conducted with a city. The submissions/responses are located in a table, each submission a new record. We have a second table with a list of important words/criteria. I'd like to match the important words to the submissions to result in both a count of records matching the important words, and a query list of them.

I'm not exactly sure how to do this! I tried just typing into a query criteria: Like [searchwords].[search] in a column under AllResponses.Response. That didn't work.

Thank you for any direction.

John Bee

 

[theDBguy]

Hi John. Welcome to AWF!


One way is to use a non-equi join. Can you post the structure of the two tables? Thanks.

 

[johnbornsworth]

Thank you!

I'm not 100% sure how to do that... but....

Table 1 has 1 large text field, "Responses"
Table 2 has 2 fields, short text: "Search" and "Category"

Search may be something like "good work" or "great job" and category would be "supportive".

It's a very simple database and literally has one purpose. To parse through the responses and find text. Online survey applications just don't offer enough options.

 

[theDBguy]

Hi. Is your backend Access or SQL Server. I think SQL Server would be good for something like this.

 

[Galaxiom]

A join criteria using the InStr() function can do this but will not look beyond 255 characters in the long text field.

FullTextCatalog in SQL Server can do a great job on this task.

 

[johnbornsworth]

Currently access backed. Don't have SQL server but could download SQL server express. Is this function not available in access?

 

[theDBguy]

Hi. SQL Server has a feature called Full-Text Search. You can do some limited version of it in Access. For example:

SELECT T1.ID, T2.Keyword, T2.Category
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.TextField Like "*" & T2.Keyword & "*"

 

[Deleted member 147267]

I think you will find that a query won't work on a memo field if you use any operator such as LIKE on it. I did test 899 characters and Instr returned a record in a query where the searched word was at the beginning, and then another word that was at the end. However, there were no joins.