It's a mouthful to pronounce but CONCATENATION in Access is a really useful feature when it comes to combining data. This article describes how to use concatenation to create a custom lookup for use in a Combo control by using a query to combine fields from an existing staff table. The Combo lookup to be used in another table is to show complete staff names in the pop down list.

If you want to carry out calculations or operations on data stored in an Access table you can only do this in a query, form or report rather than in the actual table. We'll describe using concatenation in a query in this article. Suppose you have a staff table containing the four fields Ref, Title, Firstname and Surname. Ref is an autonumber field and is the primary key. You want to create a Combo Lookup to use in another table which shows each member of staff's title, firstname and surname all in the lookup list and adds the appropriate staff Ref number into the table.

You want the Combo lookup to show the full names to ensure the correct member of staff is selected by the Combo. This can be achieved by using a query based on the first table to create a custom field using concatenation. The custom field can then be used as the Combo lookup list in the second table.

To create the custom lookup, first create a query based on the staff table and add the four fields Ref, Title, Firstname and Surname to the query builder. Then create a new fourth field in the query with the label FULLNAME followed by a colon and then use concatenation to combine data from the other three fields. Concatenation uses the ampersand symbol & to combine text fields together in a new field. So the expression entered for the new fourth field is FULLNAME:Title & Firstname & Surname and then click into the next cell down to let Access automatically add square brackets round the field names. You don't need to type spaces before and after the ampersand symbol as Access will also do this for you.

However, running this query will produce a list which contains data such as 1 MrsMarySmith, 2 MrBillGreen and 3 MrsJaneJones. To add spaces between the parts we return to the query design view and amend the expression to include spaces. This is done using additional ampersands and " ". Note: There's one spacebar press between each quote mark. So the amended fourth field becomes FULLNAME:[Title] & " " & [Firstname] &" " & [Surname] and then click one cell down to let Access tidy up the spacing.

Then run the query and this time you should see each record showing as Mrs Mary Smith, Mr Bill Green, Mrs Jane Jones and so on. We're almost there. Switch back to query design view, and untick the show checkboxes for all the fields except Ref and Fullname. the custom one, as we only want to see the Fullname field. Run the query again and this time you'll see just the Ref and the custom field, which is perfect for our Combo lookup. Save the query with an appropriate name such as QryFullnames.

Next we're going to use this query in a Combo control to allow us to select members of staff from the lookup list and add to another table, for example to record which member of staff attended a particular training course.

So in this other table, in design view, we create a Staff field and set the type to numerical, to match the Ref field type. Then with this new field selected, look in the properties in the lower part of screen and select the Lookup tab. Then choose the display control Combo. Leave Row Source type as Table/Query, and set the Row Source to your new query, QryFullnames, then save the table to finish. Now in regular table view test the new combo and ensure the pop down shows the data from our query QryFullnames. We see the names in the lookup list but we actually add the staff Ref number into the new table. And that completes the building of the Combo lookup using concatenation.

What to find out more about using queries and lookups in Access? A really effective way is to attend a training course as this can be really effective in boosting your skills in a short time.