Group within grouping

[ChronicFear]

Hello,

Having a conceptualization problem here. In my database, every entry has a risk code from 1 to 100. For reporting purposes, management would like those grouped into ten catagories. As it stands now, I have three tables - one that holds the entire entry's information including the individual risk code, one that holds a discrete set of individual risk codes and their grouping assignment, and then one that holds the grouping assignment and its description. They are all linked. Example:

Table 1:
Name ID Number Risk Code
Bob 46746 1
Mary 45645 85
Sam 68797 15
Josh 86468 2

Table 2:
Risk Code Group
1 1-9
2 1-9
15 10-19
85 80-89

Table 3:
Group Description
1-9 Lowest Risk
10-19 Low Risk
80-89 High Risk


I'm trying to produce a report with an output that looks like the following:

Group Name Risk Code
1-9 (Lowest Risk)
Bob 1
Josh 2

10-19 (Low Risk)
Sam 15

80-89 (High Risk)
Mary 85


How would I go about getting Access to look at the risk code, then its group, then group by that group on the report?

Thanks!
ChronicFear

 

[Rich]

Add the different groups to the underlying query and then use the Reports sorting and grouping options

 

[ChronicFear]

Hi Rich, thanks for the reply.

I'm intending to use the grouping feature of reports, but I can't figure out how to get it to roll up correctly. As it stands now, I have grouped by the Group that I have, but its producing seperate lines for every entry. So I get

Group Name Risk Code
1-9 (Lowest Risk)
Bob 1

1-9 (Lowest Risk)
Josh 2

instead of

1-9 (Lowest Risk)
Bob 1
Josh 2