BML224: Data Analysis for Research Database Management: Microsoft Access Learning Outcomes At the end of this session, you should be able to:
Construct, manipulate and interrogate a database management system
Create and import data from Excel into an Access database
Design basic and advanced queries to investigate data tables
Join and create relationships between different tables
Import and interrogate a series of data files
SEMAL Dr Andrew Clegg
Database Management
Data Analysis for Research
1.0
Introduction Basic data compilation, for example from a questionnaire survey, is usually performed using a spreadsheet in Microsoft Excel. However, for more advanced data handling and interrogation, Excel spreadsheets can be imported into Access to form the basis of a table within a database management system. The aim of this exercise, is to guide you through the basic steps of importing a series of Excel spreadsheets into Access and then performing a number of basic and advanced data queries. The initial datasheet you are going to import is called Hotel.xls and can be found in the Coursework folder on the student network or downloaded from the BAM211 website. The spreadsheet contains 1,233 records relating to serviced, self-catering and chalet holiday accommodation in Torbay. Open the spreadsheet in Excel and look how the data has been entered. The specific details of the different data fields and the coding schemes that have been employed are displayed in Table 1.
Table 1: Data Classification for Hotel.xls Column Headings
Description
Name Address ERTB
Name of the establishment Address of the establishment Marketing in the English Riveria Accommodation guide [1=yes; 2= no] Resort [T=Torquay; P=Paignton; B=Brixham] Establishments within Principal Holiday Accommodation Areas [1] Size of the establishment [1=small; 2=medium; 3=large] Classification of the establishment [1 = serviced; 2 = self-catering; 3 = chalet] Number of actual bedspaces available Licensed premises: [1=yes; 2= no] Open all year: [1=yes; 2= no] Open Christmas: [1=yes; 2= no] Indoor pool: [1=yes; 2= no] Outdoor pool: [1=yes; 2= no] TV: [1=yes; 2= no] Sauna: [1=yes; 2= no] Disabled facilities: [1=yes; 2= no] Launderette: [1=yes; 2= no] Tea-making facilities: [1=yes; 2= no] Entertainment: [1=yes; 2= no]
Area Code PHAA Code Size Type Bedspace Lic Open all year Open Christmas Indoor pool Outdoor pool TV Sauna Disabled Launderette Tea-making facilities Entertainment
Š Dr Andrew Clegg
p. 1
Database Management
Data Analysis for Research
When creating an Excel spreadsheet for use with Access there are a number of important points to remember to ensure that the data is imported successfully:
1.1
Make sure that the column headings have individual names. If the titles of column headings are repeated Access will report an error.
When entering data, do not enter text and numbers within the same column. Access works by identifying different data types (for example: numbers and text). Therefore, including text and numbers in the same data column will generate errors.
If you have a missing value then leave the cell blank. When the data is entered into the database, Access will register the data as a null value.
Creating and Importing Data into a New Database in Access To import an Excel spreadsheet, you first need to create a new database in Microsoft Access. Launch Access from the start menu. In the Getting Started with Microsoft Office screen click the Blank Database option.
The Blank Database dialog box appears. In this example, the database has been called TOURISM1 and has been saved in the My Documents folder. Use the same file name and save your new database to your own file space on the network. Click Create.
© Dr Andrew Clegg
p. 2
Data Analysis for Research
Database Management
A new table window will appear in the database window. Note that the table opens with a tab. You can open multiple tables and queries in the database window and navigate between them using the tabs. To close this table move the mouse over the Table1 tab and click the right button. A submenu appears. Move the mouse over Close and click the left mouse button. Any tables or queries created appear on the left handside of the database window.
Š Dr Andrew Clegg
p. 3
Data Analysis for Research
Database Management
To import external data, move the mouse over the External Data tab and press the left mouse button. Move the mouse over Excel and press the left mouse button. Follow the onscreen instruction to import your data file. First select the data file you want to use. Access will ask you to specify the source of the data.
In this example please select the Hotel data file.
Š Dr Andrew Clegg
p. 4
Database Management
Data Analysis for Research
The Import Spreadsheet Wizard dialog box appears. Follow the instructions outlined in the wizard and Access will import the data for you automatically. Step 1:
In the Excel spreadsheet you are going to import, the first row contains the field names of the variables that will form the basis of your table. You need to ensure that Access recognises this. Therefore, move the mouse over First Row Contains Field Names option and press the left mouse button (
becomes
).
Step 2:
Š Dr Andrew Clegg
p. 5
Data Analysis for Research
Database Management
Step 3:
Step 4:
Š Dr Andrew Clegg
p. 6
Data Analysis for Research
Database Management
A quick note on the Primary Key. The power of a relational database system such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries, forms, and reports. In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the Primary Key of the table. Once you designate a primary key for a table, to ensure uniqueness, Microsoft Access will prevent any duplicate or null values from being entered in the primary key fields. In the Excel file that you are import, the data field ‘Counter’ acts as this primary key so in this instance we can choose our own primary key.
Step 5:
Specify the name of the table. Press Finish.
© Dr Andrew Clegg
p. 7
Data Analysis for Research
Database Management
Access now imports the data and displays a message indicating that the process has finished. Note that a table called Holiday Accommodation has appeared in your database.
To view the contents of the table, double-click the Holiday Accommodation table.
Š Dr Andrew Clegg
p. 8
Data Analysis for Research
Database Management
To edit the names and properties of the individual data fields, move the mouse over the Home tab and press the left mouse button. Move the mouse over View and select Design View in the sub menu.
The table now appears in Design view.
Š Dr Andrew Clegg
p. 9
Data Analysis for Research
Database Management
In order for Access to process your queries properly, you need to change the data Data Type for some of the variables listed in the table. Any field that you plan to analyse numerically must be labelled as ‘Number’ in the Table Design view. To start, move the mouse over Text in the Data Type cell for PHAA code. An insertion point and a will appear. Click on and a submenu appears. Move the mouse over Number and press the left mouse button. The submenu closes and Number is now displayed as the chosen data type.
Repeat this process for the remaining Field Names in the table that are using a numeric coding scheme. The Design view also allows you to include a brief description of the field name. This is particularly useful for noting any coding schemes that might be in use.
Now you have successfully imported the Hotel.xls file into Access, you now need to import the following Excel files into your database, as these will be used later in the handbook. These files can be found in the Access Exercise Files folder in the Coursework Directory, or can be downloaded into your own file space from the BAM211 website. A guide to the content of each file is provided overleaf.
© Dr Andrew Clegg
p. 10
Database Management
Data Analysis for Research
Hotel Room Details 1994 This file contains details on the room structure of serviced accommodation in Torbay, and is based on those serviced establishments listed in the 1994 English Riviera accommodation brochure: Ensuite Double Ensuite Single Double Single Single -
ED ES D S
You will notice that the file contains no details on the actual location of hotels or their names and addresses. Do not panic! The details are already held in the Hotel file so you will have to create relationship between the two tables based on a common link, in this case the unique identification number given to all hotels in the column labelled ‘Counter’.
Interview Data This file contains the results of 200 detailed interviews that were conducted among operators of serviced accommodation establishments in Paignton and Torquay. The field names include [type of data indicated in brackets]: [AREA]: Area Code [Categorical] 1 = Torquay; 2 = Paignton [AGE]:
Age of Interviewee [Ratio]
[CORD]:
Competitive or Defensive Strategy [Categorical] 1 = Competitive; 2 = Defensive Refers to the type of strategy that the hotelier adopted in response to recession. Competitive refers to a more aggressive approach characterised by investment and a continued emphasis on marketing. Defensive strategies are less aggressive and are characterised by a reduction in investment, a degree of rationalisation, staff redundancies and possibly finding additional employment.
[SIZE]:
Size of the Business [Categorical] 1=Small; 2=Medium; 3=Large
[OCC]:
Previous Occupation [Categorical] 1=Managers and administrators; 2=Professional occupations 3=Clerical and secretarial; 4=Sales occupations; 5=Plant operatives
[INVEST]:
% Profit Reinvested into Business [Ratio]
[MOTIVE]:
Business Motivations of the Entrepreneur [Categorical] 1= Economic Motives (Maximiser); 2 = Non-Economic Motives (Satisficer)
© Dr Andrew Clegg
p. 11
Data Analysis for Research
Database Management
Serviced Questionnaire Results This file contains selected results taken from the questionnaire survey to serviced accommodation establishments in Torbay. A copy of the questionnaire that accommpanies this spreadsheet can be downloaded from the BAM211 website. Guidance Notes: The following coding scheme was used on the questionnaire survey: Q6: you will need to devise a coding scheme to show prior residence Q7B: coding scheme for prior occupations: 1: Managers and Administrators 2: Professional occupations 3: Associate professional and technical occupations 4:Clerical and secretarial occupations 5:Craft and related occupations 6:Personal and protective service occupationss 7:Sales occupations 8:Plant and machine operatives 9:Other occupations
Q8: motives for entering the industry - factors are marked as (E) Economic or (NE) Non- Economic Q8/12: 1= (E) Economic; 2 = (NE) Non- Economic; 3 = Both For questions including Major, Minor and Not Imp - a recorded value of 4 indicates no response Q19 (and similar questions): a recorded value of 1 is yes and 2 no Q24, Q25,Q28,Q30: a recorded value of 5 or 0 indicates no response
Any additional questions about this data set please see me. All the different data sets will be discussed in the lecture programme. Serviced Grading Standards This file contains details of the level of grading listed by serviced accommodation establishments in the 1994 English Riviera holiday accommodation brochure. The numbers listed refer to the actual number of stars (6 refers to hotels where there is no listing). This database should be treated as a sub-sample of 356 serviced establishments.
© Dr Andrew Clegg
p. 12
Database Management
Data Analysis for Research
Planning Data The planning files contain the details of all planning applications relating to the holiday accommodation industry between 1985 and 1994. The structure of the spreadsheets is self-explanatory and the following coding scheme applies for the field name DETAIL CODE: Planning Application Information Categories 1.
Extensions New accommodation Ensuite accommodation New facilities Support facilities Residential accommodation Extension (unstated) Extension to owners accommodation Other
Code 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8
2.
Change of Use To residential accommodation To holiday accommodation To alternative holiday accommodation To commercial To residential/care/nursing homes Other
2.1 2.2 2.3 2.4 2.5 2.6
3.
Health and Safety Legislation Requirements
3
4.
Car Parking and Access
4
5.
New Development New holiday accommodation New residential accommodation New facilities Support facilities Other
5.1 5.2 5.3 5.4 5.5
6.
Refurbishment of property
6
7.
Redevelopment
7
8.
Other
8
Š Dr Andrew Clegg
Other Overall Granted: 1 = Planning Permission Granted Type 1 = Serviced 2 = Self-catering 3 = Chalet 4 = Other Area Code T = Torquay P = Paignton B = Brixham p. 13
Data Analysis for Research
Database Management
When the tables are imported into Access, they will be displayed in the database window. The tables are displayed as Tables and Related Views in what is called a Shutter Bar. This view is slightly confusing. To change the view move the mouse over All Tables and select the options button. A submenu appears.
From this menu select Object Type. Your tables are now displayed in alphabetical order under Tables. Any additional tables or queries that you subsequently create will be displayed here. To minimise the Shutter Bar, move the mouse over the Shutter Bar Open/Close arrow and press the left mouse button.
Š Dr Andrew Clegg
p. 14
Data Analysis for Research
1.2
Database Management
Designing and Performing Data Queries in Access With the main data table in place, you can now create a series of queries based on the Data table. To create a query move the mouse over Create and press the left mouse button. Move the mouse over Query Design and press the left mouse button.
To design a new query move the mouse over New and press the left mouse button.
The New Query dialog box appears. Move the mouse over Design View and press the left mouse button.
The query design screen will then appear. Automatically, Access prompts you to add a table. Move the mouse over the table labelled HOLIDAY ACCOMMODATION and press the left mouse button. Move the mouse over Add and press the left mouse button again. This adds the data table to the query design window. Move the mouse over Close and press the left mouse button.
Š Dr Andrew Clegg
p. 15
Database Management
Data Analysis for Research
The field names in the data table are now displayed in the design preview screen.
The aim of the following section is to show you how to design and execute basic data queries. To start, the following example illustrates how to create a query that identifies all the holiday accommodation establishments in Torquay. First, double click on the field names you want to include in the query. In this case include Area Code and Bedspace. The fields you have selected appear in the bottom half of the query design screen.
Press the datasheet view (
) button on the main toolbar. As you can see from the datasheet, this query merely lists all the accommodation establishments listed on the database. In order to ‘filter’ those establishments in Torquay, type ‘T’ in the criteria box below Area Code.
Now press
© Dr Andrew Clegg
.
p. 16
Database Management
Data Analysis for Research
You will now notice that the datasheet lists only those establishments in Torquay. The total number of entries is recorded at the bottom of the query screen.
Move the mouse over File and press the left mouse button. Move the mouse over Save As and press the left mouse button again. The Save As dialog box appears. Type in an appropriate name for your query (e.g. HOLIDAY ACCOMMODATION IN TORQUAY) and press OK. Close the query to return to the main query screen. Create and save similar queries to answer the following:
Question:
Answer:
How many holiday accommodation establishments are there in Torquay ?
................................
How many holiday accommodation establishments are there in Paignton ?
................................
How many holiday accommodation establishments are there in Brixham ?
................................
By inserting different field names into the design query you can produce more detailed answers. For example, repeat the process outlined above but this time also add the Type of accommodation.
This time you are going to filter out serviced accommodation. Type ‘1’ [the numeric code for serviced accommodation] in the criteria box below Type. Now press . You have refined your query further and now your datasheet view lists all the serviced accommodation in Torquay. Save this query with an appropriate file name. Repeat this process in order to calculate the type and number of accommodation establishments in each resort. IMPORTANT NOTICE It is very important to remember that when conducting queries you should only have one table in the query design window at a time (unless they are joined by a relationship - see page 29). If your query design contains more than one table access will not generate the correct results!!
**Please refer to page 42 for more details on filter queries**
© Dr Andrew Clegg
p. 17
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Table 1: The Distribution of Holiday Accommodation in Torbay Resort
Serviced
Self-catering
Chalet
Torquay Paignton Brixham Total
Refine your query further to calculate the size structure of serviced and self-catering accommodation in Torbay. In this instance you will need to include the Size field in your query. Table 2: The Size Structure of Serviced Accommodation in Torbay Resort
Small
Medium
Large
Torquay Paignton Brixham Total
Table 3: The Size Structure of Self-Catering Accommodation in Torbay Resort
Small
Medium
Large
Torquay Paignton Brixham Total
Š Dr Andrew Clegg
p. 18
Data Analysis for Research
Database Management
Access Queries: Self-Directed Activities Having examined aspects of the Holiday Accommodation table, devise a series of queries to complete the following table in relation to planning applications in Torbay. Table 4: Planning Applications in Torbay
Š Dr Andrew Clegg
p. 19
Database Management
Data Analysis for Research
1.3
Advanced Queries The query function can also be used to perform more advanced queries. For example you can calculate the total number of bedspaces in Torquay. To do this, follow the procedure outlined to create a query that lists all the serviced establishments in Torquay. Within the design view screen, move the mouse over
in the
main toolbar and press the left mouse button. You will notice that an extra line has now appeared in the query grid and the terms Group By has appeared underneath the field names.
Underneath Bedspace, move the mouse over Group By and press the left appears. Move the mouse over this button and press mouse button. A the left mouse button again. A list of available options appears in a scrolling text box. Move the mouse over Sum and press the left mouse button. Now press . The datasheet view now shows the number of bedspaces in serviced accommodation in Torquay.
to get Run the same query again but this time delete the ‘T’ under Area Code in the query grid. Press the spreadsheet view. In this instance, the datasheet gives you the total bedspace capacity for serviced accommodation in each resort. Other options will also give you the average bedspace size and the minimum and maximum values. Experiment with using these additional options to complete the table on the following page.
Note that when no filter is included in the query (e.g. T for Torquay), the query will ‘Group By’ the coding regime applied to that field. In this case, it has grouped by B: Brixham, P: Paignton and T:Torquay. If any other letter had appeared then this would be an error which you would have to correct in the main table. © Dr Andrew Clegg
p. 20
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Table 5: Bedspace Characteristics of Holiday Accommodation in Torquay Serviced
Self-catering
Chalet
Total Number of Bedspaces Minimum Value Maximum Value Average Value
Table 6: Bedspace Characteristics of Holiday Accommodation in Paignton Serviced
Self-catering
Chalet
Total Number of Bedspaces Minimum Value Maximum Value Average Value
Table 7: Bedspace Characteristics of Holiday Accommodation in Brixham Serviced
Self-catering
Chalet
Total Number of Bedspaces Minimum Value Maximum Value Average Value
Š Dr Andrew Clegg
p. 21
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Use basic queries to examine the distribution of facilities in the resort. To complete these tables you will have to create more than one query. Table 8: The Distribution of Facilities by Serviced Accommodation No. Sauna Disabled Facilities Tea-Making Facilities Indoor Pool Outdoor Pool TV
Table 9: The Distribution of Facilities by the Size of Serviced Accommodation Small
Medium
Large
Sauna Disabled Facilities Tea-Making Facilities Indoor Pool Outdoor Pool TV [Note: you will have to create more than one query to complete this table. Also include the percentage values in brackets which you will need to calculate manually]
Š Dr Andrew Clegg
p. 22
Database Management
Data Analysis for Research
1.4
Advanced Queries - Crosstabulations The query function in Access can also be used to create cross-tabulations, which are particularly useful when analysing data from questionnaire surveys. Follow the same steps as creating a basic query. In the query grid include the Area Code and Size. The Size field should be entered twice. Your query grid should therefore look like this:
Move the mouse over Crosstab in the main tool bar at the top of the screen and press the left mouse button. An extra line appears in the query grid at the bottom of the screen (labelled Crosstab:)
Move the mouse over the first grid cell in the Crosstab row and press the left mouse button. A
appears. Move the mouse over this button and press the left mouse button again. A list of available options appears in a scrolling text box. Move the mouse over Row Heading and press the left mouse button again.
Š Dr Andrew Clegg
p. 23
Database Management
Data Analysis for Research
Repeat the process again, but in the second cell choose Column Heading
Repeat the process again, but in the third cell choose Value.
Under the field name Size change Group By to Count.
Now press
. You will now notice that the datasheet has created a crosstab with the names of the resorts as rows and the size of establishment (1=small;2=medium; 3= large) as the columns and each cell refers to the number of holiday accommodation establishments within each size category within each resort.
Row
Table content: in this case - ‘Count’
Column
In effect, your query has been designed to reflect how the data would be presented in a normal contingency table. When designing a cross-tab query, you need to ensure you have included fields that will correspond to columns, rows and contents.
© Dr Andrew Clegg
p. 24
Data Analysis for Research
Database Management
The data shown in this query refers to all accommodation in Torbay. By adding additional field names into the query, you can separate the relative size structures for serviced, self-catering and chalet accommodation. In the design view, insert the field name Type to the query grid.
In order to ‘filter’ out the serviced holiday accomodation establishments, type ‘1’ (the code for serviced accommodation) in the criteria box below Type.
Now press . You will now notice that the datasheet has created a crosstab with the names of the resorts as rows and the size of serviced accommodation establishments as the columns. Each cell refers to the number of serviced holiday accommodation establishments within each resort. By including the field name Type who have effectively included a filter which is not shown in the query itself. By changing the 1 in the criteria cell under Type, you can also calculate the number of self-catering (2) and chalet establishments (3).
If you were also to type in ‘T’ in the criteria grid under Area Code, you could also filter out those serviced establishments in Torquay. Thus giving:
To ensure your familiarity with the system, devise the appropriate queries and cross-tabulations to complete the tables overleaf.
© Dr Andrew Clegg
p. 25
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Use the procedures outlined in the previous section to now explore the characteristics of those establishments located within Principal Holiday Accommodation Areas (PHAAs) in Torbay. In the previous section you used the Type of accommodation as a filter in the crosstab query. You will have to design queries in a similar way, but this time use PA Code as the filter to identify those establishments located in PHAAs. Table 10: The Distribution of Holiday Accommodation Establishments in PHAAs by Resort Resort
Total
Torquay Paignton Brixham Total
Table 11: The Distribution of Holiday Accommodation Establishments in PHAAs by Type and Resort Resort
Serviced
Self-Catering
Chalet
Torquay Paignton Brixham Total
Š Dr Andrew Clegg
p. 26
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Table 12: The Distribution of Serviced Accommodation in PHAAs by Size and Resort Resort
Small
Medium
Large
Torquay Paignton Total
Table 13: The Distribution of Self-Catering in PHAAs by Size and Resort Resort
Small
Medium
Large
Torquay Paignton Total
Š Dr Andrew Clegg
p. 27
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Using the Interview set, create a series of queries to complete the following tables. The Room StructureData of Serviced Accommodation in Brixham by Size Table 14: Business Strategy of Serviced Providers, by Size of Establishment Medium
Small
Large
Total
Competitive Strategy
Defensive Strategy
Total
Table 15: Business Strategy of Serviced Providers, by Location Torquay
Paignton
Total
Defensive
Total
Competitive Strategy
Defensive Strategy
Total
Table 16: Occupation by Business Strategy Competitive 1 - Managers/Administrators 2 - Professional Occupations 3 - Clerical & Secretarial 4 - Sales Occupations 5 - Plant Operatives Total
Š Dr Andrew Clegg
p. 28
Data Analysis for Research
1.5
Database Management
Joining Tables and Creating Relationships One of the main advantages of using Access, is that new tables of information can be added without changing the structure of the existing system. By using field names and coding schemes common to all tables, relationships can be formed linking these tables together. This type of structure therefore allows very specific analysis using customised query forms. In the next section, you are going to link the Hotel Room Details 1994 table you have already imported into Access to the Holiday Accommodation table. To start, examine Hotel Room Details 1994 file in Microsoft Excel. As you will see the spreadsheet contains details relating to the number of ensuite double (ED), ensuite single (ES), double (D) and single (s) rooms listed by serviced establishments in the 1994 English Riviera accommodation brochure. However, the file contains no details on the actual location of the hotels or their names and addresses. Do not panic! These details are already held in your database - you are going to access this information by creating a relationship between the two tables based on a common link, in this case the unique identification number given to all the hotels in the column labelled ‘Counter’. To create a relationship, move the mouse over Database Tools tab and press the left mouse button. Move the mouse over Relationships and press the left mouse button again.
The Show Table dialog box opens listing all the tables that you have imported/ created in Access. Select Holiday Accommodation and click Add.
© Dr Andrew Clegg
p. 29
Data Analysis for Research
Database Management
Select Torbay Hotel Room Details 1994 and click Add.
Then click Close.
The Relationship Window appears, with the Holiday Accommodation and Torbay Hotel Room Details 1994 visible.
To create a relationship you have to link the Counter fields in both tables together. To do this, move the mouse over Counter in the Holiday Accommodation table and press the left mouse button. Hold down the left mouse button and drag the Counter field directly over the Counter field in the Hotel Room Details 1994 table. When it is directly positioned over the Counter field release the left mouse button.
Š Dr Andrew Clegg
p. 30
Data Analysis for Research
Database Management
A Relationship dialog box will appear confirming the relationship you have just created.
Move the mouse over Join Type and press the left mouse button.
The Join Properties dialog box allows you to set the nature of the relationship. In this instance we need to ensure that records in both tables are equal, so therefore move the move over option 1 and press the left mouse button.
Move the mouse over OK and press the left mouse button again. This returns you to the Relationship dialog. Move the mouse over Create and press the left mouse button. An arrow line will appear between the two lines indicating the existence of the relationship. To edit the relationship at a later date just double click the left mouse button on this line and the Relationship dialog box will appear.
Š Dr Andrew Clegg
p. 31
Data Analysis for Research
Database Management
Close the Relationship window, via the Relationship tab. You will be prompted to save the relationship you have created. Click Yes. This will now return you to the main database window. Follow the procedure outlined in the earlier section of this handbook and create a query that includes both the Holiday Accommodation table and Hotel Room Details 1994 table. Notice that within the query window the relationship between the two tables is highlighted.
Your query window should look like this:
With our relationship now established we are going to use the two tables to create a query that will provide the Area Code and the number of ensuite double bedrooms (94ED) for serviced accommodation. Note that you do not have to include the Counter field in your query to establish the relationship between the two tables as this has already been created.
Š Dr Andrew Clegg
p. 32
Data Analysis for Research
Database Management
From the Holiday Accommodation Table, double-click on the field(s) you want to include. In this case select Area Code. From the Hotel Room Details 1994 table, double-click on the field(s) you want to include. In this case select 94ED. Your query design screen should now look like this. Notice that the screen actually highlights the table that the different field names have come from.
Press the datasheet view (
) on the main toolbar. You now have a new spreadsheet giving you the Area Code and the number of ensuite double rooms. You will notice that no blank values appear in the query and the database has matched entries where the counter number is identical in both tables (i.e. Counter). Save your query. This is now effectively a new query table and can be be used as a base table for any additional enquiries regarding room details. [DISCLAIMER: You may spot some slight discrepancies in the data and query tables. For example in the above query only 353 entries are listed instead of 356. Ignore these discrepancies - they are related to data coding and trying to consolidate a range of large data sources into one database]. Š Dr Andrew Clegg
p. 33
Data Analysis for Research
Database Management
The query function can also be used to perform more advanced queries. For example you can calculate the total number of bedspaces within each resort. Refer back to page 23 and the section on creating advanced queries. Within the design view screen, move the mouse over in the main toolbar and press the left mouse button. You will notice that an extra line has now appeared in the query grid and the terms Group By have appeared underneath the field names.
Underneath 94ED, move the mouse over Group By and press the left mouse appears. Move the mouse over this button and press the left button. A mouse button again. A list of available options appears in a scrolling text box. Move the mouse over Sum and press the left mouse button. Now press . The datasheet view now shows the number of bedspaces in serviced accommodation within each resort.
Other options will also give you the average bedspace size and the minimum and maximum values. Refine your query further to complete the tables on the following page. You can include more than one field name in your query at this point.
Š Dr Andrew Clegg
p. 34
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Table 17: The Room Structure of Serviced Accommodation in Torbay Resort
94ED
94ES
94D
94D
TotalBS
TotalRM
Torquay Paignton Brixham Total
Table 18: The Room Structure of Serviced Accommodation in Torbay by Size 94ED
94ES
94D
94S
TotalBS
TotalRM
Small Medium Large Total
Table 19: The Room Structure of Serviced Accommodation in Torquay by Size 94ED
94ES
94D
94S
TotalBS
TotalRM
Small Medium Large Total
Table 20: The Room Structure of Serviced Accommodation in Paignton by Size 94ED
94ES
94D
94S
TotalBS
TotalRM
Small Medium Large Total
Š Dr Andrew Clegg
p. 35
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Table 21: The Room Structure of Serviced Accommodation in Brixham by Size 94ED
94ES
94D
94D
TotalBS
TotalRM
Small Medium Large Total Create a series of new relationships linking the Holiday Accommodation table to the various planning tables (see page 39). Once you have done this attempt to complete the following tables, using a crosstab query, and additional filters. You will need to create more than one query to complete these tables. Refer back to p. 14 for information on the numerical codes used for planning applications. Table 22: The Size Structure of Serviced Accommodation with Granted Planning Permission for Change of Use to Residental Accommodation, 1985 to 1994 1985
1987
1989
1990
1992
1994
Small Medium Large Total
Table 23: The Size Structure of Serviced Accommodation with Granted Planning Permission for Refurbishment, 1985 to 1994 1985
1987
1989
1990
1992
1994
Small Medium Large Total
Š Dr Andrew Clegg
p. 36
Data Analysis for Research
1.6
Database Management
Creating Additional Relationships So far you have created one relationship between the Holiday Accommodation table and the Hotel Room Details 1994 table. Access allows you to create more than one relationship, and you can link several tables to the main Holiday Accommodation table. Remember in order for relationships to be established, each table must contain a common field name in this case ‘Counter’. To create an additional relationship, move the mouse over the Database Tools tab• and press the left mouse button. Move the mouse over Relationships and press the left mouse button again. The Relationships window is displayed, showing the relationships you have already established.
To add a new table, move the mouse over the Show Table button and press the left mouse button. The Show Table dialog box opens listing all the tables/queries that you have imported/created in Access. Select Planning Information 1985 and click Add.
© Dr Andrew Clegg
p. 37
Data Analysis for Research
Database Management
The Planning Information 1985 table is displayed in the Relationships window..
Follow the instructions on page 31 to create a link between this table and the Holiday Accommodation table.
Š Dr Andrew Clegg
p. 38
Data Analysis for Research
Database Management
Once you have included all the required tables you can move them around within the window, so the nature of the relationships between the different tables is apparent. Once finished Close and Save the changes to your relationships. You can move the tables around in the relationship window to help you visual the relationship between your different data tables.
Š Dr Andrew Clegg
p. 39
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Using a relationship the Accommodation Holiday Accommodation Table and the Serviced Questionnaire The Room Structure between of Serviced in Brixham by Size Results, attempt to complete the following tables. Table 24: The Prior Occupation of Proprietors of Serviced Accommodation by Resort Q7B: Occupation
Brixham
Paignton
Torquay
1: Managers & administrators 2: Professional occupations 3: Associate professional & technical occupations 4: Clerical & secretarial occupations 5: Craft & related occupations 6: Personal & protective service occupations 7: Sales occupations 8: Plant & machine operatives 9: Other occupations
Š Dr Andrew Clegg
p. 40
Database Management
Data Analysis for Research
Access Queries: Self-Directed Activities Using a relationship the Accommodation Holiday Accommodation Table and the Serviced Questionnaire The Room Structure between of Serviced in Brixham by Size Results, attempt to complete the following tables regarding the motives of serviced accommodation providers in Torbay. You need to use Q8/12 in the serviced questionnaire results to refer to respondents motivations (see page 13). Table 25: The Motives of Serviced Accommodation Providers for Entering the Tourism Industry in Torbay Brixham
Paignton
Torquay
Economic Motives [Maximisers] Non-Economic Motives [Satisficers] Economic & Non-Economic [Both]
Table 26: The Motives of Serviced Accommodation Providers for Entering the Tourism Industry in Torbay, by the Size of Establishment Small
Medium
Large
Economic Motives [Maximisers] Non-Economic Motives [Satisficers] Economic & Non-Economic [Both]
Š Dr Andrew Clegg
p. 41
Data Analysis for Research
1.7
Database Management
Filter Queries In this handbook you have used queries to filter information from the main data tables. A number of useful additions to refine your query searches include: In this query if you type in ‘t or b’ under Area Code, Access will filter out entries relating to both Torquay and Brixham. The operative command here is: Or
In this query if you type ‘Is not null’ under ERTB, Access will filter out all the blank cells and give you a list of business advertising in the ERTB brochure. The operative command here is: Is Not Null.
In this query, if you use the > (more than )or < (less than) commands under Bedspace, Access will look for entries relating to the criteria you have set. In this example, Access will filter out all those entries that have less than 50 bedspaces. In this query, using the > (more than )or < (less than) commands together under Bedspace, Access will filter out entries with 51 to 99 bedspaces, based on the command: >50 and <100. You can experiment with these options to refine your searches further. These commands can be applied to numeric values, including dates. If you are looking for an entry based around a specific word, you can also use this as the basis of your criteria. In this example, by typing in *queen* under the address variable, Access will filter out all the entries that have ‘queen’ in any part of the address. You can use this option with any variable but you must put * at the beginning and at the end of the word you are using as the basis of your query. Where you use a Where instruction in a field, Access uses in the query any criteria you create in the field but does not display the field itself in the results of the query.
© Dr Andrew Clegg
p. 42
Database Management
Data Analysis for Research
1.8
Filter Queries within the Table View Access will also let you filter information within the main data table view. For example in the following the Area Code column has been selected within the Holiday Accommodation table. Move the mouse over Selection and press the left mouse button. A submenu appears, giving you a number of filter options. If you select ‘Equals B’ access will automatically filter out all those entries for Brixham (B).
Alternatively, move the mouse over Filter and press the left mouse button. A submenu appears.
This option allows you to select/deselect specific values in the column as the basis of a filter. For example in the following P has been selected. Access will automatically filter out all those entries for Paignton (P).
Also note that this option provides you with a sorting tool to list the values in the column in alphabetical order.
© Dr Andrew Clegg
p. 43
Data Analysis for Research
Database Management
Access Queries: Self-Directed Activities Using the Structure additionaloffilter queryAccommodation options, answer inthe following based on the Holiday The Room Serviced Brixham by questions Size Accommodation Table: How many accommodation establishments in Torbay have less than 50 bedspaces? ......................... How many accommodation establishments in Torbay have over 250 bedspaces? ......................... How many accommodation establishments in Torbay have between 100 and 200 bedspaces? ......................... How many serviced accommodation establishments in Torquay have less than 60 bedspaces? ......................... How many serviced accommodation establishments in Brixham have less than 40 bedspaces? ......................... How many serviced accommodation establishments in Paignton have more than 150 bedspaces? ......................... How many self-catering accommodation establishments in Paignton have more than 100 bedspaces? ......................... What is the name of the hotel in Torquay at postcode TQ1 2NN? ........................ How many bungalows are listed in the database? ........................
Š Dr Andrew Clegg
p. 44
Data Analysis for Research
Database Management
Access Queries: Self-Directed Activities Using the additional query options, answer the following questions based on the Interview dataset. The Room Structurefilter of Serviced Accommodation in Brixham by Size 1. How many businesses in Torquay are run by entrepreneurs who are over the age of 42, and who have invested more than 40% of turnover into their business in the last year. ......................... 2. How many businesses adopting competitive strategy, run by green maximisers are over the age of 36? ......................... 3. How many businesses are run by green maximisers, aged between 26 and 41? ......................... 4. How many businesses adopting a competitive strategy, run by those with a previous professional occupation invest more than 25% of turnover into their business? ......................... 5. How many businesses who adopt a defensive strategy are run by entrepreneurs who were not previously employed in a professional occupation? .........................
Š Dr Andrew Clegg
p. 45