highlighting differences between records in a report

[Howard]

I have a series of nested queries processing thousands of student
results from two different assessment times (shown by the set code) that
finally end up a query result similar to that below.

I've made a report grouped by gender within subject showing the %C_plus
and %G_plus for each set based upon the number of students.

What I need now is indicate in some way (numerically, colour coded, a
symbol or something) whether the % for the higher set number - in this
case S02 - is bigger, equal or less than the corresponding value for the
lower set number - in this case S01 - for each gender and subject.

I can't get my head around how I can compare, for example, ICT Females
set S02 with ICT females set S01 in order to do this.

Can anyone help? I'm happy with code if that's what it needs.

subject Set Num Gen C_plus G_plus
ICT S01 21 F 20 21
ICT S01 46 M 27 46
ICT S02 9 F 7 9
ICT S02 14 M 5 14
Art S01 42 F 35 42
Art S01 24 M 14 21
Art S02 12 F 11 12
Art S02 9 M 2 8
Chem S01 34 M 34 34
Chem S01 27 F 27 27
Chem S02 16 F 16 16
Chem S02 13 M 13 13

Thanks
Howard

 

[Howard]

Ah, I've sorted it now.

But just in case anyone else is interested in how to do it I managed to
get a couple of sub queries to do it like that shown below.

This gives me two more fields called Previous_C_plus and Previous_G_plus
that contain the corresponding values from the set that is
alphabetically less than (and in my case chronologically earlier) than
the one in the main query but with the same subject and gender



SELECT grades.subject, Grades.Gen, Grades.Num, Grades.Set,
Grades.C_plus, Grades.G_plus,
(SELECT TOP 1 SubQ.C_plus
FROM Grades AS SubQ
WHERE SubQ.subject = Grades.subject
AND SubQ.gen = Grades.Gen
and SubQ.set < Grades.set
) AS Previous_C_plus,
(SELECT TOP 1 SubQ.G_Plus
FROM Grades AS SubQ
WHERE SubQ.subject = Grades.subject
AND SubQ.gen = Grades.Gen
and SubQ.set < Grades.set
) AS Previous_G_plus
FROM Grades;


Howard