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