Looping through values in a table

[draaYak]

Hi,

I have a class of 300 students, and in a table (Student Marks), it lists their name and a mark out of 100 for a test I gave them recently.

I have another table (Mark Frequency), which lists possible marks out of 100 (a table with values 0 to 100), with another attribute left blank ready to calculate how many students got that mark.

What I am trying to do is when I press a button, to get the blank field to calculate how many students got more than that mark.

I have tried macros, but I can't get anything to work.

I have read about For...Each Next loops and was wondering whether that might be able to work.

Any help would be much appreciated.
Thanks,
Lex

 

[draaYak]

Copy of Database

Hi,

Here is a cut down version of the database.

Thank you for your time and effort, it is much appreciated!

Regards,
Lex

 

[KenHigg]

Giz, I have not looked at the examples, but could you not just group and count the scores in a totals query?

kh

 

[Jon K]

I have another table (Mark Frequency), which lists possible marks out of 100 (a table with values 0 to 100), with another attribute left blank ready to calculate how many students got that mark.

What I am trying to do is when I press a button, to get the blank field to calculate how many students got more than that mark.

If I read your questions correctly, I think you can calculate them with a series of two queries. You can run the second query in the database to see if the query results are what you wanted.
.

 

[Jon K]

...... I think I may need an explanation!

The first query is just a totals query that groups and counts the marks, and would return 86 records.

The second query links the first query to table tblFreq with an outer join to display all 101 records in the table:-
SELECT [tblFreq].[Mark],
nz([qryOne].[CountOfMark])+0 AS [Students got that mark],
Nz((Select Sum(CountOfmark) from qryOne as S where S.[mark]>=[tblFreq].[Mark]))+0 AS [Cumulative Total]
FROM qryOne RIGHT JOIN tblFreq ON [qryOne].[Mark]=[tblFreq].[Mark]
ORDER BY [tblFreq].[Mark] DESC;


The Nz(...)+0 converts any Null values to numeric zeros. (Some people would use Nz(..., 0), but that would change the resulting field to Text.)

The [Cumulative Total] is calculated from the first query using a (Select Sum......) subquery based on the criteria [mark]>=[tblFreq].[Mark]. The "as S" is just an alias and is used here to make the code more readable. Here the Nz(...)+0 is used in case no one gets the mark of 100.

Hope this helps.
.