Microsoft Access Introduction


Hands-On Course 522 – 1 Day

Course Requirements

Learners must be confident using a mouse and keyboard.

What You Will Learn

In particular you will be able to:

  • Get working with Microsoft Excel efficiently and effectively
  • Optimize your worksheets by creating and using formulas
  • Modify worksheets to manipulate and edit data
  • Format and style worksheets to get more impact from data
  • Produce printed workbooks
About This Course: This course will provide you with the foundational Excel knowledge and skills necessary for business today. As you apply the robust functionality that’s part of Excel to your organization’s raw data, you will be able to gain a level of insight into that data that would have been almost impossible years ago. Excel helps you organize, calculate, analyze, revise, amend, and present your data in ways that will help you and the decision makers in your organization steer forward in the right direction.

Course Description

Getting Started with Microsoft Access (2007, 2010, 2013 2016)

Working with Table Data

You are getting used to the Microsoft® Office Access® 2013 user interface, and you can now easily create a simple database. By now your data has been moved into Access tables, and you can take the advantages of Access powerful tool to help you manage the content within a product database, by searching, sorting and filtering data .
In this lesson, you will learn to add, delete, and update data in a table. Sorting and filtering a table to show only records that match certain criteria and creating lookup fields is a necessary step toward developing more advanced table relationships.

    • Modify Table Data

As you are progressing with creating the basic table structure within a database, you also need to gain knowledege that will help you to keep data in your tables up to date. This topic has been designed for this. Here you will modify your data in a table by adding, deleting, and amending data to keep it current.

    • Sort and Filter Records

If you need need to quickly focus on a particular group of records or view the products for a particular vendor,then you will need to learn to sort or filter a table to show only records that match certain criteria. As you work with a database, you will find that this can be a very repetitive task,but with Access features you will be able to quickly accomplish it.

    • Create Lookups

It is possible to create a single-table databases in Access, but things get more interesting when your data is structured in multiple tables, and when you need to establish relationships between them. To do so, you will need to create lookup fields to establish a simple relationship between two tables. Suppose,that you have a supplier table packed with information such as the supplier ID, contact information, product list and your inventory table might have information of the product code and the supplier ID of the company from which you buy the product. As both tables include a supplier ID, you could use that field to establish a relationship between the two tables. Once you establish a relationship between these tables, you can work with the data in those tables simultaneously.

Querying a Database

You have come so far on your journey that you can use all powerful features addressed in the previous topics. You can create a database, fill tables, and make updates to database content. You learnt how to search, sort, and filter datasheets, and how to configure fields to search for data in other tables. But..these powerful features can be quite limited and easily overwritten.
In this lesson, you will find out how to use queries to create instructions that can be reused and which will perform complex operations on dynamic table data including joining data from multiple tables, sorting and filtering,and performing calculations.

    • Join Data from Different Tables in a Query

Joining data from multiple tables by using a query is very important task when you want to organize data within multiple related tables. In this topic you will become familiar with the different types of queries. You will create query by using the Query Wizard to help you to produce a view of your data that combines elements from your inventory and supplier tables and their already established relationship. And you will also be shown how to use the query design feature to create a new query in design view. This view will enable you to specify criteria, sorts, and summary data that will be included in the query.

    • Sort and Filter Data in a Query

Access queries can save you from a difficult and time consuming taks of sorting and filtering displayed results manually.
You need to know which query criteria to provide to specify which records you want to be included in a query result. Query criteria can be written in a mathematical or logical expression to produce a result, and you can include one or more of them. In addition to this, there are some various wildcard characters which can be used in conjunction with query criteria. They match records based on patterns and can be substituted for literal values.

    • Perform Calculations in a Query

When customers buy products from your store, you would like to update the inventory count and determine whether the inventory is low enough to resupply of stock. By adding a calculated field that checks the difference between inventory and a reorder level, you can trigger a new order when inventory dips below the reorder level. Access can calculate values based on one or more fields and display the result in the query output.

Creating Advanced Queries

Queries you have used so far proved to be beneficial to manage complex data. You learned how to use saved queries in order to perform the same operations on your data each time you run them. However, when your needs change, your query will need to change too. In this lesson, you will create flexible queries that you can tailor to your needs. These queries will perform an action, they will delete or modify matching records and you will want to know how to control such a powerful tool which can make mass updates to a database.

    • Create Parameter Queries

In this topic, you will create parameter queries.They are ssome useful order. Another type of help you get from queries is filtering data. You will learn to filter your data by using comparison, wildcarts and date range functions. These are some of the tasks a query can perform quickly and efficiently for you.

    • Perform Calculations in a Query

When customers buy products from your store, you would like to update the inventory count and determine whether the inventory is low enough to resupply of stock. By adding a calculated field that checks the difference between inventory and a reorder level, you can trigger a new order when inventory dips below the reorder level. Access can calculate values based on one or more fields and display the result in the query output.

Creating Advanced Queries

Queries you have used so far proved to be beneficial to manage complex data. You learned how to use saved queries in order to perform the same operations on your data each time you run them. However, when your needs change, your query will need to change too. In this lesson, you will create flexible queries that you can tailor to your needs. These queries will perform an action, they will delete or modify matching records and you will want to know how to control such a powerful tool which can make mass updates to a database.

    • Create Parameter Queries

In this topic, you will create parameter queries.They are simple but useful when you need to retrieve records based on input criteria. In some cases, you might need to find all records that contain information related to particular suppliers. By learning how to create just one query that will aim at users of the products of the particular suppliers, you will save your time and effort.

    • Create Action Queries

When records change on a small scale, updating them is not the problem. You are more concerned with mass update to multiple records.
Let’s say that you need to apply a price increase to all products within a certain product line and you do not know how to do it? The answer can be found in this topic and is called an action query.
Action Query is oriented toward updating data automatically. This can be a very useful tool for making data modifications on a large scale.

    • Create Unmatched and Duplicate Queries

Your database is getting larger and larger. Sometimes, you might have two different product codes in separate records that represent the same product. You are aware that some records are no longer used or needed, and you might even find duplicated records. This topic will focus on database management issues – especially on how to keep the database clean and efficient. Access comes to your help with two types of action queries called unmatched and duplicate and they will find and correct problems automatically.

    • Summarize Data

When you summarize results after query,the display data might provide insights that are not readily apparent through a normal query.
To be able to reveal patterns in categories of data,you will need to become familiar with a new query known as a Crosstab query.
The name “crosstab” comes from “cross tabulation,” and refers to the way values are distributed across the table. This query displays the values of a field as a row heading, column heading, or in the cell intersection of the row and column.

Generating Reports

You have come a long way since you first started learning about Access. You have created a database and learnt how to manage its content using Access® 2013 tools. You might be aware that gathering and organizing data leads to producing a report as the end result. In this lesson, you will create a report, add controls and change the report’s layout, apply themes, and prepare a report for print.

    • Create a Report

In order to create a report from useful information that your database has accumulated, you need to invest some time and become familiar with few report creating tools that will guide you to produce reports that you can use for analysis. It is better to learn them all and find out which one of them would be of the best use for your circumstances.

    • Add Controls to a Report

Even though some tools that generate a report are often able to create most of controls and layout for you,sometimes you will need to add controls manually.
So,if you are working toward producing a useful report layout for your orders,you will need to adjust the layout of controls to better use the available space.
You will also need to understand how to apply the report design view which provides bands for various sections of the report, as well as the property sheet within tabs to make them easier to find format, data, event and others.

    • Enhance the Appearance of a Report

Your report is almost completed, almost ready for printing. Few more details, some final touches are neded like changing the color theme and adding a graphic elements. Access provides various options to enhance the appearance of your report. This lesson is designed to enable you to apply color themes and font styles, and add graphics to a report or other database elements.

    • Prepare a Report for Print

Before sending your report to a printer, it would be wise to adjust page setup and layout options in order to avoid problems you could experience when the report contains too much information to fit within a page, or page margins overlap. Preparation is very important as it will cut down time, effort and paper waste.

Customizing the Access Environment

In this lesson you will explore options of customizing the Access Environment to meet your specific needs. You will look at possibilities available in the Options dialog box, as well as configuration options, and find out how they can best fit with your requirements.

    • The Access Options Dialog Box

By using Access Options Dialog Box, you can customize and configure your installation of Access,database,language,add-ins,ribbon and other categories you need to customize your database behavior. This topic will convey skills that will enable you to be in control and use Access to manage your data.
Congratulations! You have completed your introduction to Access journey and have successfully created an Access database, including tables, forms, queries, and reports. You are ready to face large amounts of data, with no fear or confusion, to organize, manage, and analyze with full confidence.