Survey Response Database - Forms

[Kathryn]

Hello all,

I am posting this in both Database Design and Forms Coding

I have been asked to create a database application that is
turning out to be way beyond my knowledge and experience!
I'm not sure if the problem(s) are with the data structure
or my limited programming knowledge. I will say that in
the past I have run into problems because my data is "too
normalized" (if that is possible) so most queries are not
updateable.

Here is my task - create an application for evaluating
proposals that allows multiple evaluators to rate and
score each proposal. There are multiple categories with
the option of choosing more than one response. Some
responses have sub-categories within them so if the
category is "Goal" and "affordable housing" is chosen, the
evaluator needs to then select sub-categories such
as "conversion", "new construction", "re-zoning" again
choosing more than one if applicable. If they were to
choose "economic opportunities" as their category, they
would then be able to choose sub-categories related to
that choice. Secondly, there are questions corresponding
to each category that have up to 6 weighted options of
which only one can be chosen. The evaluators want to be
able to see each other's responses to all questions and
categories (here I though a tabbed sub-form would work).

Here are my primary tables (not listed are contact and
correspondence info tables)
Each RFP (RFP_ID) will have one set of categories,
questions and possible responses
Each Grant (gt_id) will have to capture responses made by
multiple evaluators to the set of categories/questions of
which the Grant is a member
tblRFPKey has RFP_ID as PK
tblGrantStatus has gt_id as PK and RFP_ID as FK
tblReviewKey has Review_ID as PK and Reviewer_ID and gt_id
as FK (new record each time a reviewer evaluates a
proposal)
tblCategoryKey has Category_ID as PK and RFP_ID as FK
(lists each category possible for the particular RFP -
ex. "Goal")
tblCategoryOptionKey has Category_OptionID as PK and
Category_ID as FK (lists each possible choice for each
particular category - ex. "Affordable Housing")
tblSubcategoryKey has Subcategory_OptionID as PK and
Category_OptionID as FK (lists each possible choice for
each particular sub-category - ex. "New Construction")
tblQuestionKey has Question_ID as PK and RFP_ID as FK
(lists each question possible for the particular RFP -
ex. "Effectively connects to target population and needs")
tblQuestionOptionKey has Question_OptionID as PK and
Question_ID as FK (lists each possible choice for each
particular question - ex. "not present," "somewhat," etc. -
each with a corresponding numerical value)

Then the response capture tables:
tblCategoryResponses has many-to-many relationships to
tblCategoryOptionsKey through Category_OptionID and to
tblReviewKey to Review_ID
tblSubcategoryResponses has many-to-many relationships to
tblSubcategoryOptionsKey through Subcategory_OptionID and
to tblReviewKey to Review_ID
tblQuestionResponses has many-to-many relationships to
tblQuestionOptionsKey through Question_OptionID and to
tblReviewKey to Review_ID

Is there some way for me to create the evaluation form
with the actual values from the Category and Questions
keys (by RFP) as the text of the questions (this way I can
use the same form for subsequent RFP which will have
different categories and questions)? Do I use unbound
check boxes backed up by code to capture the responses and
populate the appropriate tables?

I realize this is a huge request. I purchased the WROX
programmer to programmer last night and plan to work
through it this weekend. That said, any advice or
guidance you may have relating to any part of my quandary
would really be appreciated!

Thank you!

Kathryn

 

[PC Datasheet]

Contact me at my email address below if outside fee-paid help is an option
for you.