In Microsoft Access databases, concatenation is the process of joining small separate fields to create a larger data string. These strings are typically generated by query and not stored in the database. It might seem easier to just enter the string as a whole -- such as a full street address -- but there are good reasons for not doing so. Concatenation is a necessary Access skill, and understanding the basics will help make the process easier.
Why Concatenating is Necessary
In a Microsoft Access relational database, each field in a table should ideally hold one unique piece of information about the subject of the table. This allows the user to search and sort by a particular field. It also lets the developer catch empty fields -- for example, if a person fails to enter a zip code -- which reduces errors and makes updating the information easier. Concatenation joins these fields together.
Information That Can and Should Be Concatenated
Any two or more pieces of information can be joined by concatenation. The most common examples include names and addresses. For example, rather than store a full name in one field, it should be broken into separate fields for last name, first name, middle initial and title, and joined by query when needed. Another common example is the combination of various elements of an address -- street, city, state and zip code -- into a full address.
The Terminology of Concatenation
Access requires a small but specific terminology for concatenation to work properly. The main symbol required is the ampersand -- &. This is the link that joins two fields together. Any other information that is to be included in the string -- often spaces and commas -- is surrounded by double quotation marks -- " ". By using these symbols, a string containing virtually any information can be created.
Using a Query to Concatenate Fields
To create a full address string in a query, the user would type this into the Field line of a blank column of her query in design view -- "[street] & "; " & [city] & "; " & [state] & "; and " & [zip]. The actual names obviously should match the fields of her table. Access will assign a name to this new field, but the user can change it. Running the query will give this result: street, city, state and zip.
- Photo Credit Jupiterimages/Photos.com/Getty Images