AnsweredAssumed Answered

Many-to-Many Reporting - HELP!

Question asked by Sealander on May 18, 2012
Latest reply on May 23, 2012 by philmodjunk


Many-to-Many Reporting - HELP!


Thanks to anyone reading this. I have a many-to-many relationship that I have no idea how to report on. I'll use a simpler example than what I'm really doing that has the essence of my problem. 


I have users evaluate songs. Each evaluation is a record in an Evaluation Table, with an auto-serialized EvaluationID primary key. In the entry layout, I use two portals. The first portal allows users to categorize the song (e.g. Country), with possibly multiple categories (e.g., Country, Western) with each category its own record in a Categories Table related to the Evaluation Table by the EvaluationID. The second portal allows users to give feedback on the song (e.g. Too Twangy, Awful Lyrics), with also possibly many feedbacks with each its own record in a Feedbacks Table related to the Evaluation Table by the EvaluationID field. 

What I need to do is calculate things like "For each Category, such-and-such feedback was given x% of the time". For example, "For 'Country' songs, 'too twangy' was given 70% of the time and 'awful lyrics' was given 100% of the time" and maybe make a bar chart or something. 


What I was trying to do is report on the "Feedbacks" table, but if I try to include the category field from the "Categories" table, it only picks the first category associated with the EvaluationID its related by (and there are possibly many). Any ideas? I think if could somehow create a new table that somehow joins the Categories Table and the Feedbacks Table I could just report on that. Is that something I can do as a table occurence? If that's what I should be doing, I have no idea how. THANK YOU!