Help with reporting/table relationships

I am designing the tables for a risk management database. The current
build is like so:

RISK TABLE
Risk ID*
Risk Description
Risk Profile
Risk Ratings (etc)

CONSEQUENCE TABLE
Risk ID*
Consequence*

CONTROL TABLE
Risk ID*
Control*

In order to make each risk unique, I've assigned an autonumber primary
key to the Risk Table.

Each risk has multiple consequences and multiple controls.

The Risk ID field in the Risk Table has a one to many relationship
with the Risk ID field in the Consequence table.

The Risk ID field in the Risk Table has a one to many relationship
with the Risk ID field in the Control table.

The primary key for the Consequence Table is a multi-field key,
consisting of Risk ID and Consequence.

The primary key for the Control Table is a multi-field key, consisting
of Risk ID and Control.

My problem is that I want to do a report that goes like this:

Risk 1 Consequence 1 Control 1
Consequence 2 Control 2
Control 3

However, when do do a standard report with the report wizard,
consisting of:

Risk Table.Risk Description
Consquence Table.Consequence
Control Table.Control

.... that is grouped by Risk Description then by Consequence and then
by Control...

.... I get data reptition, where the risk control data for that risk
repeats each time the risk consquence for that risk is stated. Sort of
like:

Risk 1 Consequence 1 Control 1
Control 2
Control 3

Consequence 2 Control 1
Control 2
Control 3



I think I need to stick in another relationship between Consequence
and Control (perhaps a junction table) to ensure the report comes out
correctly, but I'm not sure what to try next. Any ideas?

Re: Help with reporting/table relationships

On Jan 14, 11:42=A0pm, lotusv...@gmail.com wrote:
> I am designing the tables for a risk management database. =A0The current
> build is like so:
>
> RISK TABLE
> Risk ID*
> Risk Description
> Risk Profile
> Risk Ratings (etc)
>
> CONSEQUENCE TABLE
> Risk ID*
> Consequence*
>
> CONTROL TABLE
> Risk ID*
> Control*
>
> In order to make each risk unique, I've assigned an autonumber primary
> key to the Risk Table.
>
> Each risk has multiple consequences and multiple controls.
>
> The Risk ID field in the Risk Table has a one to many relationship
> with the Risk ID field in =A0the Consequence table.
>
> The Risk ID field in the Risk Table has a one to many relationship
> with the Risk ID field in =A0the Control table.
>
> The primary key for the Consequence Table is a multi-field key,
> consisting of Risk ID and Consequence.
>
> The primary key for the Control Table is a multi-field key, consisting
> of Risk ID and Control.
>
> My problem is that I want to do a report that goes like this:
>
> Risk 1 =A0Consequence 1 =A0 Control 1
> =A0 =A0 =A0 =A0 =A0 =A0Consequence 2 =A0 Control 2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Co=
ntrol 3
>
> However, when do do a standard report with the report wizard,
> consisting of:
>
> Risk Table.Risk Description
> Consquence Table.Consequence
> Control Table.Control
>
> ... that is grouped by Risk Description then by Consequence and then
> by Control...
>
> ... I get data reptition, where the risk control data for that risk
> repeats each time the risk consquence for that risk is stated. Sort of
> like:
>
> Risk 1 =A0Consequence 1 =A0 =A0 =A0Control 1
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 Control 2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 Control 3
>
> =A0 =A0 =A0 =A0 Consequence 2 =A0 =A0 =A0 =A0 Control 1
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 Control 2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 Control 3
>
> I think I need to stick in another relationship between Consequence
> and Control =A0(perhaps a junction table) to ensure the report comes out
> correctly, but I'm not sure what to try next. =A0Any ideas?

Subreports