Creating Tables and Relationships



UCL

Education & information support division

information systems

Access

Designing and Developing Databases

Document No. IS-004

Contents

Database Design 1

Objectives of database design 1

Process of database design 1

Creating a New Database 3

Tables 4

Creating a table in design view 4

Defining fields 5

Creating new fields 6

Modifying table design 6

The primary key 8

Indexes 8

Saving your table 10

Field properties 11

Importing Data 14

Importing data from Excel 14

Lookup fields 16

Relationships 20

Creating relationships 21

Viewing or editing existing relationships 22

Referential integrity 22

Forms 24

Creating a simple AutoForm 24

Reports 25

Create a report with the Report Wizard 25

Previewing a report 27

Printing a report 27

Getting Help with Access 28

Learning more 28

Introduction

This document is intended for those who have experience of using an Access database and would like to learn how to create their own database and to create simple forms and reports. Although some design guidance is given here, this document is intended as a practical guide to creating databases in Access rather than as a guide to database design theory.

How to use this guide

This guide is intended for use as a reference document. It may also be used for self-paced study if used in conjunction with the Exercises document of the same name which contain a series of practical tasks to accompany each section of this workbook. It is recommended that you try each of these tasks as you progress through the guide, to assist your learning. The exercises document and associated files are available to download at: ucl.ac.uk/is/documents/

If you have any difficulty with instructions within this guide, contact the Information Systems Training team at: is-courses@ucl.ac.uk.

Database Design

Time spent in designing a database is time very well spent. A well-designed database is the key to efficient management of data. You need to think about what information is needed and how that information is to be used.

Objectives of database design

The strategy of database design is to accomplish the following objectives:

• To organise stored information in a timely, consistent, and economical manner.

• To eliminate, or minimise, the duplication of database content across the organisation.

• To provide rapid access to the specific elements of information in the database required by each user.

• To accommodate the possible expansion of the database to adapt to the needs of a growing organisation, such as the addition of new products and processes.

• To maintain the integrity of the database so that it contains only validated, auditable information.

• To prevent access to the database by unauthorised persons.

Process of database design

Planning your database

This should be done on paper.

Determine the purpose of your database. This will include deciding what information needs to be stored and what will need to be retrieved. What ‘questions’ will you need to ask your data?

Tables

Decide what tables you need in your database:

• A table should not contain duplicate information and information should not be duplicated between Tables. If information needs to be updated, it should only need updating in one place. This is more efficient and also eliminates the possibility of duplicate entries that contain different information. Do not include any derived or calculated data (data that is the result of an 'expression') as this is effectively duplicate information.

• Each table should contain information about one ‘entity’ or subject. This enables you to keep information about each subject independently from other subjects.

Fields

Determine the fields you need in the tables:

• Each field should relate to the subject of the table.

• Each field should contains a particular type of information about the table's subject.

• Create fields so that you can store information in its smallest logical parts (e.g., First Name, Initial, last name, rather than just name). How small this part is will depend on how you will want to sort, filter or query your records.

Primary keys

Identify which fields have unique values and decide which field(s) will be your primary key(s). See The Primary Key on page 6 for further details on primary keys.

Data types

Determine the appropriate data types for each of your fields (e.g. Text, Currency, Date, etc). Unless you want to use the default data type (Text), you will need to assign a data type to each of your fields. All data in a single field must consist of the same data type.

Relationships

Identify associations between the tables (when you have more than one Table). You will need to define relationships between your tables so that Access can bring related information from different Tables back together in meaningful ways.

Implementing your design

The list below is a suggested step-by-step plan:

• Create a small database based on your design.

• Specify relationships between the tables

• Enter a few records in each table.

• Create rough drafts of your forms and reports to see if they contain the data you need.

• Try running a few queries to see if you get the answers you expect.

• Check your database for any unnecessary duplication of data.

• Check your design with anyone else who will be using your database.

• Decide who will be able to access the database, the tables, and the fields within the tables.

• Finally, enter your data into your tables. Create any queries, forms, reports, etc. that you need.

• Ensure all the data in your database is relevant and kept up-to-date.

• Remember the Data Protection Act.

Creating a New Database

Before you can create objects such as tables and forms, you must first create the database file in which they will be stored.

1. From the File menu select New. The New File task pane will appear at the right of the screen (seen here).

2. Under New click on Blank database. The File New Database dialog box appears.

3. In the Save in box, select the folder you want to store your database in.

4. In the File name box, type a name for your database.

5. Click Create.

The Database window will appear on the screen.

Tables

Tables are the fundamental objects in a database. Without any tables, no data can be stored. To create a table you need to follow these steps:

• Create the table object

• Define the fields in the table including their name, data type and description

• Set the properties for each field

• Create appropriate indexes

• Set the primary key

• Save the table

Creating a table in design view

1. From the Database window, double-click on Create table in design view

Or

From the Database window, ensure Tables is selected in the Objects list and then click on the New button, select Design view and click OK.

A new table will appear in design view:

The table design view

There are three main parts to the design view window.

Upper pane For defining fields by name, data type and description (optional).

Field properties (bottom left) For defining specific properties for a field.

Help (bottom right) As you move around the design view window, context-specific help is provided here.

Defining fields

Before creating the fields in a table, the following information about naming, data types and descriptions should be considered:

Field naming rules and conventions

The following conventions should be observed when naming fields:

• Field names are mandatory. They may contain up to 64 characters.

• Names may include embedded (but not leading) spaces and punctuation except full stops, exclamation marks and square brackets.

• You cannot assign the same field name to more than one field in the same table and it is good practice to use a unique field name for each field in the entire database.

• It is not good practice to include spaces in field names. Instead, use an underscore ( _ ) for spaces to improve the readability of field names.

• Minimizing the length of field names conserves resources and saves typing when you refer to the field name in macros, etc.

• Avoid specifying a name for a field that could cause a conflict with a built-in Access function or property names (e.g. ‘name’, ‘date’, etc.).

Data types

You must assign a field data type to each field in a table unless you want to use the text data type that Access assigns by default. All data in a single field must consist of the same data type.

There are ten different data types to choose from:

|Data Type |Description |

|Text |Allows text and numbers to be stored. The size of the field is limited to a maximum of 255 characters. |

|Memo |Allows an unlimited amount of text and numbers to be stored. Usually used for notes, descriptions, etc. |

|Number |Allows only numbers to be stored. Used for fields which contain numbers you may wish to use for calculations. |

|Date/Time |Stores date and time formats. |

|Currency |Inserts the currency sign and decimal point. |

|AutoNumber |Automatically inserts a sequential number. |

|Yes/No |Is used for fields which only have either a Yes or No or a True or False value. |

|OLE Object |Stores pictures, charts, etc. |

|Hyperlink |Stores a hyperlink address which is a path to an object, document, Web page, or other destination. |

|Lookup wizard |Allows you to create a field that looks up data either from a list of values you type in or from another table. See |

| |page 16 for details on using the Lookup Wizard. |

Description

This is optional but can be useful for other users of the database. If you enter a description it will be displayed in the status bar at the lower left of the Access window when you select the field for data entry or editing in the datasheet view of a table or the form view of a form.

Creating new fields

1. In the first row of the Field Name column, type the name of your first field, following the naming conventions listed above.

2. Press the Tab key to take you to the Data Type field.

3. Click on the drop-down arrow that appears in the box to display a list of data types (see below) and select the appropriate one.

4. Press Tab to take you to the Description field. Type in a short message describing the current field.

5. Press the Tab key to take you to the next row and back to the Field Name column.

6. Continue as above until you have entered all your fields as shown below.

Modifying table design

It is possible to modify the design of your table and fields at any time by going to design view and making the required changes. However, any changes made after data has been added to the table may cause loss of data. Changes to field names, data types and properties may also impact on other objects which are linked to or based on the table.

Adding and removing fields

You can modify the structure of your table by adding and removing fields in design view. Note, however, that using the cut, paste or delete functions will remove the field and any data in that field.

Selecting fields

To select a field, click on the row selector to the left of the field name. To select multiple fields, click on the row selector and drag the mouse down the selector symbols to select additional fields. The screen capture below shows a table with two fields selected.

[pic]

Deleting fields

1. Select the field or fields to be deleted using the row selector.

2. Press the Delete key on the keyboard, select Delete from the Edit menu or right-click on the row selector and choose Delete Rows.

Inserting fields

1. Select the field below which you wish to insert a new row.

To insert multiple rows, select the required number of rows using the row selector.

2. From the Insert menu select Rows or right-click on the row selector and choose Insert Rows.

Moving fields

1. Select the field or fields to be moved.

2. Click on the selected field(s) and drag the mouse to the new location for the field(s). As you drag, a bold black line will appear indicating where the fields will be moved to if you release the mouse button.

3. Release the mouse button in the appropriate location.

Helpful hint: Don’t use cut and paste to move fields if you have data in the table as this will delete the data.

Changing data type

1. If your table contains data, make a backup copy of the table before you change data types or field sizes.

2. Open the table in design view.

3. Click the data type column of the field you want to change, click the drop down arrow, and select the new data type.

4. Click the Save button on the toolbar.

Helpful hint:

If the data type conversion would result in lost values, Access displays a message telling you that errors occurred during conversion before it actually saves the changes. Click Cancel to cancel the changes. Click OK to continue and save the changes anyway.

In large tables, changing a Data Types might take a long time. If you want to cancel the conversion process at any point while it is running, press Ctrl+Break, and then click OK.

Changing field size

If you convert a large field size setting to a smaller one in a field that already contains data, you might lose data in the field. For example, if you change the field size setting for a text data type field from 255 to 50, data beyond the 50 characters will be discarded.

If the data in a number data type field doesn't fit in a new field size setting, fractional numbers may be rounded or you might get a null value. For example, if you change from single to integer data type, fractional values will be rounded to the nearest whole number and values greater than 32,767 or less than -32,768 will result in null fields.

The primary key

A primary key is a single field or combination of fields in a table that uniquely identifies each record in that table. No two records can have the same value in the primary key field or combination of fields. It is advisable to create the primary key before entering data in a table because Access will prevent duplicate values being create in the designated field.

It is particularly important to set a primary key when creating relationships between tables as Access uses it to link tables together.

Helpful hint: Records are displayed in primary key order by default.

Setting a primary key

1. Click on the appropriate field name and click the Primary key button [pic] on the toolbar.

2. The primary key symbol will appear on the button at the left of the table on the appropriate row (see the screen capture on previous page).

Setting a multiple field primary key

With a multiple-field primary Key, the combination of the contents of all fields included in the key must be unique. For example, for a primary key based on the CourseID and CourseDate fields, the same CourseID and CourseDate combination cannot appear in more than one record.

1. Click and drag across the row selector (see screen capture on previous page) of the appropriate fields.

2. Click on the Primary Key button [pic] on the toolbar.

Indexes

Indexing a field speeds up data access when running queries, sorting, and grouping. If you regularly sort or search on certain fields it is a good idea to create an index for this field. However, indexes can slow down some processing activities such as adding or deleting records as the Indexes for many fields need to be updated while performing these operations. For this reason, indexing should be limited to a few important fields.

Indexing can also be used to prevent duplicate entries in a field.

The options in the Indexed text box are:

No (Default) The field is not indexed.

Yes (Duplicates OK) The field is indexed with duplicates allowed.

Yes (No Duplicates) The field is indexed with no duplicates allowed.

If the primary key for a table is a single field it is automatically set to Yes (No Duplicates).

Helpful hint: You can't index Memo and OLE Object fields.

Creating a multiple-field index

A multiple-field index can be created when you wish to be able to sort or filter records by more than one field and where one or more fields may contain the same value and you need an additional field or fields to enable you to distinguish between records.

A good example of this might be a two-field index using the first name and surname fields – records could then be sorted quickly by last name and then by surname in a multiple sort.

1. From the View menu select Indexes or click on the Indexes button on the Standard toolbar. [pic]

A list of all current indexes (single and multiple field) will be displayed (see below).

2. Type a name for the index in the Index Name column of the next blank row. (It is advisable to choose a name that includes the names of the fields to be indexed or a similarly descriptive name.

3. In the Field Name column of the same row, choose the name of one of the fields you wish to include in the index.

4. In the next row, leave the Index Name column blank and in the Field Name column choose another field to include in the index from the drop-down list.

5. Repeat this last step (above) until you have included all the fields you want.

The screen capture above shows indexes for a table with the following indexes:

Dept A single-field index on the Dept field

PrimaryKey A single-field index on the ID field that was automatically created by setting the primary key to be the ID field.

Names A two-field index on the Last_Name and First_Name fields.

Helpful hint:

You can use the Indexes window shown above to view and edit all Indexes in a table, both single and multiple field.

Saving your table

1. From the File menu, select Save or click the Save button on the Standard toolbar. [pic]

2. Type in a name for your table. This can be up to 25 characters long and can include spaces, although these are not advisable. It is a good idea to use the naming convention tbl_Tablename, replacing Tablename with the name of the table.

Entering data in your table

To enter data into your table, switch to datasheet view and enter data as normal. For more details about data entry and editing, see Document IS-001 Using databases in Access.

Switching to datasheet view

From the View menu select Datasheet or click on the Datasheet button on the toolbar. [pic]

Field properties

Each field has property settings that you can change to alter the way the field looks and behaves. Field properties are optional and can be entered after a field has been created. There are different properties available for different data types.

The properties for a particular field are displayed at the bottom left of the design view window when you click in the field and the General tab is selected as shown on the right.

Field size

You can use the Field Size property to set the maximum size of data that can be stored in a field set to text or number data type.

Number field size

The default setting for a number field is Long Integer. The Field Size property settings and their values are related in the following way:

|Setting |Description |Decimal precision |Storage Size |

|Byte |Stores numbers from 0 to 255 (no fractions). |None |1 byte |

|Integer |Stores numbers from -32,768 to 32,767 (no fractions). |None |2 bytes |

|Long Integer |(Default) Stores numbers from |None |4 bytes |

| |-2,147,483,648 to 2,147,483,647 (no fractions). | | |

|Single |Stores numbers from -3.402823E38 to 3.402823E38. |7 |4 bytes |

|Double |Stores numbers from -1.79769313486232E308 to 1.79769313486232E308. |15 |8 bytes |

Text field size

The default setting for a text field is 50. You can reset the Field Size for anything between 0 and 255.

Helpful hint:

Use the smallest possible Field Size property setting for number fields because smaller data sizes can be processed faster and require less memory.

You can't use Undo to undo changes to a table's design after saving it in Table design view.

Format

The Format options also vary depending on the Data Type set. The format of a field only affects the way the data is displayed: Access will still store the data in the same way.

Formatting dates and numbers

Click in the Format box and select an appropriate format from the drop-down list:

Formatting text

There is no drop-down list for fields with a data type of Text. However, you can type in formatting options.

For example:

Less than []: converts all text in the field to uppercase

Decimal places

For Number or Currency fields, you can specify the number of decimal places allowed for a field between 0 and 15.

Input masks

An input mask is used to provide some control over what values can be entered. They are primarily used in Text and Date/Time fields, but can also be used in Number or Currency fields. An Input Mask is usually used when data needs to be entered in a fixed pattern of numbers, letters, spaces etc., such as a National Insurance number but is unsuitable where the pattern may vary (e.g. a postcode or telephone number).

When you click in the input mask area, a build button [pic] appears on the right. Clicking this button will enable the Input Mask wizard, which will help you select an appropriate input mask for your field.

To find out more about how to create input masks, click in the Input Mask area and press F1.

Caption

This is an optional label that can be used to replace the field name in forms and reports. The caption will also appear as the column heading in datasheet view in place of the field name.

Default value

If you type in a default value, this is automatically entered in the field for each new record. When new records are added, the default value can be kept or changed.

Helpful hint:

You can enter =Date() in the Default Value box of a date field to enter the current date automatically when data is entered.

Validation rules

Validation rules will automatically check for any particular words or phrases that are being entered into a field. If the match is not exact to that specified in the validation rule, then the information will not be accepted into the field and an error message will appear explaining why the data could not be accepted.

When you add words in the validation rule area that you want accepted, separate them with OR. For example: Mr OR Mrs OR Ms OR Miss OR Dr.

Helpful hint:

You can use symbols and words such as , =, OR and AND or refer to other field names when creating validation rules. Validation rules can also be set for tables and records, as well as fields.

Validation text

This property is only relevant if you have set a validation rule. In the Validation Text area, type in the message you want to appear for the user if the validation rule is broken.

Required

This can be set to Yes or No to specify whether or not the field is compulsory (i.e. whether a value must be entered in the field).

Importing Data

If you have existing data in an acceptable form, you can import it into Access using the Import wizard. This might be a text file, a spreadsheet or another database. For example, data can be imported from an Excel spreadsheet to create a new table in your database or to append data to an existing table – the columns become the fields and the rows become records in the new table.

Importing data from Excel

Preparing to import from Excel

The spreadsheet data should be organised in the following way:

• The first row of the worksheet should contain the column headings that will become your field names. You should therefore ensure that these observe Access field naming conventions. See page 5 for more information.

• There should be no blank rows in the dataset, particularly between the column headings and the first row of data.

• There should be no other data on the worksheet apart from the dataset.

• To append records to an existing table, the field names and data types must match the fields in the table to which you wish to append the record. Appending records is not covered in this workbook.

Importing data to create a new table

1. Go to the Database Window of the database into which you want to import.

2. From the File menu, point to Get External Data, and then select Import. The Import dialog box will appear.

3. In the Files of type box, select Microsoft Excel.

4. Use the Look in box and files and folders list to locate the folder that contains the file with the data you want to import.

5. Select the file you want to use and click Import. The Import Spreadsheet Wizard will appear.

6. The wizard will automatically select the first worksheet to import. If necessary, select a different worksheet.

7. Click Next.

8. If not already ticked, tick the First Row Contains Column Headings to enable Access to set up the field names for your table and click Next.

9. Select the option to store the data In a New Table and click Next.

10. You now have the option to add indexes to fields, change data types or to choose not to import a specific field by selecting each field in turn and changing the Field Options above. However, these can be changed once the table has been created.

11. Click Next.

12. You can now allow Access to add a primary key field with an AutoNumber data type or to use an existing field. Whether you have a suitable field will depend on the specific data you are using.

13. Click Next.

14. The final step is to name your table. Access will offer the name of the Excel worksheet by default, but this can be changed if you wish.

15. Click Finish. The table will now be created.

Lookup fields

A lookup field is a field which ‘looks up’ its values from a particular source. This might be a set of values from a field in another table or query (a record source) or a set of values which you type in (a value list). When entering data in datasheet view, the values appear in a drop-down field known as a combo box.

Creating a lookup field from a record source in another table automatically creates a relationship between the two tables in a similar way to creating relationships between tables in the Relationship Window. However, no referential integrity rules will be applied.

Advantages of using a lookup

Lookups make data-entry easier and reduce the chances of errors caused by mistyping. They can also be used to restrict the data that can be entered in a field by limiting data-entry to the values on the drop-down list. Using a table or query as a lookup is particularly useful, because when you change the values in the table upon which the Lookup is based, the values in the drop-down list in the lookup field are also updated.

Lookup tables

A lookup table is a table which is created with the specific purpose of being used to create values for a drop-down list in another table. A lookup table will have a One-to-Many relationship with the related table. For example, a lookup table might contain a list of wards which will appear as a drop-down list in the patients table.

A lookup table may also contain additional fields with further information about the records. For example, the ward lookup table mentioned above might also contain information about number of beds, location etc. of each ward.

More than one field can be added to the drop-down list that displays for the user in the Datasheet view of the table. However, only the value in the linked field from the lookup table will actually be stored in the related table (e.g. the field containing the ward name). This field should be unique and will usually be the primary key field in the lookup table.

Using the lookup wizard

The easiest way to create a lookup field is to use the Lookup wizard. This appears as a Data Type option in Table Design view but isn’t a data type as such. Choosing this option invokes the lookup wizard which guides you through the process of creating your Lookup field.

In the Data Type column for the relevant field, select Lookup Wizard. The first page of the Lookup wizard will appear:

Creating your own value list

1. Select I will type in the values that I want and click Next.

The following will appear:

[pic]

2. Enter the number of columns you want in the list in the Number of columns box.

3. Click in the first row of the Col 1 column and type in your first value.

4. Press the Tab key to move down to the next row and type in your next value.

5. Continue until all your values have been entered and click the Next button.

6. Ensure the correct field name is displayed for your lookup column and then click Finish.

The lookup field will be created as shown in Datasheet view on the right.

Helpful hint: The Data Type for the field is set to Text by default.

Creating a lookup based on another table

1. Invoke the Lookup Wizard as described previously.

2. Select I want the lookup column to look up the values in a table or query and click Next.

3. Select the table you wish to use as your lookup table and click Next.

4. Select the fields you wish to include in your drop-down list by clicking on the field and then clicking on the [pic] button.

Helpful hint:

The first field you select should always be the field you wish to use to link to the related table (usually the primary key field). You can also add additional fields to be displayed in the drop-down list.

5. Click on Next.

The data in the field(s) you selected will be displayed in columns as shown overleaf. You can re-size the columns as you would in a table in Datasheet view. Bear in mind that additional records added to the lookup table will need to fit the columns.

6. The Hide key column check box appears above the columns if the first column is the primary key field in your lookup table.

Tick this column to hide the primary key value in the drop-down list. If, for example, the primary key is an AutoNumber and therefore not meaningful when entering data, you may wish to hide it. Only the other selected fields will appear in the drop-down list in Datasheet view.

7. Click on Next.

8. Ensure the correct field name is displayed for your lookup column and then click Finish. The lookup field will be created.

Helpful hint:

The data type for the field is set to the same data type as the linked field from the lookup table by default. The values will appear in the drop-down list sorted in the order of the linked field by default.

Editing the lookup properties of a field

Once a lookup field has been created you may want to make changes to it. For example, you may wish to change the width of the columns in the drop-down list or restrict the user to entering only values which appear on the list.

1. In Table Design view, click on the relevant lookup field to select it.

2. Click on the Lookup tab in the Field Properties pane:

[pic]

Changing column widths

The Column Widths box lists the widths of the columns displayed in the combo box drop-down list separated by semi-colons. If there is a primary key column and it is hidden, this is displayed first with a size of zero.

• To display the hidden column:

Click in the box and change the value to something appropriate greater than zero.

• To increase or decrease a column width:

Click in the box and modify the size of the appropriate column.

Note that the combined width of all the columns is dictated by the List Width property so you may need to alter this as well.

Modifying the row source

If the Row Source Type is Value List, the Row Source Field Property box will list the values you typed in separated by semicolons. You can modify the value list by adding new values (always followed by semi-colons) or editing existing ones.

If the Row Source Type is Table/Query the Row Source Field Property box will contain the code that Access uses to define which fields are included in the lookup field.

To modify the row source:

1. Click in the Row Source box.

The Build button [pic] will appear.

2. Click on the Build button.

The query that the lookup field is based on will appear.

3. Modify the query as required.*

4. Click on the Close button to close the query

5. When prompted, click Yes to save the changes you have made.

*For example, to change the sort order of the drop-down list:

1. Click in the Sort row for the relevant field.

2. Select Ascending or Descending from the drop-down list.

Limit to list

This indicates whether the user can enter their own values in the lookup field or whether they are limited to those on the drop-down list. This is set to Yes by default if the bound column of the lookup field is a primary key and No by default if the bound column is not a primary key or if the lookup is based on a value list.

For example, a lookup field in tbl_nurse which looks up Ward information from tbl_ward would, by default, have the Limit to List property set to Yes if the bound column is WardNumber (the primary key in tbl_ward).

You can change the Limit to List property if required.

Helpful hint: You cannot change the Limit To List property to Yes if the bound column is hidden.

Relationships

If your database contains more than one table, it is necessary to define how the tables are related. Tables can be related by linking a field in each table. The links or relationships are used to define how the tables interact with each other when being searched.

One-to-many relationships

The most common relationship is a One-to-Many relationship. A record in one table is linked to one or more records in another table. This is done by storing a value from a particular field (usually the Primary Key field) of the record on the ‘One’ side of this relationship in a field in the related record on the ‘Many’ side. This field is known as a Foreign Key. A foreign key will not be unique in a One-to-Many relationship. The table on the ‘One’ side of the relationship is known as the Primary Table.

Scenario

In a hospital database, one ward may have many nurses but each nurse is assigned to only one ward. In this scenario, information relating to wards is stored in one table with one record for each ward. The primary key for each ward might be WardNumber. Information about nurses is stored in a separate table. In addition to fields containing nurse data, such as their name and address, job title, etc., the nurse table also contains a Foreign Key field which stores the ward number value for the ward for that particular Nurse.

One-to-one relationships

This would be created when data relating to one subject is split across two different tables but each record in the first table would only link to one record in another. This is done where particular fields are only required for some of the records in a table.

If the tables are linked using the Primary Key value from the first table, this value could be stored in a field in the second table which is also the primary key for that table because there will be no duplication of values. One-to-One relationships are created in the same way as One-to-Many relationships (see Creating relationships on page 21).

Scenario

Some (but not all) patients are included in a particular research study. Rather than creating additional fields in the primary table which would be empty for most patients, an additional table is created to store records for just those patients in the study. Both tables might have a field for the patient’s hospital number (the primary key) and these would be linked to form the relationship between them.

Many-to-many relationships

In this situation a record in one table needs to be linked to several records in another table and each of these records might link to several records in the first table. In order to create these relationships, a table known as a Junction Table is created which links the two Primary Tables. In addition to any other fields the junction table may contain it will have two Foreign Key fields to display the data from the primary key fields of the tables it is linking. Each of the primary tables is on the ‘One’ side of a One-to-many relationship with the junction table.

Scenario

Over time, one Patient might be admitted to many wards and each ward might admit many patients. The same patients might be admitted to the same ward at different times. These could be linked by having an additional table for admissions in which each record would include a field for the PatientID from the patient table and a field for the WardNumber field from the ward table as well as the date of that particular admission and any other relevant information relating to it. The diagram below illustrates this relationship:

Creating relationships

Preparing to create relationships

In order to create a relationship between two tables the following rules must be observed:

• Both the tables must be in the same database.

• Both fields you wish to link must have the same data type (AutoNumber fields are regarded as Number fields in this regard).

• If the matching fields are number fields, they must also have the same Field Size Property settings.

Creating relationships between tables

1. Close all tables which are to be linked. You cannot create or modify relationships between open tables.

2. From the Tools menu, select Relationships or click on the Relationships button [pic] on the toolbar. The Show Table dialog box should be displayed as shown on the right.

3. If not, from the Relationships menu select Show Table [pic]. The Show Table dialog box will appear:

4. In the Show Tables dialog box, ensure the Tables tab is selected.

5. Select the tables to be related and click on Add to add them to the Relationships window.

6. Click on Close to close the Show Table dialog box.

7. The Relationship window displays the tables to be linked. The Primary Keys are show in Bold.

8. To create the link, click and drag between the two common fields in the table. The Relationship dialog box will be displayed as shown on the right.

It is necessary to define the type of relationship for each link:

1. Click Enforce Referential Integrity if required (see Referential integrity on page 22 for further details).

2. Click on Create.

3. A line now joins the fields and the two tables are linked between the common fields.

4. Click the Save button on the toolbar.

5. Close the Relationships window to return to the Database Window.

Viewing or editing existing relationships

1. From the Database Window, select Relationships from the Tools menu, or click on the Relationships button [pic] on the toolbar.

2. Double click the relationship line (see above) to display the Relationships dialog box. A thick line indicates referential integrity (see Referential integrity on page 22).

1 – indicates the one side of a one-to-many relationship.

∞ – indicates the many side on a one-to-many relationship.

Helpful Hint:

You can modify the structure of a table from the Relationships window by right-clicking on the table and selecting Table Design from the menu.

Removing relationships between tables

1. Close all tables which are to be linked.

2. From the Tools menu select Relationships.

3. Right-click on the line linking the two tables you want to unlink.

4. Select Delete from the shortcut menu.

5. Confirm by clicking on Yes.

Referential integrity

Referential Integrity is used to ensure that records in related tables are valid and that you don’t accidentally delete or change related data.

In order to be able to enforce referential integrity between two tables, the matching field from the primary table must be a primary key field or have a unique index (no duplicates).

If the Enforce Referential Integrity option is selected the following rules will apply when creating or editing records:

• When entering a record in the related table, the value on which the link is based must already exist in the primary table.

For example, if you are adding a new patient record to the patient table (which is related to the ward table), the patient can only be allocated a ward name if it already exists in the ward table.

• You cannot delete a record from a primary table if matching records still exist in the related table.

For example, you could not delete a ward record if a patient record still exists for that ward.

• You cannot change the value in the primary table linking field if a matching record exists in the secondary table.

For example, you could not change the value in the Ward_Name field of the ward table after you have already created a patient record and entered that Ward_Name in the foreign key field for the patient.

If you try to break any of these rules, Access will display an error message.

Enforcing referential integrity

1. From the Tools menu select Relationships.

2. Double-click on the Relationship line to display the Relationships dialog box.

3. Tick the Enforce Referential Integrity check box.

4. The following options are also available:

|Cascade Update Related |Select this option to ensure that when you change the primary key value of a primary record, the related |

|fields |records are also changed. |

|Cascade Delete Related |Select this option to ensure that when you delete a record in a primary table, that the related records are|

|Records |also deleted. |

Forms

Forms don’t actually contain data, they merely provide a user-friendly interface from which to enter data into tables. Forms can be created in a number of different layouts but they usually allow you to see all the fields for one record on one sheet. For this reason, they are particular useful for entering data into tables with many fields where entering the data would require scrolling across a wide Table.

Creating a simple AutoForm

1. Click on Form in the Object List in the Database Window.

2. Click on the New button.

3. The New Form Dialog box will be displayed (see opposite):

4. Select AutoForm: Columnar from the list of options.

5. Select the Table required from the drop-down list below it.

6. Click OK.

7. From the File menu select Save.

8. Save the form with an appropriate name.

The form will now appear in the list of forms in the Database Window.

Helpful Hint:

For more information of creating forms, please see the 'Creating Forms and Reports in Access' course.

Reports

A report is an effective way to present your data in a printed format because you have more control over the appearance of everything on the report. You can use reports to create mailing labels, group records into categories, calculate totals, etc. You can also include graphs, charts and pictures.

Create a report with the Report Wizard

1. From the Database Window, select Reports from the Objects List and double-click on Create Report by Using Wizard

Or

Click on the New button in the Database Window and from the New Report dialog box, select Report Wizard and click OK:

[pic]

2. Select the Table or Query you want to base your report from the Tables/Queries drop-down list. The fields from the table or query will appear in the Available Fields box.

3. In the Available Fields box, select the fields that you want to include in your Report and click on the arrow button [pic] to move the selected fields to the Selected Fields box on the right.

4. Click Next:

5. You can apply Grouping levels to your report if your wish. Try the different options. Click Next when you are happy with the layout.

[pic]

6. You have the option to sort by up to four fields, in either ascending or descending order. Select the fields you want to sort on, if necessary, then click Next.

7. Choose a layout for your Report and select an appropriate page orientation. Click Next.

8. Select a style for your Report. Click Next.

9. In the final Report Wizard box, type in a title for your Report and click Finish.

Previewing a report

Once a report is created, it is displayed in a Print Preview screen.

To view an existing report, select Report in the Object List in the Database Window, select the report you want to preview and click Preview. The report is displayed in Print Preview view.

When you position the mouse pointer over the page it becomes a magnifying glass. Click once to zoom in, click again to zoom out from the page.

Use the navigation bar at the bottom on the screen to view other pages.

Printing a report

Once a report has been created, it can be printed at any time. Any new information added to the table will be automatically updated in the report.

Printing a report

1. Preview the report as explained in Previewing a report on page 27.

2. From the File menu, select Page Setup.

3. Set your margins on the Margins tab page.

4. Click on the Page tab and select the page orientation and paper size.

5. Click OK.

6. From the File menu select Print.

7. In the Print Range, select which pages you want to print, or select All.

8. In the Copies box, select the number of copies you want to print.

9. Click OK to print your report.

Getting Help with Access

As well as the context-specific help which you can call upon any time you are working with Access (press the F1 key for help with the feature you are working on), there are a number of other resources available to you.

Learning more

Information Systems publishes documents and runs courses to accompany this one as detailed below:

|Overview of databases |This 1.5hr course is for those new to databases and is designed to explain the difference |

| |between a database and a spreadsheet and to illustrate how you can decide which is most |

| |appropriate for your work. |

|Using Access 2003 databases |This 3hr course will provide an introduction to the main features of Access, and enables you to |

| |enter data and extract simple information from an existing database. |

|Creating forms and reports in Access |This 3hr course teaches users of Access how to design and modify their own forms for data entry |

|2003 |and how to create and modify simple reports. |

|Creating queries in Access 2003 |This 3hr course is aimed at existing users of Access who will learn how to use a variety of |

| |criteria and parameters in queries to extract specific data from one or more tables. They will |

| |also learn how to perform calculations and group data in queries. |

Open Learning Centre

Database development and advanced Access support – This training is delivered in the Open Learning Centre and is intended for those who wish to obtain training, on an individual or small group basis, in specific features in Access or for those who need help designing a database. Please let us know your previous levels of experience, and what areas you would like to cover, when arranging to attend. You will need to book a session in advance at ucl.ac.uk/is/olc/bookspecial.htm and sessions will last for an hour or possibly longer, depending on availability. See the OLC Web pages for details of times and how to book at ucl.ac.uk/is/olc

Online learning

There is also a comprehensive range of online training in Access 2003 available via TheLearningZone at: ucl.ac.uk/elearning

-----------------------

Build button

OJ[?]PJQJ[?]^J[?]hazCJOJ[?]PJQJ[?]^J[?]hý.õCJOJ[?]PJQJ[?]^J[?]-hazCJ8OJ[?]PJQJ[?]^J[?]aJ8$hˆQ©hazCJ0OJ[?]PJQJ[?]^J[?]aJ0$hˆQ©hazCJ ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download