Updating Data with a query

[mshakeelattari]

When I make a query in ms access in sql view using this code:

SELECT *
FROM StudentsExams;

Data of the table may be updated by its datasheet view or a form based on it. However when I use a query:

SELECT (SELECT Count(*)
	 FROM StudentsExams As X
	 WHERE X.classid = StudentsExams.classid
and x.PaperID = StudentsExams.PaperID
		And X.Result > StudentsExams.Result)+1 AS Ranks, *
FROM StudentsExams;

Data can not be updated.
Please what is the reason behind it and any alternate for it please?
Moreover when I create a report based on the 2nd query, multilevel grouping fails. Any help will be appreciated.
Another issue is: This query results in ranks as:

StudentID   ClassID     PaperID    Result    Rank
1          1         1        10        1
2          1         1        12        2
3          1         1        12        2
4          1         1        13        4

And the required ranking is as:

StudentID   ClassID     PaperID    Result    Rank
1          1         1        10        1
2          1         1        12        2
3          1         1        12        2
4          1         1        13        3

I shall be very thankfull for any solution.

[jforbes]

Lots of questions here...

Check out http://allenbrowne.com/ser-61.html for why your query isn't updatable.
An alternative would be to generate the Count in a Function and call it from your Form.

For Ranking, Rabbit has you covered: http://bytes.com/topic/access/insigh...-ms-access-sql

Using a function would probably fix your Reporting Grouping or it might start working after reading the stuff from Rabbit.

[mshakeelattari]

Ranking results the same condition from the code giben by Rabbit.

Please guide about the funciton for reporting grouping

[Rabbit]

You already asked the ranking question in another thread and I responded in there.