Access 2002 Queries Combine Fields with Perfect Spacing
Search MS Office A-Z | Search Web Pages/ Design A-Z
Access 2002 Queries Combine Fields with Perfect Spacing
When you create tables in Microsoft Access, you usually want to use a separate field for each piece of information. For example, let’s say you want to enter the names of employees in a table that has their personal information. Your chief accountant is Bob Jones. You use a FirstName field to type Boband a LastName field to type Jones. You can also use a Salutation field to enter Mr., as well.
This approach does give you maximum flexibility for creating queries and reports with the information you need. But there’s a drawback: It makes it more difficult to combine values, such as Mr., Bob, and Jones, when they need to be treated as a single entity, that is, Mr. Bob Jones.
For example, suppose you want to create a report with employee biographies. You want to display their full names, such as Ms. Helen Trump. On the hard copy generated by the report, the spacing between Ms. and Helen, and Helen and Trump, must be equal. It must also be equal for all other employee names.
But with separate controls for each field, and with employee names of different lengths, that’s a tall order. You could try placing the text boxes for the Salutation, First Name, and Last Name fields adjacent to one another. But at least for some employees, that will give you too much space between the parts of the names. To correct for the excess space, you could try to move the controls so they overlap somewhat, but now you have the opposite problem: For some staff, their first and last names could partly cover each other.
Access has a much easier and much better solution. You can create an expression combining all three fields that will give you the full names and perfect spacing, as well. This technique is called concatenation. It’s not as difficult as it sounds, but the process is a bit tedious. Let’s try an example to get the hang of it.
Create
A Query
We’ll create an expression that concatenates fields to display employee names from the Northwind database. Open the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Assuming you have made a copy (or have a backup) of the Northwind database, open the file. In the Database Window, click the Tables button, and double-click the Employees table to open it. Review the First Name, Last Name, and TitleOfCourtesy fields and close it.
We’ll create the expression in a query. In the Database Window, Employees should still be selected. Click the Insert menu and then Query, which will create a query using data from the Employees table. With Design View selected, click OK. Maximize the Query Design window.
Create
An Expression
Now we’ll create the expression you need to concatenate the fields. Click the first row of the first column. Type Staff:[FirstName]&[LastName]. Here’s the purpose of each part you entered:
•Staff: This gives the column a name; it doesn’t affect the data displayed.
•[FirstName] When you type the FirstName field enclosed in brackets, you include its data in your expression. Note that there is no space between First and Name. In the table, the caption First Name is used for easy readability, but the field is FirstName.
•& This mark, knownas an ampersand, is the operator for concatenation. It joins together what appears before and after it.
•[LastName] This entry includes the data from the LastName field.
Click View to see the data the expression generates. First and last names are both displayed, but there’s no space between them. Let’s add it.
Click View. Press SHIFT-F2to open the Zoom window. Now you have plenty of room to work with. Click after the ampersand. You can add a space, a comma, or other punctuation by enclosing it in quotation marks. Type ", press the Spacebar once, and type " again. Your expression is now Staff: [FirstName] &" " [LastName].
Are you finished? Not quite. Look at this expression from Access’s point of view: "Okay, I’ll retrieve data from the first name field, and then I’ll leave a space, but now what am I supposed to do with the LastName field?"
Because there’s no ampersandafter the second quotation mark, Access doesn’t know how the LastName field is connected to the rest of the expression. Each time you add another element to the expression, you also need to surround it with ampersands to include that element.
Click after the second quotation mark and type &. Click OK and click View. You now see the employee names as they should appear: first and last names with a space between them. Click View.
Add
The Salutation
On your own, try adding the salutation to the expression so that your records will be displayed as Ms. Nancy Davolio, Mr. Andrew Fuller, and so on. The salutation data is in the TitleOfCourtesy field.
Need some help? Here are the steps you need to complete the exercise:
1.With the cursor in the first row of the first column, press SHIFT-F2
2.Click in between the colon and the initial bracket of [FirstName]. Type [TitleOfCourtesy] to add the salutation data
3.Type &
4.Type ", press the Spacebar once, and type "
5.Type &
6.Click OK
Click View to see your records. If you want to see the staff names in full, move your pointer to the right border of the Staff cell. When the pointer becomes a double-arrow-with-bar, double-click to resize the column.
You can click File and Save and save the query as Smart Computing Concatenation (or any other title you wish) or click the Close Window button and then click No to discard it.
Access 2002 Queries Combine Fields with Perfect Spacing