Advanced Databases – Concatenate fields in a report.

Concatenate fields means to merge them together.

There is no option to do this automatically in Base, so we first have to create a query in design mode.

Add the Car and owner details table. We want to merge First Name and Surname fields.

In the Field column type: “First Name” || ‘ ‘ || “Surname” to merge the fields. Give it the Alias ‘Full Name’

If you prefer, you can enter the command in SQL view.

Press F5 to run the query.

Notice that the First Name and Surname fields have been merged into a single field called Full Name.

Save the query with the title: ‘Query Names’ and exit the query design window.

Create a report in wizard mode.

Add Query Names and select the field ‘Full Name’ to appear in the report. (Note: if we want to display more fields we can simply add them to the query, then add to the report).

Keep clicking Next until you get to Choose layout and select a column layout.

Click Finish to run the report.

The report will display the concatenated (merged) fields as one.

This can be repeated for other fields if necessary by using the ||’ ‘|| command in a query, then running the report.