More Access 2003 help

[tpreece01]

Here's an Access 2003 table (for ease of typing, I've used letters in place of full titles on this example).

[student] [date]
[a1] [a2] [a3] [a4] [a5]
[b1] [b2] etc, which continues through to e1-5

a=english
b=maths
c=geography...

Each student will be given a number of marks each month, from 1 (excellent) to 5 (poor)

How can I run queries to:
1) show the number of grade 1's for an individual student over the month (I'd repeat this for 2's, 3's etc)
2) calculate an average grade for each student in each subject for the month

So:

student1 in may for English
0 @ grade 1
2 @ grade 2
1 @ grade 3....

student1 in may for geography

1 @ grade 1
1 @ grade 2
1 @ grade 3....

Totals: 1 grade 1; 3 grade 2; 2 grade 3
Average (english): (0x1)+(2x2)+(1x3)=7 / 3 (items graded).

[vascop]

You may not realise, but you have asked for a lot here.
For a start your design is not good and will lead to difficulties.
Here is a better design showing 3 tables and linking.
http://docs.google.com/Doc?id=dfzbst2k_10xf588 kfk
Please enforce referential integrity when linking the tables.
Input data into student table first, then into grades table and at the same time link to the student by clicking on the little plus sign on the left of each record.
This will automatically put data into the other table.

Have a go at this and then I will put some queries up in another document.
I am going abroad on holiday for a week this Saturday, so I will not be able to help you much more till I come back.
I may be able to put a few example queries for you to look at before I go.
If you are wanting to make this into a fancy system with input forms and proper Access reports there is quite a lot of work involved.
If you just want rough and ready on-screen stuff then it's quite a lot less work.

[vascop]

Here's a query for what you want in 1) in your post:
http://docs.google.com/Doc?id=dfzbst2k_14dnmxn 4c5

[vascop]

Sorry, I had to modify my initial stuff, so first link is now:
http://docs.google.com/Doc?id=dfzbst2k_20jgbtt df9
and second link is:
http://docs.google.com/Doc?id=dfzbst2k_24djcsb 2f9

[vascop]

Here are a couple of queries which approximate to what you want in 2) in your post. You can probably work out for yourself how they work and then modify them if necessary:
http://docs.google.com/Doc?id=dfzbst2k_26cr6nn 9gd

[vascop]

In query 6b the fourth column top line is Month([Date]). I forgot to widen the column before I captured the image.