FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Combining Fields In The Query Design Window

In most cases, good database design requires you to avoid having fields that contain full names. Usually you will have a FirstName and LastName field. On the other hand, you will often want to have the full name available to FrontPage to work with.

You may also want it formatted in one of several ways. You may wish it to be "FirstName LastName" or "LastName, FirstName". Both of these can be easily accomplished in the Query Design Window.

Open the Northwind database on your local computer. Click on the Queries tab and then select New. You should see the following dialog box:

Select Design View and click OK. The Query Design Window will open up. Click the Employees table and select Add. You will see the table added to the Query Design window. Click Close to close the Show Table dialog.

In the Query Design Window, select the EmployeeID, LastName and FirstName fields and drag them onto the Query Design grid. Your Query Design grid should resemble the following:

If you click the preview button on the tool bar, you will see a list of employees with their EmployeeID, last name and first names. It will look something like the following:

Now, lets add a field that will combine the first and last names separated by a space. To do this, place your cursor in the Field line of one of the blank fields in the Query Design Window. In Access, you can create new fields from existing fields by giving the field a name, followed by a full colon (:) and then providing details on how the form should be constructed. In this case you'll type the following:

FullName: [FirstName] & " " & [LastName]

In Access, you separate fields using the ampersand (&) symbol. To add a space, you place two double quotes separated by a space. Essentially you tell it to display FirstName AND a space AND LastName.

Your Query Design window will look like the following:

If you click the preview button, you will see a new field called FullName with the names separated by a space. It will look like the following:

Now, let's take it one step further. What if you want to create a sortable name in the format LastName, Firstname. The process is exactly the same. In this case though, you will type the following into a blank Query Field:

IndexName: [LastName] & ", " & [FirstName].

Ok, let's get really fancy. What if some of your records only contain a last name. Using the above technique would display those records as:

LastName,

If they only contained a first name, they would display as:

, FirstName

We can alter the coding so that it varies its display based upon what data is in the table. Essentially, what we want is the following:

If LastName is blank, display FirstName only
If FirstName is blank, display LastName only
Otherwise display LastName, Firstname

We accomplish this using the iif statement. We have created an entire tutorial that focuses on this powerful tool. Click here to read the tutorial

In this case we use the following syntax:

IndexName: iif([LastName] = "",[FirstName],[LastName] & ", " & [FirstName])

Now this takes care of the first and last option. Essentially, it says if the LastName is blank, display FirstName, otherwise display LastName, FirstName.

To add an option to cover the situation where FirstName is blank we need to nest a second iff statement. It will look like the following:

IndexName: iif([LastName] = "",iif([FirstName] = "",[FirstName],[LastName] & ", " & [FirstName]))

You can get around this by making both the FirstName and LastName fields required fields when entering data. However, if that is not practical, the useful iif statement is always there to rescue you.