Question Normalization Of Questionnaire

[vapid2323]

Normalization Issues

INTRO:

http://www.access-programmers.co.uk/forums/showthread.php?t=36545

I found the above article but I wanted to see if I should be following the same layout.

I posted a question in the excel section of this website but it sounds like my issue is with normalization and not excel so much. (You can see that post at http://www.access-programmers.co.uk/forums/showthread.php?p=1016166#post1016166)

QUESTION:
We have a excel document that is a form for QA to use when auditing a facility out in the field, each excel form is then emailed into the main office where I can access the data and import it into Access.

The Form has about 70 questions and 5 sections with a yes, no and NA and comments option (See attached).

This form is updated all the time, some questions are removed and some are newly created. I currently have to change the table each time the form is updated.

I am not a pro at access just yet (as you can tell) how might I normalize this database so that it can handle updates to the excel file without having to change table design?

 

[HiTechCoach]

http://www.access-programmers.co.uk/forums/showthread.php?t=36545

I found the above article but I wanted to see if I should be following the same layout.

Yes. The-Doc_Man's post is very close to what I would recommend.

 

[vapid2323]

Ok so this is what I came up with! I think this will work but I figured I would check in and see what you think

tblSMRForm
SMRFormID (PK)
ApprovalID (FK)
VersionID (FK)
StaffID (FK)
SiteID (FK)
FileName ' Used for nameing the records (Form1,Form2 etc)

tblComment
CommentID (PK)
SMRFormID (FK)
Comment ' for the comments after each question

tblDropdownOption ‘this is for the Yes, No, NA options
DropdownoptionID (PK)
SMRFormID (FK)
Options ' will list the dropdown options

tblVersion
VersionID (PK)
VNumber 'Holds the version number for the form used at the time

tblSite
SiteID (PK)
SiteName ' Site infomation
City
State

tblStaff
StaffID (PK)
First Name 'staff info
Last Name

tblApproval
ApprovalID (PK)
COINumber 'For mail merge approval form to be printed later
ANMMonitorBy
Date
IssuesIdentified
ANM Comments

tblQuestion
QuestionID (PK)
SMRFormID (FK)
Question ' Will have all questions for the form

 

[HiTechCoach]

It appears that you have only included the PF and FK field in the tables.

To know if it is properly normalized we must to see every field in every table.

What is tblDropdownOption used for (the table name is not very descriptive)?

 

[vapid2323]

It appears that you have only included the PF and FK field in the tables.

To know if it is properly normalized we must to see every field in every table.

What is tblDropdownOption used for (the table name is not very descriptive)?

Updated my post

 

[HiTechCoach]

tblDropdownOption ‘this is for the Yes, No, NA options
DropdownoptionID (PK)
SMRFormID (FK)
Options ' will list the dropdown options

If this is a lookup table, then why is there a foreign key field for SMRFormID (FK).

This would indicate the ever record (SMRFormID vaule) would ahave a separate list of Yes, No, NA options

 

[vapid2323]

If this is a lookup table, then why is there a foreign key field for SMRFormID (FK).

This would indicate the ever record (SMRFormID vaule) would ahave a separate list of Yes, No, NA options

This database will not have a data entry form; users will never use the lookup option. I just need to be sure that the Yes, NO, NA is associated with the correct questions from the Excel import.

You are saying that I need to remove the FK and not have a relationship with the tblSMRForm?

 

[HiTechCoach]

This database will not have a data entry form; users will never use the lookup option. I just need to be sure that the Yes, NO, NA is associated with the correct questions from the Excel import.

You are saying that I need to remove the FK and not have a relationship with the tblSMRForm?

I think you have the relation reversed.

The relationship should be between the Lookup table for responses (one side) to the record that holds the response (many side).

Where are you storing the responses (answers) to the questions?

It should not matter how the data gets into the tables while you are desiging and normalizing the structure. Normally at this point I do not even worry about the UI or how the data will get into the tables.

This database will not have a data entry form

I would still create one just for reviewing data and testing.

 

[vapid2323]

I think you have the relation reversed.

The relationship should be between the Lookup table for responses (one side) to the record that holds the response (many side).

Where are you storing the responses (answers) to the questions?

It should not matter how the data gets into the tables while you are desiging and normalizing the structure. Normally at this point I do not even worry about the UI or how the data will get into the tables.


I would still create one just for reviewing data and testing.

I see what you are saying, I fliped the relationship.

This is where I planned to store the data. The Comment IS the answer for this database.

tblDropdownOption 'holds (Yes, No and NA)
tblComment 'Holds the comments for the question (answers)


I hope I am not confusing anything, I am really trying to learn how this all works

 

[vapid2323]

Uploaded my DB, I hope its helpfull

 

[HiTechCoach]

I see what you are saying, I fliped the relationship.

This is where I planned to store the data. The Comment IS the answer for this database.

tblDropdownOption 'holds (Yes, No and NA)
tblComment 'Holds the comments for the question (answers)

I do not see any relationship between these tables.

I will take a look at your attached database.

 

[The_Doc_Man]

My answer might seem a bit harsh, but it isn't intended that way. I'm just going to answer based on what I see in the thread. My answers are sometimes forcefully stated because, like all other people, sometimes I feel strongly about real-world problem solving. If my answer is a bit abrasive, forgive me for having strong opinions.

The problem that I see from your description is that you face a variable-content dataset that approaches unpredictability. You have a bunch of questions but never know which ones you will use, and apparently the range of questions is mutable. The first part of that isn't so bad, but the mutable range is guaranteed to be a headache in subsequent analysis.

The problem with this application is that it is haphazard. Your design doesn't do you any favors. And it does quite a few disfavors. What SHOULD happen here is that you step away from the drawing board and ask the question, How am I Going to Gather the Information? Which leads to the more basic question, What Am I Seeking from this Dataset? The basic issue here is that the general variability of your question base is going to fight your analysis unless you can control and constrain it.

Suppose that we look at the way some testing companies manage techie certification exams. Not quite a questionnaire, but relevant to this discussion, or so it seems to me.

The CompTIA folks have these delightful exams for things like security certification, network management certification, etc. They have, for any given certification, a set of several hundred questions covering 5 to 10 sub-topics within the exam. They more or less randomly choose some number of questions from each sub-topic. But they ALWAYS know which questions they COULD have asked. That isn't random. They always know that the subtopics will be fairly represented but the specifics of that representation are not guaranteed. The subset questions chosen for the test are the random factor.

I'll state this plainly. Unless you take something like this approach, you are going to never get this analysis to be more than randomly useful. You must know ahead of time the entire range of questions that COULD have been asked. Then it would be possible for you to define what was in the subset by reading the questionnaire for the text of the questions (or the other way I'm going to suggest).

Do yourself a big favor and add a column that contains a question code. Let's say you have a thousand questions coded with identifiers as A00 through A99, B00 through B99, ..., J00 through J99. Then, your questionnaire has a question code, the text of the question, and the answer. Now, you can simply read the spreadsheet contents via the Excel API in VBA. Open a workbook object, activate the sheet, scan the rows, read the contents of each cell.

Extract based on the question codes and their responses. And you don't ever need to actually store the questions for each questionnaire, just the codes. You can associate the responses to the codes and then look up the questions (via the codes) from the master question table. Add new questions there, perhaps, but remember that the more new questions you add, the more you confuse matters.

How does this help you? Because to take statistics out of an unpredictable and highly mutable questionnaire violates TWO good practices at once. Good programming design - and good TESTING design.

I'll digress this much: The "Doc" in my name represents a PhD in Chemistry. I am more than just slightly familiar with sampling theory. What I see in your questionnaire process is chaos. Control the chaos first by agreeing on a set of possible questions and LOCKING THAT SET DOWN. Then and only then can you control the import process. You can still have the variability of the displayed questions - but not the variability of the question base.

Back to the problem: You are fighting a design issue in the nature of what is being tested. Nail down the design. If the bosses refuse, then advise them that they will get doodlum-squat from any long-term analysis.

 

[vapid2323]

Very well said!!

I will work with the folks that come up with the questions and try to get a standard set.

Let’s assume that we only have ONE set of questions, moving forward. I still have basic issues with storing that data?

I need to create a relationship the ties the Comments and questions and dropdown options?

Thank you for all your help, I really do appreciate it!

 

[The_Doc_Man]

The design of a Q&A database is a nightmare to some. But it shouldn't be.

You have a questionnaire. What are its attributes?

• The questions and associated answers
• Who filled it in (for themselves or an organization)
• When was the questionnaire presented?
• Who presented it?

You have a set of questions. What are their attributes?

• The text of the question
• a list of condoned answers
• optional alternatives such as "none of the above" or "don't know"
• which questions from the set were offered
• which of the presented questions were answered

Somewhere in the exploration of those attributes, you might come up with a data structure. But first you have to recognize your basic data element. It is the COMBINATION of a question and a condoned answer. Isolated questions with no answers tell you nothing you didn't already know. Isolated questions with lists of possible/condoned answers tell you nothing you didn't already know. It is the survey subject's answers to the questions that is the variable item. There is where statistics are made.

I'll add that I hardly ever download someone's samples. I'm an ideas kind of guy, not an exploratory surgeon. But that's just me.

 

[vapid2323]

Ok I think I have this down, please keep in mind this is only the second DB I have had to make from the gound up

 

[vapid2323]

Bump, No other comments? I assume this means I got this figured out?

 

[HiTechCoach]

Bump, No other comments? I assume this means I got this figured out?

Not sure about that.

You previously posted:

tblDropdownOption ‘this is for the Yes, No, NA options

If this is true then why is it not related to the "answers" is some way?

If you want any more comment then I would recommend that you post a database with some sample data. At least two records in every table if possible. (My "secret trick" I is a report). I would include a report/sub-reports) that shows all the data formated together. It could be similar in format to the spreadsheets.

From what I can see, there are some naming issues.

1) Avoid naming a field Date

2) I normally avoid using dashes (-) in field names. I will use sometimes use an underscore (_).

3) Pick a naming style and stick to it.

 

[vapid2323]

I think I fixed the issue with the Options not being linked to the question.

But I can not do any qry on the tables or get a form to work? I am still missing something...

I have attached the DB so you can take a look

 

[vapid2323]

Looks like I got the qry thing figured out, we will see

 

[vapid2323]

Ok I have done my best to get this thing to work; I spent the last two days trying

Deadline for my project is getting closer and I am stuck.

I think the relationships are looking better but I have a major issue that I cant figure out... How do I import my data?

I have a table "Import Test" with some basic data, with 4 data fields: Filename, Option, Comments and Question.

I can’t figure out how to import this data into the tables let alone get all my other required fields filled in while keeping records associated with each other.

I think I bit off more than I can chew with this project, if I can get past this set of issues I will be back on track.