Long time lurker....first time poster!

Overview

I'm building a database application that (amongst other things) stores data on job roles and their responsibilities. The core fields are:

JobRoleID - ID of the job role (from a table defining job roles)
ResponsibilityID - ID of the organisational processes that the job role holder is responsible for (from a table defining the process hierarchy)
ResponsibilityLevelID - Categorisation of different types of responsibility

Each job role may be responsible for many organisational processes, and each organisational process may involve many job roles (each with varying Responsibility Levels). So far, so straightforward.

The challenge lies in the fact that we may be dealing with many hundreds of organisational processes and a couple of hundred roles. The processes are hierarchical in structure (e.g. 1 > 1.1, 1.2, 1.3 > 1.1.1, 1.1.2, etc... and to an extent so are the job roles (insofar as organisations have hierarchies).

The volume of permutations make selecting a job role, a process and a level incredibly time consuming on a line-by-line basis i.e. drilling down through cascading combo boxes is just too slow.

Problem definition



I am trying to come up with a speedy way to enter the data that probably looks something like a datasheet view i.e. (all/sub-set) processes along one axis, (all/sub-set) job roles against the other, levels as the cell data. I obviously don't want to run into any normalisation errors, so simply setting job roles or processes as fields isn't going to cut it.

I was wondering whether it would be possible to create an editable query/form that displays [every process] x [every job role], that could be filtered down to a more manageable level during data entry. Didn't get as far as I would have liked with this...

Of course there may be a better way...?

Summary

I am hoping that being embedded in this problem for what seems like weeks has left me blind to the obvious (and humbling) solution. I am open to all and any ideas!

(Also: I am not sure that I have defined the problem clearly enough to make sense to anyone other than me. Please let me know if you would like more information/context)

Steven