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?

You have added an additional 1-to-many relationship to your existing structure. To handle this properly, you will need to add a fourth table to the structure and change the tables that the relation table "relates". In your current structure the junction table relates Student to Class. It now has to relate Student to ClassGroup.

tbl1-Student
StudentId (primary key)
etc.

tbl2-Class
ClassId (primary key)
etc.

tbl3-ClassGroup
ClassId (primary Key field1)
ClassGroup (primary key field2)

tbl4-StudentClassGroup
StudentId (primary key field1)
ClassId (primary key field2)
ClassGroup (primary key field3)

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

I already posted the key structure for the tables that I knew about. Your Tests table which now probably has a relationship with the Class table would have to be changed so that the relationship will be with the ClassGroup table instead.

Conversion will be tedious but not impossible since once the users create ClassGroup and StudentClassGroup tables, you can use them to figure out which Group needs to be assigned as you convert the Test table. Don't forget to make backups before you do the conversion. If I understand what's going on, the Tests table is the only one that will actually need to be converted by queries that you will build.

Rather than modifying your app to allow the users to modify the ClassGroup and StudentClassGroupTables through it, I would export spreadsheets. Let them change the spreadsheets and you use an update query to get the Group code from the spreadsheet into your table. You'll have to hold off specifying the primary key on these tables until the Group is populated since it will be part of the primary key and therefore Access will not allow it to be null.

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:)

Look again at the tables I posted earlier. You need to modify your current table structure to be similar to what I suggested. Once you have done that, the USERS have to populate the changed tables. You have no way of knowing which student belongs to which group or even how many groups a particular class has. Once the users set up the groups and students properly, you can use an update query to populate the necessary columns in the test db. Of course you'll need to also change your queries and forms to accomodate this change.