Adding a calculated column to a query

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have query that lists the total points for players. I
have a column named "Name" and I use a "Group By" for that
column and in the Points Column I do a "SUM" which gives
me a column called SUM of Total Points ( I use the
descending option to list the player with the highest
points first) after the query is run. The result is a
query that list the player with the highest points. I
would like to add a cloumn called PointsBack to that query
that would give me the difference from the highest player
for all players.

I would like it to look like this

Name SumofPoints PointsBack
Mike 375
Bob 365 10
Dave 350 25
Joe 340 35

Thank You for your help.


Regards

Chris
 
This could be rather slow, but it works...

Save your existing query as, eg, Q1.

Create a new query...

Select
q1.[name],
q1.[SumofPoints],
dmax("[SumofPoints]","Q1")-q1.[SumofPoints] as PointsBack
from Q1
Order by q1.[SumofPoints]

If you want to suppress the zero that will be returned for
the first row, put the third item inside an IIf statement
 
Back
Top