Complex query

[GazzaN]

I have a table with a whole set of records relating to progress to date. The data itself is the same (Levels 1 to 10) for each field and I want a query that calculates the total Level 2s for each field (Maths, Science, English) into a percentage of the whole. I can create a COUNT query for each field but I want a single query that does all three. Any ideas?

 

[PHV]

Any chance you could post the table's schema, some input samples, expected result and your actual query (with its result) explaining us what is wrong for you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GazzaN]

Okay,

Example records in the original table would be:

Intake Year: 2001
KS2 English: 4
KS2 Maths: 5
KS2 Science: 4

This data would be in every record for students, up to 250 for each year of intake. I have three queries, each showing the result for each subject for each year of intake. An example would be for English:

Intake Year KS2 English KS2 English Totals
2001 0 48
2001 2 6
2001 3 50
2001 4 120
2001 5 47
2002 0 48
2002 2 3
2002 3 66
2002 4 104
2002 5 47
2003 0 32
2003 2 7
2003 3 63
2003 4 108
2003 5 38
2004 266

So we can see that in 2003 108 students achieved Level 4 for English. However, I want this as a Parameter query so that the user can input the intake year and gain results for all courses and Key Stages. Therefore I would want the following:

Intake Eng Tot Math Tot Sci Tot
2001 48 44 32
2001 6 3 1
2001 50 69 29
2001 120 119 150
2001 47 36 59

With each line being a level (in this case 0, 2, 3, 4, 5 but should be 0,1,2,3,4,5,6,7,8,9,10)