Forum Discussion
Post PatronMany-to-Many Relationship - LOST
I feel like I've read at least half the world's literature on M2M relationships, but alas I am making no headway on a solution.
There is a CLASS of 10 Students (TABLE: "Students", COLUMNS "Index", "StudentName").
The students take a test with 10 questions. (TABLE: "Test", COLUMNS: "Index", "Questions").
The test data are recorded in a table, "Data", with "Students" on the rows and "Test Results" for each of 10 questions in columns 2-11. (MANY Students and MANY Questions.)
I want to be able to filter by Students and by Questions.
Do I need a bridge table?
You need to unpivot your data table:
- goto query editor
- select student column in your data table
- right click and select "unpivot other columns"
- you will see 3 columns, student , attribute, value
- rename attribute to question
apply changes
- in relationship , set relationship between student and data table
that's it, you are good to go
2 Replies
- parry2k
Super User
You need to unpivot your data table:
- goto query editor
- select student column in your data table
- right click and select "unpivot other columns"
- you will see 3 columns, student , attribute, value
- rename attribute to question
apply changes
- in relationship , set relationship between student and data table
that's it, you are good to go
MojoGeneParry2k:
Thanks very much! Worked like a charm!