Normalization and form design

[SaviorSix]

Dim db As DAO.Database
Dim rs As Recordset
Dim strSQL As String
If Me.QuestionID = "Q22a" And Me.txtActualAnswer = "0" Then  'If Mental Illness: None, then skip to Q23a
 Me.Dirty = False
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("qryQuestionSkipQ22a", dbOpenDynaset)
If Not rs.EOF Then rs.MoveFirst
        Do While Not rs.EOF
        strSQL = "INSERT INTO tblStudyData ([ActualAnswer]) VALUES(" & 99 & ") WHERE " & Me.QuestionID & " =" & rs!QuestionID & ");"
    DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        rs.MoveNext
        Loop
 
        Set db = Nothing
        Set rs = Nothing
    Else
 
    End If
Me.Form.Requery
 
End Sub

If a value for a certain question is selected, Im trying to auto-populate the answers for the next several questions, almost like a skip-pattern

so in this case, if "0" is selected for Q22a, then "99" is filled in the ActualAnswer for Q22b thru Q22g. If the value for Q22a is changed, its not a big deal if the "99"s get deleted or not, thats why I omitted the delete SQL string.

My syntax must be off Craig, what do you think? By the way I placed this in the AfterUpdate of txtActualAnswers (the bound control)

 

[CraigDolphin]

what do you think? By the way I placed this in the AfterUpdate of txtActualAnswers (the bound control)

Well that's your first issue. The code should run in the afterupdate event of your answer combo as the afterupdate event of the bound textbox does not get fired when that value is added from code.

As for the code itself, there are some issues.

For starters, think about what you're doing here. If the user answers question 22A with a particular value....how does the database know you want the specific questions 22B through 22G to be updated?

Currently, your code says: update all records, that have the exact same questionid, that the person just answered: regardless of which person the record belongs to. Remember, to the db, 22B is a completely different, and unrelated, question ID to 22A.

So, do you really want the code to update the values for all identical question id's for all people? or do you want something more specific?

Some hints:
Why do you need a recordset for this? (Why not simply execute a sql update statement?)
Learn about the left() function.
Learn about where clauses with multiple conditions.

If you need more help on this I'll answer more tomorrow. But see if you can figure it out with those hints and the preceding questions.

 

[SaviorSix]

You're right, now that ive looked into it, seems like an UPDATE is a whole lot simpler and would do what im looking for.

Private Sub cboActualAnswer_AfterUpdate()
 
'this is to take the combobox selection and put it into the txt box
Me.txtActualAnswer = cboActualAnswer.Column(2)
 
'insert query begins here
If Me.QuestionID = "Q22a" And Me.ActualAnswer = 0 Then
 
strSQL = UPDATE tblStudyData SET tblStudyData.ActualAnswer = "99"
WHERE (((tblStudyData.QuestionID) Between "Q22b" And "Q22g") AND ((tblStudyData.PatientID)=[Forms]![frmMain]![txtPatientID]));
 
End if

This "should" be entering 99 as the actual answer for the fields where the question id is Q22b, Q22c, Q22d, Q22e, Q22f, Q22g, and where the patientID matches what was entered into the patientID textbox, if "0" is selected from the Q22a combobox.

I used the query designer in access and then looked at the view->SQL view to make sure I had the syntax right. I copied this into the VBA AfterUpdate but ive been constantly getting the "Expected: End of Statement" right after the UPDATE TableName section.

Im not quite sure why a Left() function would be used?


------------------
As a sidenote:
I was using the txtActualAnswer afterupdate, because some of the answers are free-text, and not combo box selections, so for these questions, I have

cboActualAnswer_GotFocus()
If Me.QuestionID.Value = "Q01" Then Me.txtActualAnswer.SetFocus
If Me.QuestionID.Value = "Q04" Then Me.txtActualAnswer.SetFocus
If Me.QuestionID.Value = "Q05a" Then Me.txtActualAnswer.SetFocus
 
'etc etc etc

Yes its primitive, but because of the nature of the end users, they will have the tendency to always click the drop-down box, and then enter a number, which causes a not-in-list error (i cant set LimitToList = no, because the bound column is hidden)

 

[CraigDolphin]

I'd use the not in list event of the combo to take the entered value and enter it directly into the textbox myself...

The left function would be used to identify records with the same root without hardcoding the question id's into the code.

Let's say that 3 years from now they add a new question 22H...

using left() to identify all records with the root '22' would still work fine without editing the code.

using between 22B and 22G would then need to be edited and fixed to allow for the new question.
<air code>

Dim strSQL as String
'this is to take the combobox selection and put it into the txt box
Me.txtActualAnswer = cboActualAnswer.Column(2)
 
'insert query begins here
If Me.QuestionID = "Q22a" And Me.ActualAnswer = 0 Then
 

strSQL = "UPDATE tblStudyData SET tblStudyData.ActualAnswer = '99' " & _
         "WHERE ((tblStudyData.[QuestionID]<>'22A')) AND (Left(tblStudyData.[QuestionID],Len(tblStudyData.[QuestionID])-1) = '22') AND ((tblStudyData.PatientID)=[Forms]![frmMain]![txtPatientID]));"
'Docmd.Setwarnings False 'hides message box notification when adding the records
Docmd.RunSQL strSQL
'Docmd.Setwarnings True 'turns messaging back on
End if
'Don't forget to add an error handler that includes the line Docmd.Setwarnings True in 
'case an error occurs that leaves you with messaging turned off

 

[SaviorSix]

Oh ok! I wasn't using the quotation marks for the sql string

Craig you have increased my knowledge exponentially and Im not sure how to thank you enough for your help and the time you've taken out of your schedule to instruct me as to what the solutions are to my questions.

 

[CraigDolphin]

Yep....although my example does other things too. Like declaring the string variable before using it. You may want to investigate using the Option explicit statement on the second line of the code module to enusre you cannot use variable without declaring them and specifying their data type. It also helps avoid finding typos in variable names that cause your code to behave unexpectedly without apparent errors etc.

Glad I could pass along some of what I've learned here.

 

[SaviorSix]

Hey, I have a question about the Answer table and its relation to the Question table. The Question table has a 1 to many relation with the answer table. As you know the answer table is used as the rowsource for the actual answer combo.

AFter testing out some queries, I am wondering if this is the ideal setup? Since not all of the answer fields use a combo box, some are free text. The only other way I could think of doing this would be to use the answer table as simply a lookup table, remove all its relationships, and write in a hundred IF statements to change the rowsource depending on the question...

 

[CraigDolphin]

Is this the ideal setup? I don't know...depends on whether it works well for you.

Personally, I'd NOT want to hard-code your row-source options to deal with a hundred different scenarios. Think about what you'd have to do any time you want to add, edit, or delete an 'answer' to a question in that situation. Now compare that to just adding, editing, or deleting a record from the answers table.

Now, just because there are 'Answers' in the Answers table, that doesn't mean your Actual Answer field is constrained to just those values. You would be so constrained if you were storing a fk in your actual answer field, but you're actually storing the answer value, or the free text so you're free to enter whatever value you want. You could add code to the bound text box's after_update event (which will be triggered by any user-typed entry in the control) to ask the user if they want to add the new value into the drop down list for that question. If they respond yes, then use a sql staement to add that as a new record in the answers table (along with the relevant question ID).

Is this the most efficient use of space? Well, maybe not. Ideally, you'd be storing the fk to the Answer table's pk, rather than the actual answer value. However, if you go that route then you need to force the issue regarding adding the value to the drop down list.

It's up to you how you want to go. In this situation, I'd probably live with the small denomalisation. But if it bugs you, then by all means, fully normalize the design and write the code necessary to ensure that all user entries are added to the answer table.

I'm curious though, you said you tested out some queries ....what situation arose that made you think this approach was not ideal?

 

[SaviorSix]

You have the scenario exactly... I'd much rather live with a small detour from normalization which makes things way easier in the long-run.

I tried to design a query which would list the patientID(tblStudyInfo), the questionID (tblStudyInfo) the question Text (tblQuestions), the actual answer (tblStudyInfo) and the answer text (tblAnswers)

Because the answer fields requring free text are not in the answer table, the query results do not show the questionID, QuestionText, or actual answer for those entries. The query only shows the information from the fields which draw the answers from the answer table lookup. Not sure if I explained it correctly...

 

[CraigDolphin]

First off, you are storing the actual text of the answer in the ActualAnswer field, regardless of whether it's a pre-determined answer, or a free text response, so there should be no need to add the AnswerText field to that query.

Secondly, if you really want to do that anyway, just make the join between tblStudyInfo and tblAnswers an outer join (either a left or right join). This will return all records from tblStudyInfo and only those records from tblAnswers that match, if there is nothing that matches it will have a null.

What field exactly are you performing your current join on between the two tables? The only common field that I see is the question ID and that will potentially return many records for each question which seems like a pretty unhelpful result to me.

 

[SaviorSix]

I provided some screenshots in the attachment


Im actually storing the AnswerValue field into the ActualAnswer wherever a combobox is used, this is because a number needs to be stored. (The statistician uses numbers to analyze the data). I just wanted to be able to associate the text with the answervalue (number) for other users besides the stats person


As you can see in the query result, it returns questionIDs Q02a, Q02b, Q03, Q06. Fields Q01, Q04, and Q05 have values, but are not being returned because their actual answer does not have a corresponding value in the tblAnswers (they are the free text ones)

 

[CraigDolphin]

Hmm. So you're NOT using the exact database design we discussed earlier after all.

The way you have it now requires you to store every possible answer, even free text responses, as a separate 'answer' in the answers table. This means that for each new answer, you need to code the db to add that to the answers table, AND ALSO assign a numerical value for that answer.

Is your user likely to know the correct number to assign? If not, then where does the db find that information?

Also, notice how the join based on the AnswerValue field doesn't show to 1 to many symbols? That's a clue that there's something wrong with this. Essentially, you're trying to treat the join as a pseudo pk-fk relationship but it can't determine what the relationship is because you may have dupplicate values on both sides of the join.

You'd be much better off using the auto-pk field in the answers table for the join rather than your AnswerValue field and doing as I suggested earlier for the 'complete normalization option'. Then use your AnswerValue field in the Answers table to store whatever numerical value your statistician wants returned for particular answers.

But what you have now is the 'halfway' option that leaves you in no-mans land: all the problems and none of the advantages of either of the two options, and some additional issues unique to the middle-way.

 

[SaviorSix]

Oh boy....I think I may be screwed here


The join based on the AnswerValue field only exists in the query design view, its not in the actual Relationships. I pretty much just made that messing around...

I personally assign the AnswerValue and AnswerText for each question, because they are pre-set responses for particular questions. I get what you're saying about the answer table though: the free text responses need their own entry in tblAnswers. I thought tblAnswers would effectively be just a lookup table to make referencing the rowsource easier

Although, I can get a query to work that simply lists the patientID, QuestionID, and ActualAnswer.....I just cant get it to show me the AnswerText associated with the ActualAnswer....I suppose its not the end of the world

 

[CraigDolphin]

Well, sometimes adding the join in the query is just fine. The problem with this situation is that you're trying to store a pseudo key, instead of the text, but not all user responses will exist in that table so it won't work to cover all your contingencies.

My original suggestion treated the Answers table as a lookup table to make data entry easier. You actually store the full text of the selected answer, or the free text response that is typed in, not the key value. Now, there's nothing at all to say you couldn't also populate a 'AnswerValue' field in tblStudyInfo using the same method. Have the default value in the Answers table, and use code to assign that value when the user chooses a response from the drop down. The only other thing to consider is what value to assign if it's a free text response rather than something chosen from the drop down...

or better yet, just leave the Answer value in the answers table and include it in your query using the ActualAnswer field to (outer) join tblStudyInfo to the AnswerText field in tblAnswers. (and again, you'll need to be specific about what value to return if there's nothing that matches)

If you want to be fully normalized then thta's a valid approach also, you just need to use proper keys, and ensure that all responses are added to the answers table, and assign values for your statistician at the time they're entered.

There's more than one way to skin this cat but you need to choose one or the other and implement it fully. You can't have it both ways.

 

[SaviorSix]

My original suggestion treated the Answers table as a lookup table to make data entry easier. You actually store the full text of the selected answer, or the free text response that is typed in, not the key value.

Right, this is what I am doing (or aiming for, rather), however it seems as if the Answers table was solely a lookup, the combobox rowsource would return every value in the Answers table.
Under normal circumstances this would be fine, but for my purposes I need to limit the responses based on the question, therefore I related the QuestionID with the AnswerID, otherwise, like I alluded to before, Id be writing a million lines of code to change the rowsource criteria depending on which question was selected.

 

[CraigDolphin]

No. By including the questionId in the answers table you are able to show only the answers you want to show for each question...the rowsource is a parameter query referencing the currently selected questionID control value in your subform. The rowsource never changes, but the currently selected value on the subform does therefore the result of the query changes depending on which subform row you are on. You just need to requery the rowsource each time you change rows.