Survey Databases (1 Viewer)

Liz, To me, a robust survey database application would be would be quite an undertaking for a newbie. But if you do commit to doing it the rewards could be substantial - You'd have you survey db and more important (to me anyway), would be your newly acquired Access / DB skills! I say go for it!

Thanks Ken, it is certainly a tough call. I would like to do this the "right" way, but might have to make it workable the "wrong" (i.e., flat) way first as there are several people who need to be using it now. Then if I can figure out the quirks of the more robust system, maybe I can replace with that. Or just learn it for when I set one of these up in the future. I appreciate the advice!

As I play around with this, I'm guessing I'll have more questions to post...

My favorite line here is - If you don't have time to do it right, what makes you think you have time to do it twice?

If you're going to do it wrong, you are far better off doing it with Excel. You will be very unhappy with how Access works with spreadsheetlike data. Once you see how it is done correctly with normalized tables, your palm will leave a dent in your forehead as you say duh!

Rather than making a completely generalized survey application, my suggestion would be to make a survey application for the current survey. Don't worry about expandability. Just worry about the current set of questions/answers. That will simplify your process and limit your exposure. Here is the simplest structure that makes sense. It still allows for future surveys as long as they are the same type.

tblSurveyTaker: (whoever takes the surveys)
SurveyTakerID (autonumber primary key)
FirstName
LastName
etc.
tblSurvey: (survey definition)
SurveyID (autonumber primary key)
SurveyName
etc.

tblSurveyQuestions: (questions for survey)
QuestionID (autonumber primary key)
SurveyID (foreign key to tblSurvey)
QuestionText

tblTakenSurveys:
SurveyTakerID (foreign key to tblSurveyTaker)
SurveyID (foreign key to tblSurvey)
QuestionID (foreign key to tblSurveyQuestions)
Answer

Thanks for the tip Pat! So, I've been attempting to create this database the way you pros have suggested and so far so good with setting up the tables, but beyond this, my lack of experience is getting the better of me. So here is my series of questions/concerns:

1. Is there any reason tblSurveyQuestions needs the foreign key to tblSurvey when tblTakenSurveys will connect these two? I have questions that appear in multiple surveys, but want to make sure this won't be a problem later.

2. I am adding another table, tblResponseOptions, as several questions share the same response choices. This table ends up being something like:

tblResponseChoices
ResponseID (autonumber primary key)
ResponseText
ResponseNumberCode (for later scoring)

and then the table that pulls everything together would be:

tblTakenSurveys
SurveyTakerID (autonumber primary key)
SurveyID (foreign key to tblSurvey)
QuestionID (foreign key to tblQuestions)
ResponseID (foreign key to tblResponseOptions)

Does all this make sense?

3. Now for a bigger, more involved questions. How do I turn all this into forms for data entry? I want to create forms that look like the surveys, but am having trouble wrapping my mind around this, since the questions are not fields themselves.

4. When I query this to get everything onto one sheet for analysis, should I be doing a select query to pull the relevant fields together, then a crosstab query where the questions become columns? Basically, I think I get the big picture conceptually, but I am getting lost in the details of how to make it happen.

I'd greatly appreciate any advice. Or perhaps there is a resource that explains all this to which someone might direct me?

Thanks,
Liz

1. Is there any reason tblSurveyQuestions needs the foreign key to tblSurvey when tblTakenSurveys will connect these two? I have questions that appear in multiple surveys, but want to make sure this won't be a problem later.

Click to expand...

In that case, you also have a many-to-many relationship between surveys and questions so the structure becomes:
tblSurveyTaker: (whoever takes the surveys)
SurveyTakerID (autonumber primary key)
FirstName
LastName
etc.

tblSurvey: (survey definition)
SurveyID (autonumber primary key)
SurveyName
etc.

tblQuestions: (individual questions)
QuestionID (autonumber primary key)
ResponseCodeSetID (foreign key to tblResponseCodeSets)
QuestionText

tblResponseCodeSets: (identifies a set of valid codes such as 1,2,..10 or a, b, c)
ResponseCodeSetID (autonumber primary key)
SetName

tblResponseCodes: (individual codes in a set - used to populate combo)
ResponseCodeID (autonumber primary key)
ResponseCode
ResponseMeaning

tblSurveyQuestions: (questions for survey)
SurveyQuestionID (autonumber primary key)
QuestionID (foreign key to tblQuestions, unique index fld1)
SurveyID (foreign key to tblSurvey, unique index fld2)

tblTakenSurveys:
SurveyTakerID (foreign key to tblSurveyTaker)
SurveyQuestionID (foreign key to tblSurveyQuestions)
ResponseCodeID (foreign key to tblResponseCodes)

2.

Click to expand...

tblTakenSurveys cannot contain both header and detail information. The first two fields occur only once per survey and there is very likely data associated with those two field. At a minimum, you probably want to keep the date the survey was taken. If the survey is administered by someone, you would want to log that person's ID, etc. That is why the four fields would end up in two tables.

3.

Click to expand...

Use a main form to capture the header information and a subform that lists all the questions for this particular survey. You can format the subform so that it sort of disappears into the main form if you want something that looks more like your paper form. However, if the subform contains more questions than will normally show, you will need to include scroll bars at a minimum.

4.

Click to expand...

Yes, much of the analysis will rely on crosstabs.

Pat Hartman said:

tblQuestions: (individual questions)
QuestionID (autonumber primary key)
ResponseCodeSetID (foreign key to tblResponseCodeSets)
QuestionText

tblResponseCodeSets: (identifies a set of valid codes such as 1,2,..10 or a, b, c)
ResponseCodeSetID (autonumber primary key)
SetName

tblResponseCodes: (individual codes in a set - used to populate combo)
ResponseCodeID (autonumber primary key)
ResponseCode
ResponseMeaning

Click to expand...

OK, I'm starting to get this. Now maybe I'm missing something, but why would ResponseCodeSetID be a foreign key in tblQuestions and not tblResponseCodes?

Also, I should mention that these surveys are part of a larger database, which is why I did not include a table with the "survey taker's" identifying info in my previous post. This table already exists and these survey tables will be linked to it through tblTakenSurveys.

And lastly, is there a good resource that explains step-by-step how to create entry forms from this type of table structure? I am not seeing quite how the combos will be populated, nor how the entered data will get into the right place. I'm going to play around with it a bit more to try to figure it out, but I think I may need some more explicit guidance...

-Liz

ResponseCodeSetID is a foreign key in tblQuestions. This identifies which set of responses is valid for a question. Having a set id allows you to have multiple ranges such as 1-10, 1-5, a-d, etc.

ResponseCodeID is a foreign key in tblTakenSurveys. It identifies which value of a set was chosen so it represents 1, 8, c, etc.

Right, I understand what ResponseCodeSetID refers to, but am confused as to how it is actually used. I would have thought one would want to relate ResponseCodeSetID back to the actual response codes in tblResponseCodes. How am I to be using this exactly? I mean, where does this information as to ranges of valid responses get implemented for each question? Maybe there is an example of this somewhere?

Also, for this table structure, must all the joins be inner joins? I had them as left outer joins and was getting that ambiguous join error when I tried to run a query, but it worked when I made everything an inner(equi) join.

Along the lines of table relationships, is there any reason to use or not use lookup functions in tables to enter the foreign keys?

As for the entry forms issue, I have searched these forums plus the two Access books I have (Access Bible and Access Database Design & Programming) but am still not quite getting how to design the forms. Am I just missing something? I realize I still have a lot to learn...

Thanks once again,
Liz

I would have thought one would want to relate ResponseCodeSetID back to the actual response codes in tblResponseCodes.

Click to expand...

You relate the "set" of response codes to the question definition to define what is valid. This set will be used to populate the combo on the form. You relate the specific response code to the responder's answer. You can infer the set from the specific response code but you shouldn't need to.

must all the joins be inner joins?

Click to expand...

For this application, I would use only inner joins. The only place you might want a left join is if you want to find responders who have not actually taken a query.

Along the lines of table relationships, is there any reason to use or not use lookup functions in tables to enter the foreign keys?

Click to expand...

I NEVER use the lookup "feature". I find that it causes more problems than it solves. It will do nothing but cause you confusion if you use queries or VBA.

The form will be a main form where you match the responder with a particular survey. I would add a button to this form and in its click event, I would run an append query that adds empty answer records to populate the subform. That means that you must disable the Add and Delete options for that subform. I suggest a button because it is easier to control when the answer records get added. Pressing the button a second time should result in an error or you can simply ignore the error because as long as your primary key and unique indexes are properly defined, Jet will prevent duplicate questions from being added.

So, I'm still working on this one and could use some more help... I've set up the table structure according to the above, and am working on the forms, but am getting a bit stuck. As per what you wrote:

Pat Hartman said:

The form will be a main form where you match the responder with a particular survey. I would add a button to this form and in its click event, I would run an append query that adds empty answer records to populate the subform. That means that you must disable the Add and Delete options for that subform. I suggest a button because it is easier to control when the answer records get added. Pressing the button a second time should result in an error or you can simply ignore the error because as long as your primary key and unique indexes are properly defined, Jet will prevent duplicate questions from being added.

Click to expand...

Any chance you (or someone) could explain in a bit more detail how and what needs to be in this append query? And how to run the query from a button? I am going to play around with it, but I don't really know code (or if that's necessary). I finally figured out how to make the subform that pulls the question text from the tblQuestions and enters responses via a combo bound to tblTakenSurveys.ResponseCodeID. But now, like you said, I need to populate this (continuous) subform for each survey taker. I was thinking I would basically need to be adding new records to the tblTakenSurveys table but because this table requires an entry in the foreign key ResponseCodeID, I can't leave that field empty. Should I create a response code that would have the meaning "not yet entered" or is there a cleaner way? Or am I missing something?

(At this point, I'm wondering if this thread should be moved to Forms, so feel free to re-direct there if appropriate.)

Thanks again,
Liz

OK, so I am trying to create an append query to populate the subform, but it is not working. I think this is because I need to pull autonumber PK fields from other tables to append to tblTakenSurveys (all the fields in this table, other than the PK, are foreign keys). In looking into this, it seems I cannot use autonumber fields in an append query, but if this is true, then I cannot append new records to tblTakenSurveys. I read somewhere else on this forum that this can be done through creating a temporary table and appending that, so I am going to try this next, but a) is this the right move, and b) if so, how does one create a "temporary" table? Do I delete the whole table when I'm done or is there a way to create a table that "knows" it is temporary?

Figured it out! No temporary table necessary, and I guess it was exactly what Pat was telling me to do, but I didn't get it. In case anyone is interested, here is where I reposted this question in more detail, then posted how I resolved it:

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

It always seems to be something "simple", yet the answers are surprisingly difficult to find...

I haven't looked thru all the posts but I like the overall structure Sergeant recommended (link). I'm working on a survey database right now and this is my current setup:

(These other tables are less typical and most people wouldn't need them)

The one thing I'm definitely going to change is how my multiple choice keys link to the questions. It's better if the keys are grouped in to "keysets" and each question links to a specific keyset. So two questions can have the same set of choices (without duplicating the choices in the MultipleChoiceKey table).