Profiling your data
Hopefully after following the previous instructions you will now have an Access database which contains the following tables:
OAC – Output Area to OAC lookup: Columns – OACODE, GOR, SUPER, GROUP, SUB.
User –Your data that you want to analyse: Columns – Postcode (with no spaces), any other variables you plan to analyse.
Postcode – The postcode to OA lookup: Columns – Postcode (with no spaces), OA Code.
GROUP – The OAC Group descriptions: Columns – GROUP, GROUPDESC
SUB – The OAC Sub-Group descriptions: Columns – SUB,SUBDESC
SUPER – The OAC Super-Group descriptions: Columns – SUPER, SUPERDESC
The first step is to set up relationships between the tables in your database. Click the menu “Tools” and then on “Relationships”. You will see the Relationships dialog window open up. There are already some relationships set up which join the tables SUPER, GROUP and SUB to the table OAC. Right click in the grey area and click “Show Table…” from the menu. Highlight “Postcode” and click add. Then highlight “User” and click “Add”. Now click “Close”. Two more boxes should now have appeared in the Relationships dialog window.
The first step is to join the OAC table to the postcode table. Click and hold on the “Postcode” table over where it states the OACODE (this variable may have a different name depending where the postcode directory came from – it is the Output Area code), now drag the mouse towards “OA” on the OAC table. Once your mouse hovers over “OA” release it to open the Edit Relationships dialog box. Click the button “Join Type…”, and in the dialog box that opens select the middle option (2) that states it will include all records from the table Postcode and only those matched records from OAC. Click “OK”, then click “Create” to set the relationship. You should now see a new arrow drawn between OACODE on the table Postcode leading to OA on the table OAC.
The second relationship will join your data to the OAC classification. Click and hold on “Postcode” in the box “User”. Drag the mouse while still holding until it is over “Postcode” on the table “Postcode”. The Edit Relationships dialog box will open again. Click the button “Join Type…”, and in the dialog box that opens select the middle option (2) that states it will include all records from the table User and only those matched records from table Postcode. Click “OK”, then click “Create” to set the relationship. You should now see a new arrow drawn between OACODE on the table Postcode leading to OA on the table OAC. Click the red cross in the top right of the Relationships dialog window, and then click “Yes” to save the edits you have just completed.
In the database dialog window, click “Queries” and then double click “Create query using wizard”. In the “Simple Query Wizard” window click on the “Tables/Queries” drop down box and select “Table: User”. The columns on your dataset are listed in the left hand side white box. If you want a particular variable on the final dataset click on the variable and then the single “>” arrow. You will see the column name moves from the left box into the right box. Using the “Tables/Queries” drop down box again, select “Query: OAC with Names”. In the left hand side box, click on the following variables and add then add them to the right box with the single “>” arrow: SUPER, SUB, GROUP, SUPERDESC, SUBDESC and GROUPDESC. Then click “Finish” to run the query. If all steps have been completed correctly, you will now see your original data with the OAC classification appended. The query you have just created should be called “User Query”. If you were happy with the results of this query you can create a permanent copy by right clicking “User Query” and selecting “Export…” from the popup menu that appears. In the dialog box that opens you can select where you want to save the file using the “save in:” drop down menu. You also need to select the type of file you want to create, e.g. “Microsoft Excel 97-2003 (*.xls)” from the “save as type:” drop down menu. Finally, write a file name in the “File name” box and click “OK”. The file should now be saved in the location and type you have just specified.
Last modified:
