pivot table - can a calculated field do this?

[fishysheep]

Hi

data as follows:

DATE ITEM RANK SCORE

eg

1 feb aaa 1 3
1 feb bbb 2 0
1 feb ccc 3 3
1 feb ddd 4 1
2 feb aaa 1 9
2 feb bbb 2 1
2 feb ccc 3 1
2 feb ddd 4 0



output should look as:

1 feb aaa 1 -1
2 feb aaa 1 +7

where the figure in the final column is the score of rank #1 - sum(score) for all other rank # on a particular day.

What I'm trying to say is:
sum(Scores) - (topRanked(score))
for each day but I've been working on calculated fields for hours now and I just can't get it.

Thanks

 

[PHV]

Provided your data are in the table named tblScores, here a pure SQL solution:
SELECT S.DATE, S.ITEM, S.RANK, S.SCORE, S.SCORE-Sum(T.SCORE) AS FinalColumn
FROM tblScores AS S INNER JOIN tblScores AS T ON S.DATE = T.DATE
WHERE S.RANK=1 AND T.RANK<>1
GROUP BY S.DATE, S.ITEM, S.RANK, S.SCORE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886