Sum & Previous in a query

[racefan91]

In my database I have a table for tracking points for teams. I want to then create a query to provide a total which adds all points for the team together. Based on the sum I want to rank the teams. Should I put the rank in the query? If so, is there an easy way to automatically number them?

Second question would be is there a way to show a total not counting the most current tournament or the previous rank to break ties. Attached is my current table and query.

[June7]

A GROUP BY query with aggregate calcs sorted by point total should be fairly straightforward. You appear to have accomplished that.

Assigning a rank value is not so easy. Review http://allenbrowne.com/ranking.html

[Dal Jeanis]

Assuming that your Tournament number field always increases, and that it is not possible for two different teams to get the same number of points in a tournament (like, both teams didn't show up?) then you could use something like this:

tblFirst
Team     text     The Team Name
TourNo   Number   The Tournament Number
TourPts  Number   The Tournament Points Earned

Query1:  
SELECT
   Q1.Team,
   SUM(Q1.TourPts) AS CurrPts,
   SUM(Q1.PreVPts) AS PrevPts
FROM 
   (SELECT
       T1.Team,
       T1.TourPts,
       IIF(T1.TourNo < (SELECT Max(T2.TourNo) 
                        FROM tblFirst AS T2
                        WHERE T1.Team = T2.Team),
           T1.TourPts,
           0)  AS PrevPts
    FROM 
       tblFirst AS T1) AS Q1
GROUP BY Q1.Team; 
 
Query2:
SELECT 
   Q1A.Team,
   Q1A.CurrPts, 
   Count(Q1B.Team) AS RANK
FROM 
   Query1 AS Q1A, 
   Query1 AS Q1B
WHERE
  ( (Q1A.CurrPts + (Q1A.PrevPts/1000)) <= (Q1B.CurrPts + (Q1B.PrevPts/1000)))
GROUP BY 
   Q1A.Team, Q1A.CurrPts;

I cheated slightly to simplify the last query. Adding 1/1000th of the previous points should accomplish the tie-breaker without having to separately test for the equal condition.

[racefan91]

In Query1 I am getting an error for Undefined function 'IFF' in expression

[racefan91]

Never mind, figured out that the IFF needed to be IIF

[Dal Jeanis]

Sorry, that' a holdover from my calculus days. I type IFF about half the time.

[racefan91]

Assuming that your Tournament number field always increases, and that it is not possible for two different teams to get the same number of points in a tournament (like, both teams didn't show up?) then you could use something like this:

tblFirst
Team     text     The Team Name
TourNo   Number   The Tournament Number
TourPts  Number   The Tournament Points Earned

Query1:  
SELECT
   Q1.Team,
   SUM(Q1.TourPts) AS CurrPts,
   SUM(Q1.PreVPts) AS PrevPts
FROM 
   (SELECT
       T1.Team,
       T1.TourPts,
       IIF(T1.TourNo < (SELECT Max(T2.TourNo) 
                        FROM tblFirst AS T2
                        WHERE T1.Team = T2.Team),
           T1.TourPts,
           0)  AS PrevPts
    FROM 
       tblFirst AS T1) AS Q1
GROUP BY Q1.Team; 
 
Query2:
SELECT 
   Q1A.Team,
   Q1A.CurrPts, 
   Count(Q1B.Team) AS RANK
FROM 
   Query1 AS Q1A, 
   Query1 AS Q1B
WHERE
  ( (Q1A.CurrPts + (Q1A.PrevPts/1000)) <= (Q1B.CurrPts + (Q1B.PrevPts/1000)));

I cheated slightly to simplify the last query. Adding 1/1000th of the previous points should accomplish the tie-breaker without having to separately test for the equal condition.

I managed to get the first query to work but the second query doesn't seem to want to work. When I try to run the query I get the following error:
You tried to execute a query that does not include the specified expression 'Team' as part of an aggegate function

[Dal Jeanis]

Sorry - Add to the end

GROUP BY 
   Q1A.Team, Q1A.CurrPts;

That's the problem with aircode - it takes a few shots to compile...

[racefan91]

Yeah, that's understandable. Thanks a bunch.
Is there a way to push this information into a table or something so I can query out who's been ranked by number the most times or similar stuff with the ranking?

[June7]

Can try UPDATE or MAKE TABLE sql actions.