Filtering records for a group, when records might be in multiple groups

[datanick]

I have a list of pupil assessment scores for a year group. I want to be able to filter the year group list to select the pupils who belong to certain group of pupils, but pupils may belong to more than one group.

This is how the report looks and how I want to select the groups:

How the report loooks

I have a master table of pupils, the group names, and a table with the IDs of the pupils in each group:

The data in table of scores looks like this, but I need help to know how to link this list of scores to get it to filter for the pupils who maybe in a certain group. I could add a column for the list ID but that would only allow for a pupil to be in one list, when a pupil might be in multiple lists as defined in the tbl_listgroups.

Help appreciated with how to set up the data to do this!

Thanks