Building a Crosstab query in Microsoft Access 2019

A Crosstab query allows the user to summarize data by arranginginformation into a more readable structure, such as a spreadsheet.Crosstab queries can group information horizontally and vertically,whereas a normal Select query groups information vertically only. Youwould specify which fields would be displayed in the column format andwhich would be displayed in the row format.

The following screenshot shows an example of a normal Select query inAccess:

ms office 828

Here is an example of a Crosstabquery (using the same table and data converted to a Crosstab query):

ms office 703

Let’s go through the steps together, as follows:

  1. Using the database from the previous example, click to create a newquery in Design View.
  2. When prompted by the Show Tabledialog box, select the Query tab, andthen double-click on the PupilMarksquery to add it to the query grid. Click Close to move to the query grid.
  3. Click the Crosstab icon, whichappears in the Query Type group.
  4. Notice that the grid updates to display the Total: row as well as the Crosstab: row.
  5. Add the Subject, Surname, and Mark fields from the PupilMarks query to the query grid.
  1. In the Subject column, in linewith the Crosstab: row, select Row Heading.
  2. In the Surname column, in linewith the Crosstab: row, select Column Heading.
  3. From the Mark column, in line withthe Crosstab: row, select Value. Change the Total: row to reflect Sum for the Mark column, as illustrated in the followingscreenshot:
ms office 360
  1. Click the Run icon on the Results group to see the output of the Crosstab query.
  2. Click the Save icon to save thequery. Enter Crosstab as the queryname into the dialog box provided.
  3. The Crosstab query will appearunder the Queries group on theNavigation Pane.

Leave a Comment