Share via

Finding key words from a field in Access to fill/flag respective keywords field

Anonymous
2017-10-05T02:53:46+00:00

Hello everybody:

I have a survey table from thousands of students. We asked them what they like or prefer to do. They wrote their answer in sentences. I am interested to check/find their answers by few keywords. And also I want the keywords as new fields. 

Basically, in design view, I want to set a query to get the job done in Access. But I failed, I need your thoughts please!

For example below is my table:

Name Feature
Ana I like travel, fun, walk, sleep
Mis Probably I prefer sleep, soccer,<br>hunting
Steve
Adel I prefer sleep, travel, hunt
Derek I have no preference to do
Alex Sleeping, Walking is my preference

* Notice: Adel Mentioned hunt but Mis mentioned hunting 

* Steve did not give info

* Derek has no preference 

Moving forward: I randomly choose keywords to check if the keywords (like sleeping, hunting, etc) exist in their preference list. I also want my keywords as field name and mark/flag if they got exact or closest match or no match or no info at all.

Sample output 1:

Name Football Sleeping Hunting walking Soccer Nothing Unfilled
Ana 0 1 0 1 0 0 0
Mis 0 1 1 0 1 0 0
Steve 0 0 0 0 0 0 1
Adel 0 1 1 0 0 0 0
Derek 0 0 0 0 0 1 0
Alex 0 1 0 1 0 0 0

Sample output 2:

Name Football Sleeping Hunting walking Soccer Nothing Unfilled
Ana 0 sleep 0 walk 0 0 0
Mis 0 sleep hunting 0 soccer 0 0
Steve 0 0 0 0 0 0 unfilled
Adel 0 1 hunt 0 0 0 0
Derek 0 0 0 0 0 none 0
Alex 0 Sleeping 0 Walking 0 0 0
Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2017-10-05T10:08:48+00:00

    I'm afraid you are missing the point. Access is a relational database. There are rules for the design of relational databases. These rules are called normalization. As a rule relational tables are tall and thin, not wide. Naming the fields, field1, 2, 3, etc. is just as bad.

    I understand you are a beginner, so I spelled out my answer in detail. If you didn't understand I would be happy to explain further, but you need to specify what you didn't understand. This medium works best when you ask specific questions.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-10-05T03:53:46+00:00

    Thank you indeed for your reply. Agreed, I will not use keyword as field names. Let's say my field names are field1, field2, field3, field4, field5, field6, field7. 

    I am a beginner I didn't quite catch your answer. Can you please break down your response so I can follow step by step in Access.

    Thank you.

    Was this answer helpful?

    0 comments
  3. ScottGem 68,810 Reputation points Volunteer Moderator
    2017-10-05T03:14:57+00:00

    First, no you DON'T want to use keywords as field names. That is not proper design. A survey requires a specific structure, it doesn't appear you are using a proper structure for a questionnaire.

    But if you want to record keywords to a question, then you need a child table: tblKeywords: KeywordID (PK Autonumber) ResponseID (FK) Keyword

    To populate this table you would use Append queries like: INSERT INTO tblKeywords (ResponseID, Keyword) SELECT ResponseID, Forms!formnane!txtKeyword AS Keyword WHERE Feature Like "*" & Forms!formname!txtKeyword & "*";

    So you have a form where you enter the keyword you want to search for. Then run that query to populate your keywords table.

    Was this answer helpful?

    0 comments