Correct Relationships

[goslincm]

Good morning, struggle to establish proper relationships.

tbl_survey

surveyID pk
surveyName

tbl_questions
questionID pk
surveyid fk
question

tbl_answers

answerID
surveyID
questionID
answer

Question: in my tbl_answers what should my primary key be? combination of the three ID fields?

 

[fneily]

Take a look at:
ACC2000: Sample Table Design to Support Questionnaire Applications

http://support.microsoft.com/kb/q209493/

 

[Crowley16]

if all answers MUST have a survey and question linked, then you can use a composite primary key.

However, for the setup you have now, it seems sensible to have the pKey only on answerID.

If you wanted to create junction tables to implement many to many relationships, then you can have composite primary keys.

--------------------
Procrastinate Now!

 

[fneily]

Here's another one to look at:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Scroll down until you see the samples from DHookum, then look for At Your Survey

 

[goslincm]

Thanks for the references, I've been dissecting them. Currently I have what to you will be a simple problem:

"Control can't be edited, its bound to autonumber field"

I'm trying to duplicate what you referred me to and when I created a combobox and want to select from my list of survey's I'm getting that error.

The table structure for tbl.survey had surveyid set as an autonumber field, so I did the same.

 

[dhookom]

It sounds like you have a combo box set up on a bound field that is an autonumber. This would not work. The combo box should not be bound.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

Thanks Duane. By the way I love your At Your Survey. As a very novice individual at this end, it is a great model to have to look at. Although it appears more complex than I need or should say can handle, I'm trying to work my way through it to get a better understanding of the whole process of setting up my tables to handle a simple survey.

Thanks

 

[goslincm]

Duane, hopefully your still out here as I'm struggling.

In going back and looking at the AT YOUR SURVEY application, I am trying to follow it and create things at my end so I can learn from this.

At present I have created:

TblSurvey
surveyid pk
survey name

TblQuestions
questionid pk
surveyid
question

TblResponses
responseid pk
questionid pk
response

TblResponders
responderid pk
surveyid
respondername

So with this table structure I thing drew together the relationships. THEN, I added a couple of entries into the survey table. I THEN started to create the first part of the form similar to your form called: frmSurveyResponses

I'm just trying to get the first three fields onto that form as you have. Now the first field is a combobox, and it appears the record source is surveyid, yet when I tried that the first time (as indicated in my first post) I received an error. So, based on replies, I unbound the field, and sure I can select a survey, but I cannot get it to auto assign a number in the responseid field.

That is where I am at-stuck.

Can you push me (not over the edge though please, I'm close)

 

[dhookom]

What is the record source of the form with the combo box? I believe it should be TblResponders. I would expect to see responderid in TblResponses to identify which responder was answering which question.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

Yes, got it. It was tbl.responses. Thank you Duane.

 

[goslincm]

Dealing with the same AT YOUR SURVEY program I was referred to, to get a better understanding on setting up my tables, I'm trying to understand VBA.

Specifically, in the OnOpen event for frmSurveyResponses, this code is there:

' In the OnOpen Event
Private Sub Form_Open(Cancel As Integer)

cmdAdd_Click

End Sub

I know nothing about VBA, but I do know that I somehow need to set up the similar code to work at my end. So I went into View ObjectBrowser, and for AT YOUR SURVEY (as the library?), I see all these "CLASSES" and next to them MEMBERS OF <globals>

When I click on frmSurveyResponse CLASS, I see all the MEMBERS and I know I need this....

Can someone tell me what I need to do to use these? Can you copy and paste somehow, or do I need to create my own somehow?

Appreciate any help, thanks.

 

[BobRodes]

gos, maybe it would be useful to take a step backwards and revisit your structure. Some issues that I see are that a responder can only respond to one survey (perhaps that's what you want), and that a person can have multiple responses to the same question, which seems a bit counterintuitive.

If you want to revisit your structure, the first step is to identify your "kernel entities". "Kernel entity" means basically "stuff you're going to keep track of". To, it looks like you have four kernel entities:

1. Surveys
2. Responders
3. Questions
4. Answers

The next step is to determine the relationships between these entities, and whether they are one to many or many to many. What kind of answers you have in your survey has some bearing on this. Is this a multiple choice or a written in answer type survey? If the former, you can have multiple answers for each question: a combination of correct and incorrect ones. If the latter, there will be only one answer per question and you can merge questions and answers into one entity.

For the purpose of this discussion, let's assume that you have at least some multiple choice answers in your survey. Let's also assume that a person can take more than one survey. These given, you actually have a direct relationship between responders and questions, and not responders and surveys. (If a "responder takes a survey", you have to look up the questions that are in that survey and present them to the user. You may keep a record of what surveys a user took, but that's outside of the scope of the mechanics of presenting the user with the survey questions. From a data storage standpoint, what survey a responder is taking is a separate issue from what questions he's answering.) Now the point about this is that each responder answers many questions, and each question is answered by many responders (we're assuming that the same responder can't take the same survey twice, by the way). So you need an associtive entitiy to resolve the many to many relationship between answers (note NOT questions) and responders. Let's call it Responses, since that's what they are. You will also need an associative entity for the

That given, we will implement our design with five tables:

1. Surveys
2. Responders
3. Questions
4. Answers
5. Responses

These tables will relate as follows:

1. Surveys 1 Questions *
2. Questions 1 Answers *
3. Answers 1 Responses *
4. Responders 1 Responses *

Ok, that's a first pass. If you like, see if you can find any requirements that this structure doesn't meet, and see if you can poke holes in my logic. (I always assume that there are plenty in the first pass!) Also, I'll let you work out what attributes go with each entity (i. e. what fields go in each table).

HTH

Bob

 

[dhookom]

The On Open event calls the same code as the cmdAdd command button which is:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click


    DoCmd.GoToRecord , , acNewRec

Exit_cmdAdd_Click:
    Exit Sub

Err_cmdAdd_Click:
    MsgBox Error$
    Resume Exit_cmdAdd_Click
    
End Sub

I wanted to use the same code without re-writing it. I could just as easily have written the code in the On Open as

Private Sub Form_Open(Cancel As Integer)

     DoCmd.GoToRecord , , acNewRec

End Sub

I usually don't do this for just one line of code which suggests there might have been more code in there that was removed.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

Bob, thank you so much for the information. In terms of this first survey to bring online, it requires only yes/no answers. However I know at some point a multiple-choice will come along.

Also, this particular yes/no survey is one that must be completed each fiscal year. So, in here are my entities:

tblResponders
responderID pk autonumber
surveyID pk number
year pk number

tblSurvey
surveyID pk autonumber
surveyname text


tblQuestions
questionID pk autonumber
questiontext text
surveyID fk number

tblAnswers
answerID pk autonumber
answertext text
questionID fk number

tblResponses
responseID pk autonumber
responderid fk number
surveyid fk number
year fk number

I'm not sure about this Bob. I would need a composite primary key field for my responders table wouldn't I, since they will be answering more than one survey??

 

[BobRodes]

<I would need a composite primary key field for my responders table wouldn't I, since they will be answering more than one survey??

Ok, so you need to keep track of which surveys a responder responded to. Can't you do that by evaluating which ANSWERS a responder responded to, given that a single answer goes with one and only one survey?

Also, even if that were not the case, the answer to your question would still be no! Or perhaps "not exactly."

Let's take another step back here and take an example that extracts the concept at hand from the other complications. Consider a library. A library has books, and it has patrons. Patrons borrow many books, books are lent to many patrons. How would you model that?

Bob

 

[goslincm]

Thanks for the patience with me Bob.

I would model the following:

TblPatrons
PatronID Pk
Name
Phone#

TblBooks
BookID Pk
Title
Author
PatronID Fk

TblJunction
PatronID Pk
BookID Pk

 

[PHV]

Could you explain why PatronID Fk in both TblBooks and TblJunction ?

 

[goslincm]

alright let me try again:

tblPatrons
PatronID pk
Name
PhoneNo.

tblBooks
Book ID pk
Title
Author

tblJunction
JunctionID pk
BookID
PatronID

My question in doing this is do I need tblJunction to have JunctionID as its PK so that I patron could check out the same book more than once? If I removed junctionID and had BookID and PatronID as the pK, then a patron would only check a book out once, is that correct?

 

[dhookom]

The junction table would also include a checkout date and other fields. I would leave in the JunctionID and create a unique index on BookID, PatronID, and the TransactionDate.

I am not sure how this thread became an exercise in table structures somewhat unrelated to the original question :-(

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

I guess this thread became an exercise in table structures because I keep lagging in that area and some individuals were having me take a few steps backwards in oder to get this. Sorry.

If you were making the BookID, PatronID, TransactionDate as your primary keys why would you still keep JuntionId in the table?