I'm designing a survey database. The survey is mailed to members and returned to us. A member will get more than 1 survey (purpose is to track the quality of health) Once returned we will input the responses from the questions on the survey. The purpose is to determine if the members quality of health improved or not over the time between the 2 surveys.
Each response has a numerical value to it. Each response varies...some Y/N, some T/F, etc.
tRAND (primary table) holds: "for each question/response there is a new record"
SurveyDate (short date)
TRANSFORM Sum(t_RAND_Responses.Response) AS SumOfResponse
SELECT t_RAND_Responses.pkQuestionID AS [Question#], t_RAND_Responses.pkDemoID AS ID, Sum(t_RAND_Responses.Response) AS Evaluation
FROM t_Questions INNER JOIN t_RAND_Responses ON t_Questions.pkQuestionID = t_RAND_Responses.pkQuestionID
GROUP BY t_RAND_Responses.pkQuestionID, t_RAND_Responses.pkDemoID
PIVOT Format([SurveyDate],"Short Date");
which of course is the recordsource for rSurveyEvaluation where a graph is included and each questions response value per survey is reviewed.
My problem is:
I have the input form for the survey which has ALL the questions and a combo box for each answer. Currently this form is UNBOUND because I don't know the BEST method to get the data to the tRAND. Keeping in mind that each question/answer will create a new record, my thought was to use an "Update to table" method. It would also have to include the SurveyDate. I just don't think that's the best approach and was hoping someone with far more vba skills than myself would point me in the right direction.