Hello!
Attached are two JPG files showing screenshots of my tables, and their relationships. I just wanted to see how my normalization looked to you guys and if im on the right track! This is for a survey type database with multiple separate surveys.
To be honest I was debating whether or not to use Autonumber for the PK, or my own manually entered PK.
The reason I used my own manual PK, is both for easy reference, (autonumbers aren't always in sequence, so it would be a pain in the butt to enter in the correct autonum in the FK fields) and because the likelyhood of the survey/question numbers changing is extremely small, almost a 'not-going-to-happen' situation.
I also figured this would help in making the Form a little easier to create - to be honest I was having a hard time wrapping my head around this if I were to use the autonumber. I do see where you're coming from though
Alright I see what you mean about there being possible problems down the road. And I have also listened to the advice of not using lookups at the table level.
Your point about never entering data into a table though...
I am creating this database with the intent on the user only entering two things through the form: The Study Number (and related info in that table), and the Actual answer to the question.
I am going to present this as a finished product with all of the Survey names and questions pre-entered by me (in the tables), since they have already been developed on the paper surveys. The user will then just select the answer to the appropriate questions using the form as an entry. This is pretty much the 'standard practice' where I am.
The second part to my question is about what you alluded to in your last post, Pat..
How do I design the entry forms for this survey? This has been killing me! I'm trying to create a design where the user will enter the Study Number (unique ID of person filling out the survey), then, select which survey out of 12 possibles. (I was thinking maybe a tab control with 12 tabs?)
Each survey has a series of different questions, and each answer is either a free text or a lookup. Other examples I have researched have required the user to enter both the question number, and also the answer.
However, I am trying to design the form so that depending on the form selected, the questions will automatically populate, and the user will just have to select/enter the appropriate answer.
SaviorSix, the design you've shown is lacking some of the tables that I suggested you would likely need in your other thread (http://www.access-programmers.co.uk/forums/showthread.php?t=147602) so it's hard to talk about how I would design the forms given that you haven't adopted the structure I suggested.
SaviorSix said:
Each survey has a series of different questions, and each answer is either a free text or a lookup. Other examples I have researched have required the user to enter both the question number, and also the answer.
The general way I would approach this is to use the after_update event of the control where the user selects the survey number to run an insert query to automate adding the list of relevant questions to the underlying table where the actual answers are stored.
So the user selects the survey which triggers code that adds all the relevant questions to tblQuestionAnswers, filling the questionID field, but leaving the answer field blank. Then, refresh the subform that is bound to that table so that those records are shown and the user can just enter the answers. You would also need to use some validation code to ensure that all questions are actually answered unless you specifically want to allow null responses to be stored.
Additionally, you would have to write some code/sql to delete any of those entries if the user changes the selected survey id.
Which fields you would use to establish the master/child link between the form and subform is going to be tricky because your structure does not seem to allow you track all the entities at the logical level that I think you need.
That said, anything Pat says to do with Access is 100% right in my book and if she disagrees with anything that I've suggested then do the smart thing and listen to her
SaviorSix, the design you've shown is lacking some of the tables that I suggested you would likely need in your other thread (http://www.access-programmers.co.uk/forums/showthread.php?t=147602) so it's hard to talk about how I would design the forms given that you haven't adopted the structure I suggested.
You're right, this particular project is a different one from the one I was talking about in that thread. It is along the same idea (survey), so I used your suggestions as more of a guideline.
This DB is a little less complicated, due to not needed separate time points. There is only a person taking the survey (StudyNumber), and their answers to the 12 surveys.
The general way I would approach this is to use the after_update event of the control where the user selects the survey number to run an insert query to automate adding the list of relevant questions to the underlying table where the actual answers are stored.
So the user selects the survey which triggers code that adds all the relevant questions to tblQuestionAnswers, filling the questionID field, but leaving the answer field blank. Then, refresh the subform that is bound to that table so that those records are shown and the user can just enter the answers. You would also need to use some validation code to ensure that all questions are actually answered unless you specifically want to allow null responses to be stored.
Additionally, you would have to write some code/sql to delete any of those entries if the user changes the selected survey id.
Beautious! That's great then, now I know there IS a way to do that. I can cross that bridge when I get to it though
Which fields you would use to establish the master/child link between the form and subform is going to be tricky because your structure does not seem to allow you track all the entities at the logical level that I think you need.
I've been tinkering with the table design and relationships, maybe I can post some changes here? Im interested in what you think I will need for this.
That said, anything Pat says to do with Access is 100% right in my book and if she disagrees with anything that I've suggested then do the smart thing and listen to her
I see. Well, then the structure I would likely adopt would be along the lines of
Code:
tblPeople 'list of people/respondents and I'm with Pat on the auto pk thing.
PeopleID (pk, auto)
StudyNumber (txt)
Initials (txt)
tblSurveys 'list of surveys
SurveyID (pk, auto)
SurveyName
tblQuestions 'list of question that may be asked
QuestionID (pk, auto)
QuestionText (text)
tblSurveyQuestions 'default list of questions for each survey
SurveyQuestionID (pk, auto)
SurveyID (fk)
QuestionID (fk)
tblPeopleSurveys 'main form bound to this table
PeopleSurveyID (pk, auto)
PeopleID (fk)
SurveyID (fk)
tblPeopleSurveyAnswers 'subform bound to this table
PeopleSurveyAnswerID (pk, auto)
PeopleSurveyID (fk)
QuestionID (fk)
ActualAnswer (probably text or memo)
You'd have a main form (single form view) bound to tblPeopleSurveys, and a subform (continuous form view) bound to tblPeopleSurveyAnswers.
It should be relatively straight forward to write some code in the after update event of the SurveyID control on the main form to run an insert query that adds the required questions to tblPeopleSurveyAnswers, along with the PeopleSurveyID (also from the main form). The key here is that there is a master-child field that can identify a particular survey taken by a particular person which links the main form to the sub form.
To do this you will probably need to learn to use recordsets in vba and write dynamic sql.
You'd have a main form (single form view) bound to tblPeopleSurveys, and a subform (continuous form view) bound to tblPeopleSurveyAnswers.
It should be relatively straight forward to write some code in the after update event of the SurveyID control on the main form to run an insert query that adds the required questions to tblPeopleSurveyAnswers, along with the PeopleSurveyID (also from the main form). The key here is that there is a master-child field that can identify a particular survey taken by a particular person which links the main form to the sub form.
Im assuming this master/child field is going to be PeopleSurveyID?
On a side-note, shouldn't the "QuestionID" FK in tblPeopleSurveyAnswers instead be "SurveyQuestionID" ? That way the correct list of questions, depending on which survey, is related? Im struggling to view this in my head without tangible data to see.
Remember, the question is an entity in it's own right. The table tblSurveyQuestions exists solely to allow your database to construct a list of questions when you tell it which survey you're about to enter. One question might occur in several different surveys, or only in one.
Storing the SurveyQuestionId would be redundant in tblSurveyPeopleAnswers since you already know which survey this is (from tblPeopleSurveys) and you know which Question this is (from tblSurveyPeopleAnswers).
If you post a copy of the db (in AC2K format) I can whip up a form/subform example for you to look at.
Storing the SurveyQuestionId would be redundant in tblSurveyPeopleAnswers since you already know which survey this is (from tblPeopleSurveys) and you know which Question this is (from tblSurveyPeopleAnswers).
Have a look at the attached and see what I was talking about.
Some notes:
1. I used an auto pk PeopleID field because it will never bite you. You can still use and select by your 'study number' field but work with the auto field to save yourself grief in the future.
2. I've added a multi-column index on tblPeopleSurveys so that you can only enter each person-surveyid combination once. This prevents accidental double entry of the same survey for the same person. If a person is likely to take the same survey more than once, you will need to remove this index.
3. You'll need to use the not_in_list event of the PeopleID and SurveyID combos on the main form to deal with situations where a person who is new to the db, or a new survey is encountered. This will probably mean opening a form in dataentry mode bound to the tblPeople or tblSurveys, respectively then requerying the combo box's row source. I haven't coded that for you but there's lots of code examples on the forum/web that you can find and adapt for the purpose.
4. I wrote some code to automate adding the questions when you select the survey
5. You will be unable to enter the survey until you have entered the person.
6. you will be unable to answer questions etc until you have entered both the person and survey
7. I added an Answer field since that seemed to be missing from your model
You'll need to do a lot of tweaking and adding forms for defining survey question lists etc to make the db useable but it should give you the core of what you need.
I'm a VBA total novice, (i only understood about 40-50% of the code at face-value) so Im amazed at a lot of the nuances in the code, for instance: I would have never realized that I had to use the Nz function, or the .Dirty, or a whole lot of other things!
Not sure how long it took you to learn how to write all this at the drop of a hat, because I would one day like to get to this point.
ps im working on the NotInList event... this is a (very)rough draft
Code:
Private Sub PeopleID_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String
If NewData = "" Then Exit Sub
Msg = """" & NewData & """ is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add this Study Number?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part #...")
If i = vbYes Then
DocName = "Form1"
'Form1 is the form with recordsource frm fmPeopleID
DoCmd.OpenForm DocName, acNormal
DoCmd.GoToRecord acDataForm, DocName, acNewRec
End If
End Sub
Nulls are a pain in the backside but I think it's a good idea to always think about whether unexpected nulls might propagate through your code and set little traps for them with the Nz function.
The .dirty thing was simply to force the current record to save to the table before running the insert statements. Otherwise, the code would be trying to enter a fk value in one table when the corresponding pk value in the other table had not yet been saved. This would violate referential integrity at the table level and cause the insert to fail. Every now and then this situation trips me up so I have learned to force the save first. There may be a better more elegant way to handle this but this is the solution that I have that works for me.
Overall I guess it took me about 1 - 1 1/2 hours for me to whip up the form and code in the example. However, many hours of trying stuff and debugging over the last couple of years goes into that. Knowing to avoid pitfalls in advance speeds up the process more than you would believe. Experience really is the best teacher. Also, working with a properly normalized table design makes the job much easier since a lot of functionality exists in the forms themselves if your table design is right.
I suspect that someone like Pat, or some of the other 'gurus' here, could have done it much more quickly and with better results. Someday I hope to get somewhere close to their level of accomplishment though the air's pretty thin up there for a biologist like me.
As for your Not in List code, I think you've got a couple of things to work on.
There's a variable that is returned from the event after the code runs called 'response'.
You set the value of the response variable in your code to one of the follwing three values:
AcDataErrContinue (Suppresses the standard error message)
AcDataErrAdded (Suppresses the standard error message,and refreshes the entries in the combo box)
AcDataErrDisplay (Displays the standard error message)
So, if the user selects 'no', they don;t want to add the new value to the list, you should probably return response = AcDataErrContinue.
But if they do add something to the list, then you want the user to be able to select this new value from the combo list. One way to do this would be to use your code to requery the combo box's rowsource.
However, you probably don't want to make them actually select it again after it is added which is what you would have to do using the requery method. If you use response = AcDataErrAdded then the form requeries the rowsource of the combo AND leaves the new item selected aftre you exit the not in list code.
Also, when you open the form, you should probably open it in dialog mode. When you are running code, the code will keep running until something explictly stops/pauses it. If you were to run your code as written, the form would open, and the rest of the code would keep running before your user has a chance of inputting something new. By opening it in dialog mode, the code pauses until the form closes again. That way, your user adds something new to the table on the open form, closes it (which saves the data into the table), then the code resumes and lets the new information in the table show up when you exit the not in list sub.
Something like....
Code:
Private Sub ComboNameHere_NotInList(NewData As String, Response As Integer)
If MsgBox(NewData & " is not currently in the list. Would you like to add this now?", vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "form_name_here", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
Else
MsgBox "Please enter a valid item from the drop down box."
Response = acDataErrContinue
End If
End Sub
Is it possible for this insert query to work (populate the table, and subsequently, the fields on a continuous form) In a database that does not have the relationships that you outlined in the example you showed me?
This is a much much simpler survey design than the other ones I have posted. This has only one set of pre-defined questions, with pre-defined answers that do not change. I am trying to set up the form so the person number is entered (main form), then the subform is populated with the list of questions (this list is the same all the time), all the person has to do is select the answer.
I have attempted to use the insert query you wrote for me, however it does not seem to work in this more simple design
(ps. Yes, I know, I know....I didn't use autonumbers for some of the PKs. This db is a one-shot deal, the manual keys I used are never going to change anyway, guaranteed)
Sure it's possible...you just need to make sure that you reference the correct table name and deal with all the various permutations of user behavior (like accidentally re-entering the same value, or clearing the patientid box) and avoid situations where you accidentally create multiple versions of the same questionaire for the same person. In other words, if the user changes the patient id then you want to delete all the old questions and answers for the previously entered person id before creating the new set. You also need to insert values into both the peopleid field and the questionid field or nothing will show up. You also forgot to ensure that the main form record is saved before running the code which would have prevented the code from actually inserting anything because it would be violating referential integrity.
You could add a bit of code to notify the user about the consequences of editing the peopleid textbox but that's up to you.
Here's the example back.
Your survey datamodel parameters seem to change every week
Haha well the standard operating procedure here, is to create a 'flat-file'. It has been that way from long before I arrived, im just trying to adapt the various current projects and slowly progress to making them normalized. They usually follow the same type of formats that I have presented here.
I have changed the ActualAnswer textbox to a combobox. I was hoping to make this a cascading combo referencing tblAnswers. In the row source for cboActualAnswer, I referred it to the QuestionID combo (the autopopulated one), put a me.cboActualAnswer.requery in the after update of the QuestionID combo, and still nothing shows. I am wondering if this has anything to do with either the fact that the form is continuous, or that the field is auto-populated?
I have changed the ActualAnswer textbox to a combobox. I was hoping to make this a cascading combo referencing tblAnswers. In the row source for cboActualAnswer, I referred it to the QuestionID combo (the autopopulated one), put a me.cboActualAnswer.requery in the after update of the QuestionID combo, and still nothing shows. I am wondering if this has anything to do with either the fact that the form is continuous, or that the field is auto-populated?
Hmm. I'm not sure but think you may need to be a bit clever here.
For starters, on a continuous form, the value of Me.QuestionID depends on which 'row' is currently selected. When you enter the answers combo you have also selected that row in the continuous form, and you want only the answers to the question for that row to be displayed in the combo, yes?
The way I would address that is to use a parameter query as my row source for the answers combo (referencing the QuestionID control on your continuous form as a parameter), and use the on enter event of the answer combo to requery the rowsource.
The problem with that is that usually when you switch to a different row, only the answers for that row will be visible in the combo....and it may look as if the combos for the other questions (which you have already answered) will be 'empty' even though they're not.
What I do in this case is to have an unbound combo box with the row source dependent on the questionid value in the current row. I then use the after update event of the unbound combo to put the selected value into a textbox bound to the answer field.
That way, the entered value will always be visible even if the user has selected a different row in the continuous form.
The bit I'm unsure about is if this value visibility issue will arise in a combo where the first column in the row source is not hidden. So, you could try it with a bound combo only first, and if the issue arises then you'll at least know how to workaround the issue.
Hmm. I'm not sure but think you may need to be a bit clever here.
For starters, on a continuous form, the value of Me.QuestionID depends on which 'row' is currently selected. When you enter the answers combo you have also selected that row in the continuous form, and you want only the answers to the question for that row to be displayed in the combo, yes?
The way I would address that is to use a parameter query as my row source for the answers combo (referencing the QuestionID control on your continuous form as a parameter), and use the on enter event of the answer combo to requery the rowsource.
The problem with that is that usually when you switch to a different row, only the answers for that row will be visible in the combo....and it may look as if the combos for the other questions (which you have already answered) will be 'empty' even though they're not.
What I do in this case is to have an unbound combo box with the row source dependent on the questionid value in the current row. I then use the after update event of the unbound combo to put the selected value into a textbox bound to the answer field.
That way, the entered value will always be visible even if the user has selected a different row in the continuous form.
The bit I'm unsure about is if this value visibility issue will arise in a combo where the first column in the row source is not hidden. So, you could try it with a bound combo only first, and if the issue arises then you'll at least know how to workaround the issue.
Genius! that worked like a charm, I have the textbox overlapping the combo, so you can't even tell there are two separate controls. Nothing like a little jerry-rigging
The "On Enter" event for cboActualAnswer also worked, instead of the After update of the QuestionID
