Example: Linking and Aggregating Postcoded Data to a Higher Geography using Microsoft Access

The postcode is one of the most commonly-used geographical references in both primary and secondary data sources. It is quite common for users to have to match postcoded data to a higher level of geography e.g. to a set of current health authorities or areas within a census geography. This operation can be performed using Microsoft Access, where it is a specific instance of the more general task of matching two lists together. Academic users can also perform this task using the GeoConvert tool, which is ideal for small to medium sized datasets post-2006.

This example will demonstrate how a set of fictitious postcoded records can be aggregated to the output area (OA) level via the a customised postcode to output area (OA) look up. The zip archive containing the two source data files can be downloaded by clicking [here].

Save the file to a local folder. After unzipping the content, you should find two files (ignore the other unzipped files which are needed for other related Geo-Refer examples): " _bypcode.csv" and "lookup_oa_pcode_xy.csv". The first file contains the postcoded records whereas the second one is an example of a postcode to OA code lookup table for the same area. An extensive postcode lookup table covering the whole UK known as the National Statistics Postcode Directory (NSPD). It is available for purchase from the Office for National Statistics (ONS). The directory is available freely to academic users through the ESRC Census Programme's UKBORDERS service.

Linking Postcoded Records with Output Area Codes using Microsoft Access

Start Microsoft Access. Select File > New and click the "Blank database..." icon on the New File pane. Name the blank database as " _byoacode.mdb".

Both data files are in CSV file format so they should be imported to Microsoft Access as external data using the "Text Files (*.txt; *.csv; *.tab; *.asc)" file type option. Please note that both files contain variable labels so the "First Row Contains Field Names" box must be checked. In this particular example, it is important to check that the "Postcode" fields are present in the two newly created tables and the values of the field are in the same format in each. Once successfully imported, a new Query needs to be constructed to link the two datasets together using the "Postcode" field that appears in each, so that there is a means of linking the postcoded data with the output area codes. Note carefully how many records there are in each table (this is displayed at the bottom of the window when viewing a table).

Open a new query using the Design view and add both tables. When first added to the query, these tables will by default be linked by the ID field (created automatically by Microsoft Access) but this is not meaningful as the rows in the tables do not correspond. This link may be deleted by right-clicking on it, and a new link created by dragging to connect the "Postcode" field in one table with the same field in the other. Fields from each table can be dragged and dropped into the output grid individually, or dragging and dropping the * symbol will move all fields in the table at once. Include the "Postcode", "Output Area" and "NOFFS" fields. "NOFFS" is the number of crimes reported. When closing the Query you will be prompted to save it, which you should do. Name the query as " _byoacode".

Double-clicking on the saved Query will run it, displaying the result of linking the two tabular datasets by their common field, the "Postcode". Each crime record now contains not only its postcode and number of offences, but also the code of the OA which it is fully within

Check that the Query has done what you were expecting, including the number of records: there should hopefully be one for each data row! All 51 records from the original data file should now be geo-referenced simultaneously by postcodes and by output area codes. Once confident that the correct result has been achieved, the output from this query can be modified to create a new table containing the linked data. Right click on the Query to open it again in Design view and from the Query menu choose "Make-Table Query...". Name the table as "linking_pcd_oa". Run the query again and it will create a new data table containing the matched data. You can now quit the query without saving.

Aggregating Data in Microsoft Access

Once an output area code has been assigned to each record, it is possible to aggregate the data to the output area level. Select Insert > Query and then choose the "Design View" option. In the Show Table dialog box, add the "linking_pcd_oa" table. Close the Show Table dialog box and drag and drop "Output Area" and "NOFFS" into the "Field:" row of the grid. Right-click anywhere of the grid and turn on the "Totals" option. A new "Total:" row will then be added to the grid. On this new row, click the "Group By" cell of the "NOFFS" column and select the "Sum" option. The values of "NOFFS" will now be aggregated by "Output Area". Close the Query window and save the results as " _aggoacode".

To examine the aggregated data, double-click on the " _aggoacode" query. The second column will now be renamed as "SumOfNOFFS" and the number of records will be reduced to just four rows of data, arranged by the unique area codes. 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.

The query results can now be exported to an external file for further analysis using File > Export, for example in Microsoft Excel spreadsheet (.XLS), database (.DBF) or comma-delimited text file (.CSV) formats. These options would also allow the aggregated data to be transferred to a mapping package.