Table Set Up and Relationships

P

Pam

I am performing a risk assessment. I currently have two tables - one for
participants and one for the risks to be assessed. I need each participant
to assess each risk and some how need to be able to accumulate the results by
participant. How do I do this?
 
A

Arvin Meyer [MVP]

You need 2 more tables. The first would be a list of appropriate risk
levels. Uou don't want 1 user to call it "High" risk, another to rate it 10
on a scale of 10, another 5 on a scale of 5, and yet another rating it 1 on
a scale of 5 (with 1 being the highest level). So you must establish a set
of standards. The second table will be a join table which will have the
ParticipantID, the RiskID, and the RiskLevel which will bring together the
primary keys of each of the 3 other tables. Further fields, like a timestamp
can enhance your data collection procedure by allowing time sensitive
comparisons, and multiple assesments from a single participant.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
L

Larry Linson

Most likely, you need a third table... Risk Assessment by Participants.

ParticipantID (foreign key to Participant table)
RiskID (foregin key to Risk table)
information fields about the assessment

If you create a Form or Report with the Participant table (or a query on
that table returning only the pertinent Fields). Create another Form or
Report on the RiskAssessmentByParticipant table (or query as above). Embed
the second form or report in a Subform or Subreport control in the first
Form, and set the Participant ID of the Record Source of each form as the
LinkMasterFields and LinkChildFields of the Subform or Subreport control.

How you would assure that there is a record in the
RiskAssessmentByParticipant table for each Risk for the Participant
currently assessing them would depend on how you intend the database to be
used. A little VBA code to run a simple AppendQuery on first use by a
participant might be all you need.

Larry Linson
Microsoft Access MVP
 
Top