Creating Tables and Relationships

[Pages:29]Access 2007

Creating Databases Fundamentals

Contents

Database Design

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

4

Creating new fields

5

Modifying table design

6

The primary key

7

Indexes

8

Saving your table

9

Field properties

9

Calculated Field Properties (Access 2010 only)

13

Importing Data ............................................................................................................................. 14

Importing data from Excel

14

Lookup fields ................................................................................................................................ 16

Modifying the Data Design of a Table ........................................................................................20

Relationships ................................................................................................................................22

Creating relationships

23

Viewing or editing existing relationships

24

Referential integrity

24

Viewing Sub Datasheets

26

.

Page 2 of 29

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 calculated data (data that is the result of an 'expression') as this is effectively duplicated 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 contain 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)

Page 1 of 29

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.

Page 2 of 29

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. On the File tab select New. 2. Click on Blank database. 3. In the File Name box, type a name for your database. 4. Click on the browse button to the right of the File name box to browse for a location for your

database. 5. Click on Create.

A new database will be created with a new default Table. 6. Click on Design View to start working with this Table.

Page 3 of 29

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. On the Create tab, click on Table Design. A new table will appear in design view:

The table design view

There are three main parts to the design view window.

Upper pane

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, contextspecific help is provided here.

Field properties

Help

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.

Page 4 of 29

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.

Data Type Text

Memo

Number

Date/Time Currency AutoNumber

Yes/No

OLE Object Hyperlink

Attachment Calculated

Lookup wizard

Description

Allows text and numbers to be stored. The default length of this field is 50 and is limited to a maximum of 255 characters.

Memo fields are usually used for notes, descriptions, etc and can store up to 2 gigabytes of which you can display 65,535 characters in Forms and Reports. Font formatting can also be applied to text in Memo fields.

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

Stores date and time formats.

Inserts the currency sign and decimal point.

Automatically inserts a sequential or random number. You cannot enter data into this field ? it is automatically filled in as you add records to your table. This data type can be used to create unique numbers to identify records.

Is a logical field used when you only have either a Yes or No or a True or False value.

Stores pictures, charts, etc. Can be up to 1 gigabyte.

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

Allows you to attach specific files to an individual record in a table.

Allows you to add a calculation based on values from other fields and/or other values. It is also possible to use functions.

This is not really a data type as such but starts up a wizard which 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.

Page 5 of 29

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.

Primary key symbol Row selector

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.

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 Rows from the Design tab or right-click on

the row selector and choose Delete Rows.

Inserting fields

1. Click in the field above which you wish to insert a new row. To insert multiple rows, select the required number of rows using the row selector.

2. Select Insert Rows from the Design tab or right click and choose Insert Rows.

Moving fields

1. Select the field or fields to be moved.

Page 6 of 29

 ................
................

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

Google Online Preview   Download