trouble understanding relationships between three? tables

[markdd1961]

I am new to Access and have been googling for an answer, but I think I am just using the wrong words to find the solution what I am trying to accomplish

I have an excel sheet of survey data. Each row is the set of responses from one person and a unique generated token for each row.
several questions allow the person to enter in open ended-text.



I would like to add one or more "codes", words the summarize the text. Those codes exist in a separate table:



I want to associate the answer in column "b" (open_test_entry1) of the raw data table with one or more codes from column "A" in the code table. Then I would like to have a third table that saves the Unique Token from column "A" (generated_token) with the codes entered for correct row and column of the (open_test_entry1).



I have created the raw data table and the codes table. I added a column to the raw data table that calls a look up to the codebook table. I have made that work with a form, but it seems an inelegant solution. I don't want to modify the raw data table at all (i.e. add columns). But for the life of me I cant understand how to create a table that pulls in the token (unique identifier) from a row and the correct test from the cell in that row, then add codes to that text and store them into a table so the two are forever linked.

[John_G]

I want to associate the answer in column "b" (open_test_entry1) of the raw data table with one or more codes from column "A" in the code table.

Leaving the table structure aside for the moment - how are you going to associate the free-form text with the code(s)? Do you do it manually, or do you have an algorithm to determine what the code(s) should be?

Before you go any further, please consider you data structure. It is not properly normalized (if you don't know what that is, let us know and we find some links for it), in that in each row of the Excel spreadsheet you have repeating occurrances of the same type of data, i.e. a question response. This is very commonly the way Excel is laid out, and it is good for that, but Access is not Excel and doesn't work the same way. Keeping data unnormalized can make extracting information cumbersome, to say the least. For example, if you wanted to know which text responses were rated "worst", and which set of "row" they were in, how would you do it?

A normalized response table would look something like this, at a minimum:

Token
Question_ID
Response


For the codes, you will need a third table, as you point out. The fact that your raw data is not properly normalized will pose some difficulties, but essentially, the table should look something like this:

Questionnaire_Token (from the main table)
Question_ID
Code

(Any given reponse should have only one rating code assigned to it, in my opinion)

First difficulty is question_ID. You don't record the question number anywhere, which is a problem, but you do at least have a field name. Normalization will fix that issue.

You might want to reorder your codes from best to work, with a numeric value for each, so that retrieving results which were rated as "good or higher" is much easier.

There is a lot to think about there, I know, but getting things right now will save a lot of headaches later.

(But I still would like to know how a text response gets a rating code assigned to it!)

Post back for any further help you need. Good luck with your project.

[markdd1961]

Ah, sorry for the confusion. I have to use dummy data for privacy reasons. The codes are associated manually. I am a cultural anthropologist trying to use Access for a form of analysis called "grounded theory." Each entry under the "open_test" entry columns are indeed unique responses. They can be one word or several paragraphs. The codes are not on a scale, that was a poor example. The each "code" is a word (or two words together) meant to summarize the concepts that appear in an entry. For example if someone writes "Well, in the morning I usually stop n the break-room first and get some coffee, then check the prices for the NASDAQ opening. After that I check my schedule to see if I have any rubes...I mean clients coming in I can fleece out of their life savings." I might code that Morning_routine, Coffee, NASDAQ. (again, terrible examples), The codes are derived by making several passes of the responses for each question and they emerge out of the data itself. (bottom up as opposed to top-down categories). With each pass the codes are refined so that each person on the analytical team is using the same code for the same concept. Human and subjective rather than Algorithm.... Does this clarify?

[orange]

I agree with John-G's comments. I looked at your latest post and googled to find this on GT
I think that getting your tables and relationships is important, as it always is in database. But, I think the analysis of text to arrive at "1,2 or 3 word summaries" consistently will be a major issue. Hopefully, you can test/refine this manual procedure for consistency regardless of the database design. I don't know if you can search for keywords or synonyms.

English (any language) phrases can bee difficult to summarize nto a few words , and especially consistently with different reviewers.
eg. "Time flies like an arrow. House flies like garbage"