Subform survey problem

[KNJ]

Business Problem:
1) I'm creating a survey type application - it's small and simple.
2) Haven't worked with Access since version 2

Tables (simplified):
1) Interview
- ID
- Date
- Category
- Interviewer, Interviewees, blah, blah

2) Category
- ID
- Category

3) Question
- ID
- CategoryID
- Code (first letter of Category & sequential number)
- Question

4) Response
- ID
- Response (Yes = 1, No = 2, N/A = 3

5) InterviewResponse
- InterviewID
- QuestionID
- ResponseID
- Comments

Mandated pain in the butt factors:
- MUST use radio buttons for responses.
- WANT the 'continuous form' look and feel

Technical Problem:
After creating an interview (parent form) a user can view/hide questions the
that are associated with it. Eventually the questions will get answered but
the interview may be started and finished in different working sessions,
therefore, save whatever we've got. The tricky part is getting all the
questions to show up whether or not they've been answered yet. If I create a
query that joins Question (all of them) & Response (whatever we have) my
subform is OK (in terms of looks). The subform links to parent on the
Category and I get the correct questions displayed. However, I also get all
the responses ever entered. If I add the InterviewID as a link to the parent
then I only get the questions where responses exist (which is none in the
case of new interview). More succinctly, how do I display the Interview in
the main form, ALL of questions and their corresponding responses (if they
exist) in a subform ? Oh yea - If responses don't exist we have to allow the
user to select a response, maybe enter some comments, and then save that
record.

Please don't direct me to At Your Survey - I've been there already.

 

[tina]

suggest that each time the user creates a new interview, there is code
running to populate tblInterviewResponse with a record for every question in
that interview. so the records exist, and will all come up in the form for
each interview record - whether or not every one has been answered.

hth

 

[Duane Hookom]

Tina,
You can't suggest that solution since that is how it is done in At Your
Survey which according to KNJ:
"Please don't direct me to At Your Survey - I've been there already."

 

[tina]

LOL! well, great minds think alike, Duane...and so do the rest of us,
sometimes! i don't know if that's the best solution that ever was or ever
will be, but it makes sense, it's pretty easy to implement, it's seamless to
the user, it's the solution i've used successfully numerous times in my own
databases - and it's the solution i'd use in this situation, based on the
post-er's description of his/her setup. ...hey, that At Your Survey db is a
pretty good demo after all, isn't it - shall we ask for a show of hands?
<g>

 

[Duane Hookom]

The At Your Survey Demo certainly has a lot of miles on it.

 

[tina]

it's right up there with my Michelins - long life, a smooth, safe ride, and
good value for money!

 

[Duane Hookom]

"money" I must have missed that!

 

[tina]

LOL...well, okay, will you settle for the accolades of the masses? <g>