Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Complex query

Status
Not open for further replies.

GazzaN

Instructor
Mar 29, 2005
18
GB
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?
 
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
 
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)

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top