I am doing a project for a Science fair that has 3 tables: Judges, Students, Scores.
Judges and students both have their own unique ID, Judge ID and Project ID, respectively. These IDs are have a relationship to the scores table by the same ID's (Judge and Project ID).
Each score has field that says if it is a "finalist score" or not (boolean 1 or 0 value) and I want to use this value to determine what projects the Judge gave a score to based on if they were in the finals or not.
In the judges table, I want to display both of these lists on the layout page for the Judges table. I am having a problem with getting these values from the scores table because I can only get the list of all the projects they scored, but not dependent on the status of the "finalist score" field. The two fields I would like to be populated are Projects Scored are: "Non_Finalist Projects Scored" and "Finalist Projects Scored"
I spent a lot of time googling this and the only thing that I came across was the conditional value list, which seems like what I want to do but I couldn't figure out how to implement it for my use case.
Attached is the relationship graph of the three tables that I am working with.
Any thoughts or suggestions are appreciated. Thank you!