Automatically Populating a Subform... Append Query or Macro?

[TundraMonkey]

Hi...
I've designed a few databases but am not an advanced user, and am having some trouble figuring out what Forum questions and answers relate to my problem. I've found some references to Macros and Append Queries, but I can't find any of the functions they mention in Access 2013.
This database is small and simple, logging responses from interviews. For each interview there are 18 questions, but somebody confused matters by numbering them: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.1, 10.2, 11, 12, 13, 14, 15, 16, 17. Because of this, there have been some errors in entering the proper question number and subsequent responses. I have an Interview table (InterviewID, Year, Region, Name, Facilitator), a Questions table (QuestionID, Questions), and an Answers table (ResponseID, InterviewID, QuestionID, Response). For those entering data into a form, after adding the year, region, and names I would like a subform to be automatically populated with 1) the question numbers from the Questions table and 2) blanks for their answers to be entered.


I've spent a week on this, trying out what I thought were similar inquiries in the Forum. Any help would be greatly appreciated.
Thanks!

[NTC]

It is not an issue involving macros. You will have a table with the 'questions' (and their funky numbering). That's a static table. Then you have a table for interviews - and probably 2 tables: 1 for the Master info ie. the person's name/contact.....and a 2nd table for interview 'Answers' to the questions.

The Answers table would have field that is a look up field linking to the Questions table. So you select the question, and then the next field is a text or memo field to record the answer.

The rest is all design set up using forms.

[TundraMonkey]

Thank you very much for your reply. This looks to be giving the person entering data a list of question numbers that they have to select from before they enter the answer, but it seems easier to just have them type it in. This, however, brings me back to the same concern about the data entry errors due to the weird numbering of questions. Instead of question numbers 10.1 and 10.2 the interview form actually reads 10 and 10a, so I was trying to avoid having the person entering data enter the question number. Is there no way to have them see a subform with questions 1-18 appear when they begin to enter a new interview?

[NTC]

It is key to first appreciate the structure; the Questions Table in the future might change - let's say more or less questions, along with the content of the questions. The structure suggested will easily support that.

Your question now is solely about the user experience - you want them to open to a screen that has all the questions' 18 records pre-entered (rather than they select and add each question as they go).... This is very do-able using some VBA for automation. When the new interview form is being opened, you need to trigger an Append query that writes in the 18 Questions into the Answers table with the cross reference to the person. It is as if the person just goes down and selects all 18 questions - leaving them all blank and then goes back to answer them...

The funky numbering/sorting is manageable however you wish it to be....you can control that in the Questions table.

[HiTechCoach]

What you are creating is also referred to as a survey.

As you are learning, creating a UI/UX is not easy.

I recommend checking out this example:

At Your Survey Click Here)

At Your Survey (AYS) is a full featured application that allows users to create their own surveys by designing the questions and providing a lookup of possible responses. AYS uses a fairly normalized table structure so the same tables, forms, code, queries, and reports can be used for any number of surveys. There is a brief manual to help you get started as well as a sample survey with data.