How to Match Two Lists Together Using Microsoft Access

For this example, we shall use Microsoft Access. Run Access and create a new database (in response to the opening dialog or at File > New > Database), giving it a suitable name.

Access works with data organized into tables, and we need to import the two lists to be matched, each into a new table. File > Get External Data > Import will present a range of options for importing external files: it is necessary to specify in each case whether the list is an Excel file (saved as a worksheet in Excel), Text file (created with a text editor such as Notepad or Wordpad), etc. Access will then examine the file to be imported and ask some questions about it, showing how it believes the data to be organized. In the examples given here, our first list is separated by commas, so we need to tell Access that this is a delimited list, and then to confirm that the comma is the character that separates the different items.

We will then be asked to name the data items, here “Respondent”, “Gender” and “Postcode”. Let Access add its own Primary Key and choose a name for the new table such as “Postcodedsurvey”. Access will confirm that the data has been imported from the external file and a new table will appear in the window representing the database. Double-clicking on this entry will open a tabular view of the data from the first list.

We now need to repeat the data import task for the second list, repeating the stages. In this example, we do not have any characters showing where one data item ends and the next one begins, as these are not usually provided in this type of directory file. Access will suggest correctly that the data are in fixed width fields and will show how it believes the data should be broken up. In this example, Access will suggest a split following the space in the postcodes, which is incorrect. The arrowhead showing the split between the fields should be dragged to its correct position after the postcode and before the ward code. Double-clicking will allow additional field divisions to be inserted or removed.

Proceed to name the data items as before, in this case "Postcode" and "Wardcode" and the table, "Postcodewardlist". If more than two lists of data are to be matched, the file import process can be repeated as many times as necessary.

Using Query for Matching Lists in Microsoft Access

We are now ready to match the two lists.

This is undertaken by running a new Query from Insert > Query. Select "Design View" and you will be presented with two new windows. The first of these "Show Table" will list the available tables, and the tables to be matched should each be added to the query by selecting them and clicking on "Add". This dialog may then be closed. A second window now displays the items in each of the selected tables, linked by a line between the ID fields (that Access has mysteriously added!) We do not wish to match our tables by the ID numbers but by the Postcodes, so this link should be deleted by right clicking on it and choosing "Delete". Dragging a new connection between "Postcode" in the "Postcodedsurvey" table and "Postcode" in the "Postcodewardlist" table will create a new link.

The last stage is to drag the items that you would like to see in your matched list into the grid. In this example, "Respondent", "Gender", "Postcode" from "Postcodedsurvey" and "Wardcode" from "Postcodewardlist" are each dragged and dropped into the "Field" row of the grid. This defines the Query that will match the lists together, and the grid should now be closed - Access will prompt for you to give it a name such as "Postcodematch" and it will be added to the database window. Double-clicking on the query will run it, producing a grid displaying the matched lists.

This can be exported using File > Export to an external file for further analysis, for example in Excel spreadsheet format. Note that selecting the query and choosing "Design" opens the design view again and allows it to be modified, for example if it has not worked as intended or you wish to re-order the fields in the result. Note also that in this example only two of our survey results can be matched because there is no postcode in the directory corresponding to the postcode of RespondentC.