Logic of database and data model

[train2]

I'm a little lost regarding the data definition of my database. I've started looking into ways of creating dynamic reports, so that they are flexible for users, and find that I can't figure it out.

Story so far:
I have pupils that belong to classes (so a pupil table, and class table, and a classpupil table). Each pupil gets a target grade for each class, plus a series of review grades (1-5 etc.). So I created a progress table made up of Class, Pupil, ReviewNo, Grade.

When I want to compare a pupils target with a review grade I set up 2 queries - 1 to query their target, (ie. where reviewno = 0) and a second which uses the results of the first to bring in the review grade where reviewno=1).

So I get what i'm looking for:
Pupil ID, PupilName, Class, Target Grade, Review1

But of course, that costs me 1 query per review grade - which seems silly. Is there a way of combining these 2 queries into 1? I'm not so hot on SQL.


What I really want is to allow users to automatically select which review grades they see in a report, eg.

Pupil ID, PupilName, Class, Target Grade, Review1, Review2 etc.

The only way I can see doing this, is setting up a series of queries for each review, but that seems a very poor solution. I'm thinking I've missed something basic.
Can anyone help? Even a quick pointer in the right direction would be welcome.
cheers,
train2

 

[train2]

I'm embarrassed at my stupidity - i just remembered i can add the same table twice in one query - and if I've got the fields in a query, I can figure out how to allow the user to select from combo boxes on a form.

...

i'm still learning!