Calculating totals with a query in Microsoft Access 2019

In the following screenshot, you will see a table named FeeAccountsTbl on the left and a completedtotals query named SumReceived on theright. This representation will help you understand what the totalsquery is about in the explanation that follows: 

ms office 700

The completed totals query named SumReceived is displayed here, showing theresult of using the calculated totals query option, as illustrated inthe following screenshot:

ms office 759

In the table in the preceding screenshot, I have student codes thatare repeated more than once, depending on the numbers of payments thathave been received. If you look at Code number 412, which belongs to CARNEY in the query result, you will noticethat if you add up the three records that are displayed, the totalamount received is £4530.00, asrecorded in the query result. The Surname has been displayed in the queryresult because of the relationship between the FeeAccountsTbl table and the StudentsTbl table used to generate the queryresult.

So, how did I manage to sum the amount received so quicklyusing a query? Let’s run through the steps:

  1. Usingthe SSGSchool.accdb database,create a new query in DesignView.
  2. Add the two tables (StudentsTbl and FeeAccountsTbl) to the query grid.
  1. Add the Code and Surname fields fromthe StudentsTbl table andthe AMT_RECEIVED field fromthe FeeAccountsTbl table to thedesign grid.
  2. From the Show/Hide group, selectthe Totals icon.
  3. On the query grid, check that the Total: rowreads GroupBy for the Surname field and thatthe AMT_RECEIVED field performsthe Sum function,as illustrated in the following screenshot:
ms office 36
  1. Run your query using the Run icon on the ribbon. Yourquery should group the surnames and give the total amount received foreach student code.

Leave a Comment