Table Design One to Many

[JD McLeod]

I have run into a report problem and that got me to thinking that maybe my
problem was with my database design. My database has business risks in it.
Each risk can only appear one time. Also included are controls that mitigate
those risks. A control, however, can appear multiple times since one control
can mitigate different risks. I have worked through my form issues and those
are functioning the way I want. I am now trying to create a report and I am
running into difficulties. Here is what I have

RiskTable
RiskID (autonumber)
RiskDescription
Likelihood
Impact

ControlTable
ControlID (autonumber)
ControlDescription
Frequency
Type

RiskControlTable (my third table where I match up the risks with the
applicable controls)
RiskControlID (autonumber)
RiskID (my form has a combo box that looks up the risk ids for the user to
choose from – the database doesn’t allow duplicates, so each risk ID can only
be chosen one time)
ControlID1(combo box on the form that looks back at the controls table)
ControlID2
ControlID3
ControlID4
The form used to link the controls to the risk has combo boxes that lets the
user select one risk and then assign multiple controls to it. Each control
ID is stored in a separate field in the RiskControlTable (i.e. ControlID1, 2,
3, etc.) There is no limit to how many times a control can be used. It
could be used on only one risk or on 10 different risks.

On my report I am struggling with how to get the report to show the risk one
time and then show all of the applicable controls with their other criteria
such as frequency, type, etc. I have a feeling that I may have a problem
with my database design as it relates to the riskcontrol table. Any help
would be appreciated. Thanks.

 

[Fred]

Your post was a little confusing.....you sort of "messed up / displaced" your
important structure discussion with later stage stuff like combo boxes and
forms. Especially where you derailed your all-important description of your
junction table (RiskControlTable) by talking about a table having combo
boxes. You'll want to think and discuss structure seperate from all of that
other later stuff.

So I don't understand your structure fully, but here something based on my
guess of what it is: You have a many to many relationship. Per the above
I'm not sure what the structure of your junction table is, but it looks like
it has too many fields and one set to autonumber that shouldn't be. While it
might end up getting a 3rd or 4th field, of those discussed fields I think
it shouild have only two fields

RiskControlID Integer, NOT autonumber, NOT PK
RiskID Integer, NOT autonumber, NOT PK

Of course back up before trying any changes.

Hope that helps a little.

 

[JD McLeod]

Thanks Fred, that helped and once i made the changes, i got the report to
work using a subreport. see my other post under "table design or report
design". I have encountered another problem separate from this open. thanks.