Others

How to Create a Crosstab Query with Two Fields in Microsoft Access

Camila Milosovich Sep 30, 2026 868 views

Question details

The user wants to create a crosstab query to summarize the relationship between two fields, using one field as rows and the other as columns.

How to Create a Crosstab Query with Two Fields in Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Summarizing database table relationships using row and column intersections.
Observed behavior: The user needs a clear method to structure a crosstab query using the Crosstab Query Wizard, Design view, or SQL view.

Before you start

Ensure you have your database opened in Microsoft Access and identify which field you want to serve as your row headings and which will serve as your column headings.

Solution 1Recommended

Use SQL View to Write a Crosstab Query

The most direct way to create a crosstab query with specific fields is by writing the SQL statement using TRANSFORM and PIVOT.

Writing a crosstab query directly in SQL View gives you precise control over your data transformations. The essential components are the TRANSFORM statement for the calculated values, and the PIVOT statement for the column headers.

Open Query Design

Open Microsoft Access, navigate to the Create tab, and click Query Design.

Switch to SQL View

Close the Show Table dialog box without adding any tables, then click SQL View in the top-left corner under the Home or Design tab.

Enter the Query Syntax

Enter the TRANSFORM query syntax. For example: TRANSFORM Count(*) AS ItemCount SELECT EmployeeID FROM EmployeeEmployers GROUP BY EmployeeID PIVOT EmployerID;

Customize and Run

Replace EmployeeID, EmployeeEmployers, and EmployerID with your actual field and table names, then click the Run button to view the summarized results.

Aggregate Functions: You can change the aggregate function from Count(*) to Sum(), Avg(), or another mathematical function depending on the data you want to summarize.

Solution 2

Use the Crosstab Query Wizard

A guided, user-friendly method for creating crosstab queries without manually writing SQL.

Free Microsoft Office alternative

Analyze Data with Pivot Tables in WPS Office

While Microsoft Access uses crosstab queries for databases, WPS Spreadsheets offers powerful Pivot Tables to summarize and analyze complex data easily without writing SQL. WPS Office is a free, lightweight, and comprehensive suite that provides seamless compatibility with Microsoft Office formats.

1. Open Your Data: Launch WPS Spreadsheets and open the dataset you want to summarize.
2. Insert PivotTable: Navigate to the Insert tab and select PivotTable to open the creation dialog.
3. Structure the Data: Drag your desired fields into the Rows, Columns, and Values areas to instantly create a dynamic crosstab-style summary.

Free and lightweight Office suiteFully compatible with Microsoft Excel (.xlsx) formatsIntuitive Pivot Table tools for easy data summarization equivalent to crosstab queriesFamiliar user interface with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

What is the purpose of the TRANSFORM statement in an Access SQL query?

The TRANSFORM statement is specifically used in Access SQL to create crosstab queries. It specifies the aggregate function (like Count or Sum) used to calculate the values displayed in the intersection of the rows and columns.

Can I use more than one field for row headings in a crosstab query?

Yes, you can specify up to three fields for row headings in a crosstab query. However, you can only select one field for the column headings and one field for the calculated intersection values.

Why am I getting an error when trying to run my SQL crosstab query?

Errors typically occur if the field names or table names are misspelled, if the GROUP BY clause doesn't match the SELECT clause, or if the PIVOT field contains incompatible data types. Ensure your database fields match the query syntax exactly.

How do I view the design of a crosstab query created by the Wizard?

After creating the query using the Crosstab Query Wizard, right-click the query name in the Navigation Pane and select Design View. This allows you to see exactly how the row headings, column headings, and value calculations are structured.

Office Quick Solutions