Overview

Your training and experience using Microsoft® Access® has given you basic database management skills, such as creating tables, designing forms and reports, and building queries. In this course, you will expand your knowledge of relational database design; promote quality input from users; improve database efficiency and promote data integrity; and implement advanced features in tables, queries, forms, and reports. Extending your knowledge of Access will result in a robust, functional database for your users. This course is the second part of a three-course Specialization that covers the skills needed to perform database design and development in Access. This course may be a useful component in your preparation for the Microsoft Access Expert (Microsoft 365 Apps and Office 2019): Exam MO-500 certification exam. This course is designed for students wishing to gain intermediate-level skills or individuals whose job responsibilities include constructing relational databases and developing tables, queries, forms, and reports in Microsoft Access for Microsoft 365. In this course, you will: provide input validation features to promote the entry of quality data into a database; organize a database for efficiency and performance, and to maintain data integrity; improve the usability of Access tables; create advanced queries to join and summarize data; use advanced formatting and controls to improve form presentation; and use advanced formatting and calculated fields to improve reports. This course requires that you have Microsoft Access installed on a Windows PC. The course setup instructions provided in the first module of the course go into more detail about the hardware and software requirements.

Syllabus

Promoting Quality Data Input

You've created a database, and now you're starting to flesh out the user interface. As you plan the forms and controls that will enable users to interact with the database, you must think about more than just presenting data so users can view and interpret it easily and correctly. You must also consider how you will help users enter the required data in the correct format and follow rules of the business the database was designed to serve. Microsoft® Access® provides a number of tools you can use to prevent users from making input mistakes.

Improving Efficiency and Data Integrity

You want a database that is logically designed, communicates its purpose and design clearly to users, and allows for speedy retrieval of data. In this lesson, you will examine the process for determining if your tables are structured correctly, finding ways to alleviate redundancy, and making improvements to an existing design in Microsoft® Access®.

Improving Table Usability

Datasheets are efficient for repetitive data entry tasks in tables, and in some situations may be more efficient or easier to work with than forms. While every table can be viewed in a datasheet, sometimes you need to work with data from an associated table.

Creating Advanced Queries

In a well-designed relational database, it is often necessary to join data from multiple tables in order to view the results you're looking for. Queries enable you to do this, but to get the results you need, you must understand your options for joining data. Different types of joins are possible, and you can even use one query to provide the source content for another query. Crosstab queries enable you to reveal patterns within data that might not be evident through other types of queries. In this lesson, you will learn how to use these types of queries in Microsoft® Access®.

Improving Form Presentation

A well-designed user interface can go a long way toward reducing data entry errors and confusion on the part of the user. In this lesson, you will work with advanced formatting features in Microsoft® Access® that will help you improve the way you present data to users within forms.

Creating Advanced Reports

Microsoft® Access® provides various features you can use to enhance reports, such as conditional formatting, themes, quick styles, and calculated fields. Various layout options and report properties enable you to control how reports print.