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

swise001

New Member
Joined
May 14, 2014
Messages
2
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


Student ID
Student Name
Grade
Course Title
Period
Teacher
123456
John T Student
9
Biology
1
Mr A
123456
John T Student
9
History
2
Mr T

<tbody>
</tbody>



Table 2: I have student assessment data organized vertically:

Student ID
Benchmark
Score
Test
Subject
123456
L.14.1
33%
Baseline
Biology
123456
L.14.3
50%
Baseline
Biology
123456
L.14.5
75%
Baseline
Biology

<tbody>
</tbody>


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.
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top