I have a number of tables for recording the development of a set of training lessons, in the following hierarchical order:
Areas table: Areas are broad areas of learning
Subjects table: Subjects break the Areas down into a number of smaller grouping
Modules table: Subjects are then further divided into a number of related modules
Lessons table: Lessons describe the specific lessons that will take less than one hour to complete.
Each table has a reference column to show the preceding tables in the hierarchy. Thus, the lesson table will also show which area, subject and module it belongs to. This all works fine. (Img 1)
I also have a Progressions table which records when each lesson moves a development stage in the following order:
- Assigned to a team member
- Outlined and content is planned
- Started and in progress
- Submitted for peer review
- Work signed off and published
The table records the date, and the progression which comes from a STATUS table as a reference.
When I go to record a lesson changing a stage, I want it to include the Area it belongs to for dashboard purposes.
So I tried to add another reference column but AREA, SUBJECT, MODULE, etc are greyed out. (Img 2)
So I tried to add it But AREA and SUBJECT are not available for selection.
Can someone help me to resolve this, OR, suggest a better way to complete the reporting aspect? That is, show how many progressions have been made per AREA within a date range.