Totals on alias columns

Question

I have a table in MS Access that has the following fields/data:

School number  Traditional Forms  Sparring         Traditional Weapons  Combat Weapons
-------------  -----------------  ---------------  -------------------  ---------------
2598                               
3286           2nd                Competed         Did Not Compete      2nd
1642           Competed           Did Not Compete  3rd                  Did Not Compete
1642           3rd                3rd              2nd                  Competed
3116           1st                1st              1st                  1st
2598           Competed           2nd              Did Not Compete      3rd
2396           Did Not Compete    Competed         Did Not Compete      Competed
3286           Competed           Competed         Competed             3rd
3116           1st                1st              1st                  1st
3169           Did Not Compete    Did Not Compete  Competed             Competed

I have a query that will count individual entries by school as follows:

SELECT [tourney report].[School Number], COUNT([School Number]) as Entries
FROM [tourney report]
WHERE (Sparring IN ('1st','2nd','3rd','Competed') OR [Traditional Forms] IN ('1st','2nd','3rd','Competed') )
GROUP BY [School Number]

This gives me the total number of entries in either Sparring or Forms per school. I can repeat this for each of the other categories (weapons/combat weapons). What I would like to get is a combination query that shows total entries per school, which is a total of each of the forms/sparring (Forms and sparring count as one entity), weapons, combat totals. Example:

School number   Forms/Sparring  Weapons Combat  Total Entries
1234                22            15      20       57

My further attempts resulted in putting the following query together, which gives me most of what I need:

SELECT [tourney report].[School Number], 
       SUM( IIF( ([tourney report].[Traditional Forms] = "Did Not Compete" OR LEN([tourney report].[Traditional Forms]) = 0) AND ([tourney report].[Sparring] = "Did Not Compete" OR LEN([tourney report].[Sparring]) = 0) , 0, 1) ) AS [Total Form and Sparring],
       SUM( IIF( ([tourney report].[Traditional Weapons] = "Did Not Compete" OR LEN([tourney report].[Traditional Weapons]) = 0), 0, 1) ) AS [Total Weapons],
       SUM( IIF( ([tourney report].[Combat Weapons] = "Did Not Compete" OR LEN([tourney report].[Combat Weapons]) = 0), 0, 1) ) AS [Total Combat]
FROM [tourney report]
GROUP BY [School Number]

This gives me the totals for each of the categories that I want, but not the total. I tried the following:

SUM( [Total Form and Sparring] + [Total Weapons] + [Total Combat]) as [All Entries]

That, however, gives the error

Subqueries cannot be used in the expression (SUM( [Total Form and Sparring] + [Total Weapons] + [Total Combat]) as [All Entries])

How can I get the total of all the sum columns as a fourth total?

Answer 1

I am not sure if Access supports this but generally SQL allows you to solve this with the help of a derived table. If you use your present query as a derived table, you can reference all the computed columns exposed by it simply by their aliases, like this:

SELECT
  [School number],
  [Total Form and Sparring],
  [Total Weapons],
  [Total Combat],
  [Total Form and Sparring] + [Total Weapons] + [Total Combat] AS [Total Entries]
FROM
  (  /* this is the query that gives you most of what you need */
    SELECT
      [tourney report].[School Number], 
      SUM( IIF( [tourney report].[Traditional Forms]   IN ('', 'Did Not Compete')
            AND [tourney report].[Sparring]            IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Form and Sparring],
      SUM( IIF( [tourney report].[Traditional Weapons] IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Weapons],
      SUM( IIF( [tourney report].[Combat Weapons]      IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Combat]
    FROM
      [tourney report]
    GROUP BY
      [School Number]
  ) AS derived

You can see that the aggregate results are both returned as their own columns and used to create another computed column, the total of all entries.

As you can also see, with the above query I have also taken the liberty of slightly rewriting your conditionals. I just wanted to make them more compact. But there, again, I am not entirely sure if Access will accept that syntax. If not, you can always revert the syntax to yours.