Assigning Positions to Students per subject 1

[cantech2]

I have a class database from which I have created a report. Now, assume Jack, Jill and Bill are in a class and they all take Chemistry. Jack has 70% with grade A, Jill has 45% with grade D and Bill has 65% with grade B. Each of them has their reports on a separate page because I asked for a page break after each students details. The scores and grading work fine.

My question is, how do I make a field called POSITION to show that Jack was 1st, Jill was 3rd and Bill was 2nd so that I can easily print each students report.

 

[dhookom]

If you don't mind assigning unique numbers when there might be duplicate scores you can:
-Sort the report by Pct descending
-add a text box
Control Source: =1
Running Sum: OVer all

If you need a query solution, use a subquery like
SELECT *, (Select Count(*) from tblA A WHERE A.Pct >=tblA.Pct) as Rank
FROM tblA;

Duane
MS Access MVP

 

[cantech2]

Dear Dhookom,

thanks for your response but I am not clear on your solution. Please see additional information:

1. My table is SS1
2. My the score field on the report is English
3. The position field on the report is Position
4. If 2 people have the same score, they should have the same position.

Thanks,
Cantech2

 

[PaulBricker]

I'm not clear where Duane what's you to do this. I would assume the easiest way would be to Group your Report on the Pct field. To do that, you on the Menu bar go to View...Sorting and Grouping. In the Field/Expression column select the Pct Field. Then in the Group Properties...Group Header select Yes. Add your Pct field to that Group Header. Then assign the Running sum textbox Over All the way Duane suggested. That should do what you need.

Paul

 

[dhookom]

A running sum in the group header will increment only once per Pct value. If two people are tied in the number 3 spot, the next person would be number 4 rather than 5.

I would not store the Position value since it can be calculated in a query.

I think the report's record source should use a subquery like my second suggestion.
SELECT *, (Select Count(*) from SS1 A WHERE A.Score >=SS1.Score) as Position
FROM SS1;

Duane
MS Access MVP

 

[cantech2]

Dear Duane,

thanks so far. When I try to execute, I get the following error message:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field"

Still waiting for help,

Humphrey. (Cantech2)

 

[dhookom]

Paste your current SQL for us to view.

Duane
MS Access MVP

 

[cantech2]

Dear Duane, thanks for your assistance so far. I have a tricky headache.

Now, the ranking works on the whole table, which is fine. But if I put a criteria to give me the result for the boys only, it still gives me their positions in the whole class whereas I want to see the new position of a particular student by changing criteria such as state, sex etc.

That is Ken may be 6th in Class, but I have a criteria like State, he may be 1st in his state, but I keep seeing 6th.

Any way around this ?

Thanks,
Humphrey

 

[dhookom]

You would need to add to the subquery
SELECT *, (Select Count(*) from SS1 A WHERE A.Score >=SS1.Score AND A.State = SS1.State) as Position
FROM SS1;

Duane
MS Access MVP