Can One Field In A Table Be Used To Limit The Result Of Another Field In The Same Rec

Hi,

I have a fairly simple database that looks like the attached. It is the beginnings of a school database. Unfortunately, the MS template doesn't quite do it.

We have around 700 pupils in 5 year groups. Each year's intake is assigned the year of that intake (e.g. 2002, 2003 etc).

The youngest pupils are in Year 7 whilst the oldest are in year 11.

Each Class is assigned based on the intake year but given the name of the schooling year - (e.g. year 7, year 8 etc). And there are 6 classes per year - hence names 7.1, 7.2, 7.3 etc.

My "Students and Classes" table should combine both the classes and pupils. However, I want to be able to firstly select the class to put the pupil into (and hence infer the induction year), then just show in the next filed only
the pupils that belong to that induction year and hence go into the selected class.

However I design my lookup or query, I can't seem to figure out how to do this, or if this can even be done.

Any help is much appreciated.

Chris