I am trying to fix a database that I didn't design and really don't have the knowledge. I am hoping for a bit of help.
The organization I am doing this with has a specific format for data entry that I need to use. All the questions for the surveys are on one sheet/form. All the questions on the form are in option boxes. I wonder if there is query I can use that will allow me to use the form and send the answers to the a normarlized table. Another thread on another site said it might be possible. I copied the code but I am not sure where to put it.
Private Sub btnAppendResult_Click()
On Error GoTo Err_btnAppendResult_Click
Dim SqlString As String 'This it the sql Statement to be run each time
Dim NumRecords As Integer 'This is the number of records to be appended
Dim Count As Integer 'This is the count of each loop action
Dim QStart As String 'This is the start of the Question control on the form the query will look for
Dim SStart As String 'This is the start of the Score Control on the form the query will look for
Dim txtQuestion As String 'This is the sql variable Question txt box control
Dim txtScore As String 'This is the sql variable score txt box control
QStart = "txtQ" 'Set the fixed part of the form Question Control Name
SStart = "txtS" 'Set the fixed part of the form score control name
Count = 1 'Set Count to 1 to Start
NumRecords = Me.QuestionNumber 'Set NumRecords to be the value in the control QuestionNumber on your form
Do Until Count = NumRecords 'Stop Loop when count reaches number of records on Form
txtQuestion = QStart & Count 'Set Control Name to be the full name for this loop
txtScore = SStart & Count 'Set Control Name to be the full name for this loop
DoCmd.SetWarnings False 'Stops any warnings
'Collect Data and append record to tblSurveyResults
SqlString = "INSERT INTO tblSurveyResults (StudentID, SurveyDate, CounselorID, SchoolID, TeacherID, QuestionResponse) " & _
"SELECT " & txtStudentID & " , Date(), " & txtCounselorID & ", " & SchoolID & ", " & TeacherID & ", " & QuestionResponse & "; "
DoCmd.RunSQL SqlString 'Append Response to the tblSurveyResults
DoCmd.SetWarnings True 'Start Warnings
Count = Count + 1 'Add 1 to count
Loop
Exit_btnAppendResult_Click:
Exit Sub
Err_btnAppendResult_Click:
MsgBox Err.Description
Resume Exit_btnAppendResult_Click
2nd questions- I know that there might be a way to take the table itself and wright a query that will move the data into a more normalized look/design. Is there a way to automate it though- having the data go through the non normalized table and then to the normarlized?