Merge Access UNION Query Results into One Row per Project
Question details
Combine audit results from an Access UNION query into separate columns with one row per project.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A UNION query returns multiple audit records for projects, and the results need to be displayed by project.
- Observed behavior
- Projects with multiple audits appear in multiple rows rather than in one row with a column for each audit.
Save the UNION query and confirm that its output includes ProjectID, an audit identifier, and the issue-result field. Check whether a project can have multiple results for the same audit, as the crosstab must aggregate those values.
Create a crosstab query from the UNION query
Use the saved UNION query as the source to display each audit in its own column.
In Access, select Create > Query Design. Add the saved UNION query from the Queries list.
On the Query Design tab, select Crosstab to show the Total and Crosstab rows in the design grid.
Add ProjectID to the grid and set its Crosstab option to Row Heading. Add other project fields as row headings only if their values are consistent for each project.
Add the audit identifier field and set its Crosstab option to Column Heading. Each distinct audit identifier becomes a column.
Add the issue-result field, set its Crosstab option to Value, and select an appropriate aggregate in the Total row, such as Max for text values.
Select Run and review the results. Each project should appear on one row, with its audit results under the corresponding audit columns.

Review exported audit results in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and compatible file formats for a smooth move between office suites. Export Access query results to a spreadsheet to review them in WPS Spreadsheets; WPS does not run or repair the Access crosstab query.

Frequently Asked Questions
Why does my crosstab still show more than one row for a project?
Check every field marked Row Heading in Query Design. If another row-heading field has different values for the same ProjectID, Access creates separate rows for those combinations.
What should I use as the crosstab column heading?
Use the field that identifies the audit, such as an audit name or ID. Set its Crosstab option to Column Heading in the query design grid.
How does the crosstab handle multiple results for one project and audit?
Access combines them using the aggregate selected in the Total row for the Value field. Choose an aggregate that matches how you want to resolve repeated results.
Can I get a list of projects with an issue instead of separate audit columns?
Yes. Create a select query using the saved UNION query, filter the issue field for Y, and return distinct ProjectID values. This produces a project list rather than a column for each audit.




