Joining data in Access queries

When your Access query results need to look neat and tidy, you'll often want to join related pieces of data together that actually appear in separate fields of the file. For example if you have a list of first names and last names in separate fields (as you should), you can still view them in a single column in your query results.

To do this, join the data in the query data sheet, for example this will join the data from the FirstName and LastName fields and display it as a proper name in the query results:

FirstName & " " & LastName

Creating Access Lookups

You're already used to using Lookup lists in many of the programs you use. You use a lookup or dropdown list to select from a series of entries that the programmer has provided for you to choose from. When you create a database in Access, you get to be the programmer and you can create your own lists.

So, whenever you have a list of options that can be used to fill a field in an Access database you can create a lookup field to manage it. Then, whenever data is entered into the table all the user has to do is to select an option from a dropdown list - this makes the dataentry process much faster and more accuate too. Here's how to create one:

1. Start by creating a table that contains the details you want to allow your user to select from. You will need two fields in the table – one for a unique ID and another for the data. For example, for a list of states, include an ID field and a list of the states. Save your table.


2. Open the main data table that you want to access the states information from. In the field for the State details, from the data type list choose Lookup Wizard. When the dialog opens choose 'I want the lookup column to look up the values in a table or query' and click Next. Select the table containing the data – in this case it would be the States table you create in step 1.


3. Click Next and, from the Available fields list choose the field containing the names of the states as you want them to appear in the dropdown list and move it to the Selected Fields list. Click Next. In the sort area choose the field containing the data and set it to be sorted Ascending.


4. Click Next and adjust the column width to the desired amount. Click Next, type a field name and click Finish and save the results. When you next enter data into the table you will find that the field with the lookup has a dropdown list that displays the data so you can choose an item from it.