Survey Databases

[Liz A]

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...

 

[Liz A]

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

 

[Liz A]

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

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

 

[Liz A]

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

 

[Liz A]

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:

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.

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

 

[Liz A]

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?

 

[Liz A]

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...

 

[TastyWheat]

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:

Employee
--------
EID (PK)
FirstName
LastName
etc.

Question
--------
QID (PK; Auto)
QuestionText (Memo)

Configuration
-------------
CID (PK; Auto)
QuestionOrder (Number)
QuestionID (FK)

Multiple Choice Key
-------------------
MCKID (PK; Auto)
QuestionID (FK)
ChoiceText (Memo)

Multiple Choice Response
------------------------
EmployeeID (FK)
DateAnswered
ConfigurationID (FK)
MultipleChoiceKeyID (FK)

Free Response
-------------
EmployeeID (FK)
DateAnswered
ConfigurationID (FK)
QuestionID (FK)
Response (Memo)

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

Subquestion
-----------
QuestionID (FK)
SubquestionID (FK)

QuestionHeading
---------------
HeadingID (FK)
QuestionID (FK)

Heading
-------
HID (PK; Auto)
HeadingText (Memo)

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).