Getting Grid Coordinates from Postcodes in Microsoft Access

In this example we will show how to relate UK postcodes to grid coordinates (eastings and northings) via a lookup table. The coordinates acquired can then be used for mapping the locations of the postcoded data. This operation is best 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.

Included for this example are a set of fictitious postcoded records and a lookup table between postcodes and coordinates. Click [here] to download the zip archive for this example. 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 grid coordinates lookup table for the same area. An extensive postcode lookup table covering the whole UK that is known as the National Statistics Postcode Directory (NSPD) 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 coordinates 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 " _bycoord.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 coordinates. 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", "NOFFS" (number of offences) , "OSEAST" (eastings) and "OSNORTH" (northings) fields. When closing the Query you will be prompted to save it, which you should do. Name the query as " _bycoord".

Double-clicking on the saved Query will run it, displaying the result of linking the two tabular datasets by their common field, the "Postcode". We can now see the data displayed in relation to the postcodes and coordinates.

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 records from the original data file should now be geo-referenced simultaneously by postcodes and by a pair of eastings (OSEAST) and northings (OSNORTH). Once confident that the correct result has been achieved, the output from this query can then be saved as a table for further work. 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 " _bycoord". Run the query again and it will create a new data table containing the matched data. You can now quit the query without saving. Note that selecting the table 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 results can now be exported using File > Export to an external file for further analysis, for example in Microsoft Excel spreadsheet (.XLS), database (.DBF) or comma-delimited text file (.CSV) formats. These options would also allow the coordinate data to be transferred to a mapping package.