Need ideas to address handling a Many to Many relationship using PowerPivot in Education

[swise001]

I am currently learning PowerPivot in hopes it can help our school manage student assessment and scheduling data.

I have 2 main Tables uploaded to Power Pivot.

In Table 1 I have the entire school schedule organized with the following design


[TABLE="width: 500"]
<tbody>[TR]
[TD]Student ID
[/TD]
[TD]Student Name
[/TD]
[TD]Grade
[/TD]
[TD]Course Title
[/TD]
[TD]Period
[/TD]
[TD]Teacher
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]John T Student
[/TD]
[TD]9
[/TD]
[TD]Biology
[/TD]
[TD]1
[/TD]
[TD]Mr A
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]John T Student
[/TD]
[TD]9
[/TD]
[TD]History
[/TD]
[TD]2
[/TD]
[TD]Mr T
[/TD]
[/TR]
</tbody>[/TABLE]



Table 2: I have student assessment data organized vertically:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Student ID
[/TD]
[TD]Benchmark
[/TD]
[TD]Score
[/TD]
[TD]Test
[/TD]
[TD]Subject
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]L.14.1
[/TD]
[TD]33%
[/TD]
[TD]Baseline
[/TD]
[TD]Biology
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]L.14.3
[/TD]
[TD]50%
[/TD]
[TD]Baseline
[/TD]
[TD]Biology
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]L.14.5
[/TD]
[TD]75%
[/TD]
[TD]Baseline
[/TD]
[TD]Biology
[/TD]
[/TR]
</tbody>[/TABLE]


What I am conceptually having a hard time with is how to connect Table 1 with Table 2.

There are a few facts to consider:
Each student is enrolled in 8 classes with 8 potentially different teachers
Each student will have assessment scores from multiple tests in multiple subjects.

I would like to ultimately create a dashboard where teachers will be able to select their name and period number, and an alpha list of the students will appear, along with the corresponding assessment data that they wish to see.


The only way I've been able to connect these two tables so far... (which is not elegant)... is by using a Macro to create 8 duplicates of every row of student assessment data and then add a column that contains a unique key (such as student ID+period#) that could be related to the same combination made on the schedule sheet.

I would happily entertain better ways to connect this information, and avoid having to duplicate data.

I look forward to your help and suggestions.

 

[dhExcel]

You need to add a 3rd table with the Student ID and the Student Name.

 

[swise001]

It's not clear to me how that would help with this problem. I have such a table, but it does not prevent me from having to follow the steps I mention in my first post. Can you elaborate more on how you feel that table would help me connect a student's assessment scores with their respective schedule without having to create 7 additional duplicate copies of each.