Many to Many Relationships

[goslincm]

Hi, I'm fairly new to access, however have taken 2 pretty basic intro classes yet I cannot get through my head the concept of many-to-many relationships. I've read about it, I've looked at examples and they make sense until I try to apply my own situations and then I'm stumped. Can anyone offer my a new perspective on how to get this? I just can't determine my relationships and especially knowing when its a many to many.

 

[kabz]

Hi, I'm fairly new to access, however have taken 2 pretty basic intro classes yet I cannot get through my head the concept of many-to-many relationships. I've read about it, I've looked at examples and they make sense until I try to apply my own situations and then I'm stumped. Can anyone offer my a new perspective on how to get this? I just can't determine my relationships and especially knowing when its a many to many.

You might also want to look at this link

http://www.ehow.com/how_13626_define-many-many.html

 

[goslincm]

Many to Many

So, in trying to establish a relationship between tables: surveys, questions, answers, is it true that:

1Survery is related to many questions
Many Questions are related to Many Answers

yet I don't know how to related the responder table to this

 

[Banana]

One survey --> Many Questions

One question --> Many answers (you can substitute answers with respondents)

One respondent --> Many questions answered

Therefore, there's a many-many between questions and respondents.

One Answer --> One Respondent (unless you are allowing a respondent to re-take same test more than once).

The structure would be thus:

tblSurvey
SurveyID
SurveyName

tblQuestions
QuestionID
SurveyID (foreign key to tblSurvey)
QuestionName
QuestionType

tblRespondents
RespondentID
FirstName
LastName
....

tblAnswers (This is the junction table)
QuestionID (foreign key to tblQuestion)
RespondentID (foreign key to tblRespondent)
Answer (the actual input from respondent)

HTH.

 

[goslincm]

Yes the Respondent will actually be taking the same survey each year.

 

[Banana]

Actually, this statement I wrote:

One Answer --> One Respondent (unless you are allowing a respondent to re-take same test more than once)

Is supposed to be deleted, as the structure I listed above doesn't care how many time respondent re-takes a time.

 

[wazz]

Yes the Respondent will actually be taking the same survey each year.

then you'll probably want a (hidden) 'date' field in tblAnswer that will automatically update.

 

[goslincm]

Given the above table structure then, how does it come together in terms of a data entry form? I would enter information into my survey table first, then the questions that go along with that survey yes?

I guess I don't see how it gets to a point of a forms where when the respondent select the survey to take, that the corresponding questions show up in a subform?

 

[goslincm]

posting relationship layout

Alright, FINALLY, my relationships are attached. So, couple questions, if they look alright, where to I begin to create my forms so that data entry can be done.

What I was hoping for is that a user could select or bring up a particular user, then a particular survey, which in turn listed that survey's questions. I don't know how to do that, as well as bring in the form where they can answer each question.

Could someone walk me through the process of moving forward onces relationships are set up?