Others

Show All Names in a Microsoft Access Crosstab Query

Kushani NimanthikaKushani Nimanthika Nov 22, 2026 868 views

Question details

Show every person from a names source alongside the results of a Microsoft Access crosstab query, including people without matching data.

Show All Names in a Microsoft Access Crosstab Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A crosstab summarizes data by person and time, but the results should also include people who have no matching records.
Observed behavior
Some names are missing from the crosstab results when they have no matching data.
Before you start

Identify the table or query that contains the complete list of people and the saved crosstab query that contains the time fields. If names are not unique, use a unique person ID as the join field instead of Full Name.

Solution 1Recommended

Left-join the names source to the crosstab query

Use this approach to include every person in the names source, even when the crosstab has no matching row.

1
Create a select query

In Access, select Create > Query Design. Add the table or query containing all names and the saved crosstab query, then close the Add Tables pane.

2
Join the matching fields

In Query Design, drag Full Name from the names source to the matching Full Name field in the crosstab. If both sources contain a unique person ID, join on that field instead.

3
Include every name

Double-click the join line, then choose the Join Properties option that includes all records from the names source and only matching records from the crosstab. Select OK.

4
Add the output fields

Double-click Full Name in the names source and the required time fields in the crosstab to add them to the query grid.

5
Run and save the query

Select Run on the Query Design ribbon and verify that the names without matches appear with blank crosstab values. Save the query.

Left-join the names source to the crosstab query
Missing time columns: The left join adds missing people, but it does not create time columns absent from the crosstab. Set fixed Column Headings in the crosstab query's Property Sheet if you need specific columns to appear.
Free Microsoft Office alternative

Review exported crosstab data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with exported Office files. Open an exported crosstab in WPS Spreadsheets to review names and time columns; the Access query and its join must still be created in Access.

Review exported crosstab results in a spreadsheetCompatible with Microsoft Excel XLSX filesFamiliar interface makes switching between Office files straightforwardFree and lightweight for everyday document work
microsoft office alternative - wps office

Frequently Asked Questions

Why does my crosstab omit some names?

A crosstab based on transaction or booking records cannot show a person who has no matching source record. Join the crosstab to a complete names table or query and include all records from the names source.

Can I join on a person ID instead of Full Name?

Yes. In Query Design, join the names source and crosstab on a unique person ID when both sources contain it. This avoids matching different people who share the same name.

Why is a time column still missing after the left join?

The join adds names, not crosstab columns. Open the crosstab in Design View and set its Column Headings property to the required values so those columns are available even when their data is missing.

How can I show zero instead of a blank crosstab value?

In the outer query's design grid, use a calculated field such as Nz([TimeField],0), replacing TimeField with the relevant crosstab field name. Use zero only when it is appropriate for the data.