Re: Survey Response Database - Forms

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: PC Data*** (nospam_at_nospam.spam)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 15:51:20 GMT

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

--
                                        PC Data***
Your Resource For Help With Access, Excel And Word Applications
                              resource@pcdata***.com
                                 www.pcdata***.com
"Kathryn" <anonymous@discussions.microsoft.com> wrote in message
news:19e201c4d23b$6d45f060$a501280a@phx.gbl...
> 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
>
Quantcast