Skip to main content
1 of 2
Andriy M
  • 23.2k
  • 6
  • 59
  • 103

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 it does not, you can always revert it to yours.

Andriy M
  • 23.2k
  • 6
  • 59
  • 103