Access Introduction Training Course

Concepts

What is a database and why do we need to use it?

When to use Access versus Excel?

There are a number of considerations when choosing between storing your data in Access versus Excel.

Size of data

Excel now allows for over a million rows of data. Access is not limited by the number of records but is by the overall file size which cannot exceed 2Gbytes.

Linking Tables

With Access you can create a database with several linked tables called a ‘relational database’.

Excel requires functions that lookup the data values or using add ins to build relationships.

Queries

Access allow tables to be interrogated or queried and the results saved for later use. With Excel you can filter list but not so easily create queries.

Forms

Access allows forms to be created for data entry and display. Forms can be created with Excel but to do so requires macro programming.

Reports

With Access you can create many reports from one table, query or from multiple linked tables. Excel would require linking multiple worksheets.

Terminology

When first using Access you will come across several new object names such as tables, queries, forms, and reports. There is also terminology specifically used when working with databases such as field, records and datasheets. Here is a summary of some terms used:

• Field

A field is a single piece of data such as name, date of birth or telephone number.

• Record

A record is a complete set of fields, for example a client name, company and email address.

• Table

A table is comprised of one or more records. Each table also has a unique name.

• Form

A form is a window or screen that containing fields organized in a logical manner for easy access and manipulation.

• Report
  A report is the formatted result of a database query and contains useful data for decision-making and analysis
  
  
• Query
  A Query searches a subset of data stored in a table. Access queries can be saved, reused and automatically updated.
  
  
• Relationships
  A relationship in Microsoft Access connects information between tables. It helps prevent redundant information and ensures that information is consistent.
  
  
• Primary and foreign keys

The primary key uniquely identifies each record in a table. The table is automatically sorted is ascending primary key order.

A foreign key is a field that matches the primary key column of a different table.

Home ribbon

File tab

Create tab

When designing a new database ensure that you know exactly what you want. Interview all involved to make sure that you know what they expect from the database. Plan ahead and anticipate changes that might be needed to help avoid huge time consuming work on the database in the future.

It helps to start from a piece of paper. Make a list of all the fields you need. Draw the tables and relate them on the drawing. With a drawing like this it is much easier to see an overview of the project.

Designing Tables

When creating a database there are some design practices that help manage the database and improve its performance.

Break fields down to their smallest parts

Use an Employee Code rather than the employee full name if there are related tables.

Remove repeating fields

Rather than have fields such as

Employee_Code       Project_No_1 Project_No_2 Project_No_3 etc

Have one field making the data easy to filter and query.

Employee_Code       Project_Number

E01                         SL-99-01

E01                         SL-99-02

E01                         SL-99-03

E02                         SL-99-01

E02                         SL-99-01

Normalization

This process is called normalization. The following table has been normalized.

Fields such as the Customer and StoreName contain lookups to a Customer table and a Stores table containing unique entries.

Database Relationships

Here is an example of relationships between tables within a relational database.

After transforming tables into smaller tables the Relationships can be identified and created between each table.  For example Product ID in the Line Items table with Product ID in the Items table.

To create relationships select:

 Database Tools > Relationships

 Add the tables you wish to relate and drag your mouse from the field in one table to a ‘foreign’ field of another table.