surveys and relationships

[auralsculptor]

I have seen references to this but really need a bit of a startup.

I am developing a survey with radio button responses eg yes, no maybe

I need to score these responses, (which vary - some questions yes is worth 1, some its worth 3) and then sum the scores

I have seen postings referring to 3 tables for survey, questions and answers but don't understand how to relate these. If someone could post a basic structure, I will be able to learn from it

[mr_vodka]

something like this...

survey.zip

[comment]

I think the basic structure would be something like the attached. Of course it can get more complex, depending on the needs.

There's an interesting demo by BruceR here, that's worth investigating.

[auralsculptor]

Thanks so much. This is exactly what I needed to get my head around the ID relationships.

[auralsculptor]

OK got this working nicely so far. The next step for me is that there are in fact 3 surveys to be completed by each participant, so I need to somehow make the "participant" the primary key to keep the data linked to the person. I'd like the script to generate the three surveys in portals on the same layout, I guess I need three import calls???

CPI_Database_EG.fp7.zip

[comment]

I don't quite folow the logic of your structure. You have a table called Survey with fields for name and surname. Seems like that is actually a table of Participants (called Subjects in my ERD).

The real Surveys table should have information about surveys (by "survey" I mean a group of question that will be presented to unlimited number of subjects).

[auralsculptor]

OK. Yes the current Survey's table really refers to subjects, and the Questions table contains the questions for multiple subjects. The upload is the halfway house when I realized that I should be able to do multiple tests for each subject and have them under a subject's record. The tricky bit for me is duplicating (triplicating?)the questions and answers tables and getting the relationship and the scripting right to make them come up on a portal as the first one does...

[comment]

I don't see the need to duplicate anything. That is the entire point of a relational database - data is only stored once, at a single location.

Granted, there's a problem of DISPLAYING the questions for each subject, even before he/she has replied to them. That is what the demo I mentioned earlier attempts to solve.

[auralsculptor]

The file I uploaded was adapted from mr-vodka's upload, which displays the questions really nicely but is only suitable for a single set of questions.

I am not wanting to duplicate the questions, but the format.

I'll explain more specifically.

each Subject will complete 3 sets of 60 questions, Which I would like to deliver one after the other in a single session, (page 1, page 2, page 3 - 3 layouts of the Subject record showing a different portal???) These measure different things and are assessed seperately. Their data must also be accessable seperately for analysis - hence the need for multiple tables (for each set of questions and their responses. Each set of answers must remain indentifiable to the Subject.

[comment]

So basically you are talking about 180 questions, to be answered by n subjects? Will this be the extent of the entire database, or do you plan to have additional surveys (with different sets of questions) in the future?

[auralsculptor]

At this stage there is just the 3 sets (60 x 3), but I'd like to keep it open to an additional survey to be added at a later date

[comment]

OK, then in my book you would have the following tables:

Surveys (currently only one record)

Questions (180 records, each record classified as belonging to one of three sets)

Subjects (one record for each person answering the questions)

Answers (one record for each individual answer, i.e. 180 x number of subjects)

The BASIC relationships are the same as shown in my diagram (you will eventually need more auxiliary relationships, as you create your interface).

The records in the Answers table can be created either automatically by answering the questions (that's what Bruce's demo shows), or you could import a blank set of answers (from the Questions table) before interviewing each subject. I don't particularly like the second method, but I thought it worth mentioning, because most beginners find it easier to implement.

BTW, this is not an entry-level project - be prepared to spend some time on this until you can get it right.

[auralsculptor]

Thanks

OK I'm going to look at this tomorrow (australian time)

what you say makes sense.

expect more posts this time tomorrow!

[auralsculptor]

OK The structure is making sense now, but I need to create a

conditional sum of four fields but only if a related field has a specific value. Kind of like a sum on a found set, without the "find". I would have several of these fields for various values of the related field

The logic works like this

sum (field1; field2; field3) if (tableA::fieldX = "1")

I found a "SumConditionalFields" custom function, but could not adapt it to my needs ....

[auralsculptor]

Apologies for double posting - its getting difficult to figure which forum area to post under ...

[auralsculptor]

Mr Vodka, much progress has been made but somehow between yesterday and today the script seems to have broken ...

It should create a new survey (from the new survey layout) and populate the portals with the correct questions from the user-responses table. I can not see what has happened ...

CPI_Database_EG.fp7.zip

[mr_vodka]

Take a look at this sample file. It accommodates for multiple surveys as well as incorporating Bruce R's technique.

keyword: survey

Multiple_Surveys.zip