Instructor-led training - access introduction course

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Access Introduction Training CourseAccess Introduction Training Course

Microsoft database course

Face to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.

Designed for Access 365

NB. some people find this page by searching for Microsift Access Training, which we've found is a common typing mistake.

Learning & Development Resources

Infographics

Free manuals

We are providing a range of our course manuals free of charge.

Why not share this resource with your friends and colleagues?

Training manual sample

Below are some extracts from our Access training manuals.

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.