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.
[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.
|Figure 3: Concatenated Names appearing in|