Create an Access Report with Names in Columns by Address
Question details
Create an Access report that displays the names associated with each address in multiple columns.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A report needs to list each address once and show its occupants in a column layout.
- Observed behavior
- Names should fill columns across the report and wrap to additional rows when an address has more occupants than available columns.
Identify the table and fields that contain the address and occupant name. If an address consists of multiple fields, decide how to identify each address uniquely before creating the queries.
Use a linked, multi-column subreport for occupant names
Use a parent report for distinct addresses and a linked subreport that wraps names across columns.
In Access, select Create > Query Design, switch to SQL View, and enter SELECT DISTINCT [Address] FROM [YourTable] WHERE [Address] Is Not Null;. Replace the table and field names with your own, then save the query.
Select the saved address query in the Navigation Pane, then choose Create > Report. Open the report in Design View and keep the address control in the Detail section.
Create a query that includes both the address and occupant name fields, then select it and choose Create > Report. Open the new report in Design View, place the name control in its Detail section, and save it as a subreport.
With the subreport open, choose Page Setup > Columns. Set the number and width of columns, select Across, Then Down, and save the subreport.
Open the parent report in Design View and add the saved subreport to its Detail section using the Subform/Subreport control. In the subreport control's Property Sheet, set Link Master Fields and Link Child Fields to the corresponding address fields.
Choose Print Preview and check addresses with different numbers of occupants. Adjust the subreport column widths and parent report layout if names are clipped or do not fit.
Work with Office documents and spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface for working with documents and spreadsheets. Open compatible Office files to migrate everyday work easily; Microsoft Access is still needed to build and run the database report described above.

Frequently Asked Questions
How do I show each address only once in the parent report?
Base the parent report on a query using SELECT DISTINCT for the address field. Create it through Create > Query Design and switch to SQL View to enter the query.
How do names wrap to another row in the subreport?
Open the subreport and choose Page Setup > Columns. Set the desired number of columns and select Across, Then Down so additional names continue on subsequent rows.
Why does the subreport show names from every address?
In the parent report's Design View, select the subreport control and check Link Master Fields and Link Child Fields in its Property Sheet. Both should refer to matching address fields or an appropriate address key.
Can WPS Office create the Access report?
WPS Office can work with exported spreadsheet data, but it does not create or run Microsoft Access database reports. Use Access to build the linked parent report and subreport.




