An Expression to Obtain a Full Name from Three Separate Fields

There is a very good reason why we separate name fields when we create an Access Table.  If we store the Title, First Name and Surname in a single field, we limit the capability of our application to interrogate this part of our data.  For example, if we store a full name in a single field, we would not be able to sort a list of names into alphabetical order (because Access cannot differentiate between a title, first name and surname). Moreover, we would also not be able to extract just the title and surname in order to address a letter. This is because the individuals first name comes in between, thereby preventing us from using the name in a mail merge operation.

As such, there is a general database design convention of storing all the elements of a name in separate fields. This gives us maximum flexibility and control when we come to process this data.  What's more, we still have the capability of joining (concatenating) the full name back together again through use of an expression in a query's calculated field, for example.  So how is this done exactly?

Imagine we have a list of names stored in a table.  We have separate fields for Title, FirstName, and Surname.

Figure 1:  An Access Table containing a list of names
stored in separate fields.

The expression we are going to use to concatenate the name into a single whole is as follows:

        [Title] &" " & [FirstName] & " " & [Surname]


The three fields are separated by two ampersands (&), and a string containing a single space in between.  The ampersand concatenates the various elements of the name, and the empty space between the quotation marks simply creates a space between the three fields when joined together.  So we have a total of five separate elements concatenated by the ampersand operator - ie Title & space & FirstName & space & Surname.

As mentioned above, this expression can be used in a calculated field of an Access Query.  To do so, just enter an alias (ie the name we are going to call the calculated field) with a colon in front of the expression.  For example:


        FullName: [Title] & " " & [FirstName] & " " & [Surname]


This is entered into the FIELD row of the query design grid as follows:

Figure 2: Expression to concatenate a full name
entered into the Query Design Grid.

Then, when we run the query we get a list of full names, each one appearing as a single field:

Figure 3: Concatenated Names appearing in
Query Result.