Show All Names in a Microsoft Access Crosstab Query
Question details
Show every person from a names source alongside the results of a Microsoft Access crosstab query, including people without matching data.

- 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.
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.
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.
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.
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.
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.
Double-click Full Name in the names source and the required time fields in the crosstab to add them to the query grid.
Select Run on the Query Design ribbon and verify that the names without matches appear with blank crosstab values. Save the query.

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.

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.




