Concatenating Fields in an Access Query

For those of you that have wrestled with concatenating fields and the use of the & and + symbols, this post will demonstrate methods of building a SQL query to combine multiple fields.

We will use one table for this example as shown below.

Let's start with a simple SQL query to combine the first and last names as PersonName.

SELECT tblPerson.PersonID, [FName] & [LName] AS PersonName FROM tblPerson;

Here we have selected the tblPerson primary key and the combined first and last name.  Below is the resulting record set.

While we have successfully combined the first and last name, there is no separation between them.  We will add a space between them by altering our SQL as follows:

SELECT tblPerson.PersonID, [FName] & " " & [LName] AS PersonName
FROM tblPerson;

This will return the person name as "John Doe", but what if your project needs to return the person name as Last, First MI?

To accomplish this we could write the query below.

SELECT tblPerson.PersonID, [LName] & ", " & [FName] & " " & [MInitial] AS PersonName
FROM tblPerson;

The drawback to this method is that even if no middle initial exists for a record, an unnecessary space is placed after the first name.  This may not seem all that important in this example, but we will explore this issue further momentarily.

A better method would be as follows (assuming the first and last names are required fields):

SELECT tblPerson.PersonID, [LName] & ", " & [FName] & " " + [MInitial] AS PersonName
FROM tblPerson
ORDER BY tblPerson.LName;

By changing the & to a +, the space after the first name becomes dependent on there being a middle initial present for the record. It may have caught your eye that an additional line has been added to the above query; this is to order the records by last name.

For those readers that are more comfortable with building access queries in "Design View", the below image is provided as reference.

Now let's look at another example of how the + can be used in concatenated fields. Below we have added a PhoneNumbers concatenated field to our query.

SELECT tblPerson.PersonID, [LName] & ", " & [FName] & " "+[MInitial] AS PersonName, "Home: " & [PhoneHome] & "/" & "Cell: " & [PhoneCell] & "/" & "Business: " & [PhoneBusiness] AS PhoneNumbers FROM tblPerson ORDER BY tblPerson.LName;

Here is the result:

You may notice that Susie's record returned unnecessary "/" separators and descriptions for phone numbers she doesn't have. We will improve the query by building in some dependencies.

SELECT tblPerson.PersonID, [LName] & ", " & [FName] & " "+[MInitial] AS PersonName, "Home: " + [PhoneHome] & "/" + "Cell: " + [PhoneCell] & "/" + "Business: " + [PhoneBusiness] AS PhoneNumbers FROM tblPerson ORDER BY tblPerson.LName;

Below is the final result.

Visit AccessInsider for other Microsoft Access tutorials and examples.

Posted in: Access,Concatenate,Databases,Microsoft Access,MS Access,Queries,SQL