Results 1 to 5 of 5
  1. #1
    jomby is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    3

    Questionnaire DB from Scratch

    I've created the following tables in ms access db. The purpose is to be able to complete quality check on loan files. The loan file details are an extract from another database. The loan details will not be deleted or changed in ms access so I plan to use a linked excel workbook as a table. I will need to be able to tie back a particular questionnaire to a loan file in the loan file details linked table.

    I've populated the SurveyTbl, QuestionTbl, ResponseTbl, ResponsiblePartyTbl, RiskTbl with the acceptable values and have established the relations between the tables.

    *Each loan should have only one response, one responsible party and one risk level per question.

    My major questions involve adding a new record to a particular loan. I envision having a form which lists out all 80 questions, the user enters the ApplicationID once, then selects the Response, Responsible Party and Risk for each question. This is where I get stumped. How do I create a form that based on the Response, Responsible Party and Risk will update the key fields listed below without the user having to know what the key values should be? I've seen people create a form that updates values in a single table but structurally I was looking to create something closer to a true database.
    *Note I own 2 access books and they don't seem to address my question above as the books seem more geared towards data manipulation of a finished database (example create a query off existing data in database. create a form that shows data from existing data in the database. Alter the appearance of an existing form).

    SurveyTbl
    ID (PK)
    SurveyDescrip (type of survey example: auto loan)
    (note: a survey can have more than 1 question associated to it but a particular question can only be associated to 1 survey)

    QuestionTbl
    ID (PK)
    QuestionDescrip (example: Signed loan application in file)
    SurveyID (FK)

    QuesionDetailsTbl
    ApplicationID (PK of loan details linked table)
    QuestionID (PK of QuestionTbl specific question from QuestionTbl)
    Question Description

    ResponseTbl
    ID (PK)
    ResponseDescrip (Pass, fail,n/a)

    ResponseDetailsTbl
    ApplicationID (PK of loan details linked table)
    ResponseID (PK of ResponseTbl)
    QuestionID (PK of QuestionTbl ties back response to a specific question)
    ResponseDescrip

    RiskTbl
    ID (PK)
    RiskDescrip(low, moderate, significant, Serious)



    RiskDetailsTbl
    AppID (PK of loan details linked table)
    RiskID (PK of RiskTbl)
    QuestionID (ties back risk to specific question)
    RiskDescrip


    ResponsiblePartyTbl
    ID (PK)
    Position (processor, underwriter, funder)

    ResponsiblePartyDetailsTbl
    AppID (PK of loan details linked table)
    ResponsiblePartyID (PK of ResponsiblePartyTbl)
    QuestionID (ties back who was reponsible for issue with specific question)
    Position

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use multi-column comboboxes. Users see the description and make their choice based on that and the key is hidden but it is the key that is saved. Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in Access Forms: Control Basics section.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jomby is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    3
    Quote Originally Posted by June7 View Post
    Use multi-column comboboxes. Users see the description and make their choice based on that and the key is hidden but it is the key that is saved. Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in Access Forms: Control Basics section.
    Thanks! I'll check this out tonight

  4. #4
    jomby is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    3
    Okay so I reviewed the tutorial 3 on combo boxes and the tutorial regarding how to use a form combined with an append query to add a record to a table and was able to successfully add a record to one table.

    My new problem is I want to update multiple tables utilizing one form. So I initially figured this would require creating an append query for each table that needed to be updated and somehow make a modification to the "Add record button" to tell it to execute the "Add record" request to multiple tables. I also figured that Access would only try and append the values from the form to the appropriate append table query.


    My new question is how do utilize one form to update fields in different tables with a single add button in the form?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do these tables have parent/child relationships? Perhaps you need form/subform arrangement. Review: http://office.microsoft.com/en-us/ac...010098674.aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-18-2012, 04:00 AM
  2. Building a database from scratch
    By kookiethekat in forum Access
    Replies: 1
    Last Post: 01-20-2011, 06:35 AM
  3. Help w/ DB from scratch.
    By ellixer in forum Database Design
    Replies: 9
    Last Post: 11-30-2010, 10:44 AM
  4. Sorting a questionnaire by gender
    By uchiha37 in forum Access
    Replies: 1
    Last Post: 10-07-2010, 07:37 AM
  5. Access Novice - Looking to learn from scratch.
    By Javaman in forum Database Design
    Replies: 1
    Last Post: 03-13-2010, 08:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums