Table Design/Relationships

[Dartos]

Hi guys,

So I'm doing research and have been asked to use access instead of other programs for my current project. I'm doing a survey and collecting responses.

I have the master table which is where I enter the responses. However, each question has been written to match several classification systems so I've been told to link the field/column for each question on the master table to a row on another table where I classify each question. Its not easy to explain so I've attached a picture.

I think they want to be able to run a query which pulls all questions which are classed as 'A' for Cat1. and then pulls those fields from the master table. Thus creating a table which has only got the answers to Q1 and Q2.

I tried dragging Q1, Q2 etc to 'question number' as a relationship but it makes duplicates of the table instead and I don't understand what its doing.


I'm sorry if I'm not making sense. Please let me know what I need to clarify.

 

[The_Doc_Man]

First, search this forum for the topic "Questionnaire" because this kind of question has been addressed many times.

Second, your primary table is not normalized. Proof of same is shown by this question: What do you do when you have to add Q4? Same applies to question table when you have to add a category.

This is a data design question in which you need to get the design right NOW or you will never get anything useful in the future.

Do some reading in the "questionnaire" search I suggested and then come back with some specific questions. While some subject matter can work "loosely" in Access, this is going to be a case where bad normalization will make your task totally impossible.

 

[Dartos]

Thanks for the reply. I figured it would have been asked before but I couldn't find it in my searches. I was over thinking the search terms a bit. Will go have fun reading some more.

 

[Dartos]

So I did some reading and put in a different design. It looks messy as anything but it seems like it should work and I can try use queries and crosstabs to produce a table that is readable for my analysis.

The design is below and I've got 2 (more specific) questions now.

tblQuestions (PK)
QuestionID
Question
QuestionTypeID (FK)
Cat1
Cat2

tblQuestionType
QuestionTypeID (PK)
QuestionType

tblRespondents
RespondentID (PK)
RespBackgroun
RespYrsExp

tblResponses
ResponseID (PK)
RespondentID (FK)
QuestionID (FK)
Answer

Now I have 2 questions.

I have 26 questions so when I add a respondent I then have to add to tblResponses the RespondentID 26 times and the 26 unique QuestionID's. Can I set it up so that when I add a respondent it automatically populates tblResponses with 26 rows, all with the same respondentID and with the 26 different QuestionIDs? So then I just have to add the answers? I'm expecting about 250 surveys back so an extra 52 fields to fill times 250 people is a lot of time. I'm thinking I may be able to use forms but not sure how they work and haven't had time to read up on them.

Secondly, to save time I set Answer as a lookup for the values yes, no, blank but the first 3 questions in the survey are actually free text. Is there a way I can specify the lookup to refer to the QuestionType and respond accordingly? That is why that table is there but I haven't been able to figure out how to do it yet.

Your help would really be appreciated.

@The_Doc_Man - I hope that since I copied another suggested table format to get this big messy one I'm now 'normalized'. I wasn't exactly sure what you meant by it and there are apparently all sorts of things that can be normalized or not in a database (I got a bit confused). If not, could you please let me know what I need to change to fix it?

 

[SteveGMC]

I had a very similar question with a great deal of help from PNGBill he helped with a solution. Take a read here

 

[Dartos]

Wow... damn... you mean the easiest way for me to do it is with all that code and stuff about sql and vb? None of that made sense to me... Not even just the code blocks, I lost track of the conversation bits too! :S

If I was going to be doing this a lot it would be fine to spend ages learning it all but for a one of project is there any way I can get through without learning to use code?

 

[AccessBlaster]

Have a look here for ideas on surveys. http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html

hth
Richard

 

[Dartos]

Thanks Richard. I'd seen that in my searching and its the outcome I'm looking for (though much more detailed and complex than I need) but I can't figure out how to make mine like that or to change that to be mine.

Is what I'm looking to do really complex? Should I just tell my supervisor that he has to pay someone in IT or a "database professional" to do it? I'd like to do it myself because we don't really have the time of budget but if its beyond me I don't want to waste days trying to do it in vain.

 

[Dartos]

I managed to get copy and paste to work had to click the top cell when it was a + cursor not the text one. So that makes half of the first question much less of a problem. I can't just copy and paste the respondent ID though since it is in the format 1,1,1,2,2,2 not 1,2,3,1,2,3 so copy/paste would just leave me with 1,1,1,1,1,1. Any tips?
Can copy and paste from excel! So I just made 2 columns with all my numbers (I know and understand excel formulas!) and copy/paste victory.

Now just need to figure out how I can use QuestionType to determine the type of answer allowed.

Edit: Not quite as easy as I thought.
Edit: Slowly making progress.