The data for this case study are taken from the Population Census for England and Wales of 1851. The data file can be found in K:\IT and is called 'Coalbrookdale Census 1851'.
Before using each file check that Autofilter has been turned on - you should see arrows next to the column headings in row 1. If you cannot see the arrows then select Data/Filter/Autofilter.
Remember to use the 'All' option in a drop-down list to restore the full list of data items.
Another way to analyse the data is to use a pivot table. To do this, select all the data from row 1 to row 226, columns A-L and choose Data/Pivot Table and Pivot Chart Report. Run through the wizard to set up the empty pivot table.
As a first example, drag the Family Position field into the area marked 'Drop Row Fields Here'. Drag the Sex field into the area marked 'Drop Column Fields Here'. Finally, drop the Family Position field into the area marked 'Drop Data Items Here'.
This plots sex against family position so we can observe some social patterns of the time.
The button second from the right on the PivotTable toolbar opens the field control list from which the displayed data type can be changed. Count is used to add up occurrences of non-numeric values of a certain form e.g. 'Head' or 'Servant'. Variable types such as Sum, Average, Max and Min can only be applied to numeric data, not to text values.
Click the Chart button in the PivotTable toolbar to create charts based on the current data.
To create a new pivot table drag the field names back to the Field List and drag new ones into the 'Drop' areas.
To convert the spreadsheet data into a database proceed as follows:
In Access, create a new database and save it as 'coalbrookdale'
Create a new table using the wizard and add one field such as 'Name'
Set this field as the primary key, save it as 'Table1' and click the icon to switch to datasheet view
Choose File/Get External Data/Import and follow the wizard. Select the file from its location, select the worksheet in the workbook that contains the data (sheet 1), tick 'First Row Contains Column Headings', leave 'In a New Table' selected and click Finish. This will create a new table in Access so you can delete the first (dummy) table, this was only created to gain access to the Get External Data/Import wizard. Rename the new table by right-clicking it and entering a suitable name.
You can now write queries in Access to interrogate the database.
Click the Back Button to Return