different groups within one table?

[Happy YN]

I have been running a database which records pupils test results in many subjects and averages the results according to their class etc. However I now have a situation wher within ny class the pupils are streamed into 'a' 'b' and sometimes even 'c' groups. The trouble is a pupil in b group for maths could be in c group for geography.Does that mean I have to add a field name for every subject into the pupils table so I can query it and if so how can I get user to control it i.e. to add or remove a subject etc
thanks!
Afterthought!
- or should I create a different table with the subjects there and join pupils with subject which would also mean the user manually linking each subject with each pupil?

 

[Happy YN]

Thanks pat for speedy response
I hope you will help me structure my tables esp. as you wrote in the intro to your forthcoming book that table sructure is vital!
I have pupils,classes,subjects and tests. Every pupil takes regular testsand has them recorded in a gigantic table using pupil id, test id and his mark. every pupil is in one class. every test belongs to one subject. This way I can query the giant table to extract info on one pupil or entire class in order to produce reports etc.
every subject is also linked to another table staff so I can extract info on amember of staff
Now I am faced with a situation where a pupil can be in maths class a and english class b and geography class c so I need the user to be able to get this info into a table somehow that I can link to this pupil. I must let this information be updateable by the user so 1) how do I build such a table and relationships 2)how do I let the user get to this info 3) how do I now redesign my queries?
If I need to explain more I will but I wanted to keep it brief
Thanks immensly in anticipation
Happy YN

 

[Happy YN]

thanks pat I understand most of what you are saying . I have the user select class,testname end by clicking cmdAdd that adds a buch of new records to the main table i.e. one for each member of the class. it also opens a subform which has all the members of that class on it so the user can add a mark next to pupils name.
I want the user to have such a button which will only add the students who belong to a particular group e.g. maths a or b etc. and display them but my main problem is how will the app know who is in which group How can i get the user to tell it that info and be able to change it themselves as necessary its the table relationship that i am concerned about.
also since I am building this app for many schools I am reluctant to make something which is specific to one school , I want all users to be able to use one app and just make invisible the appropriatr parts if you understamnd me!
thanks again