Microsoft Access Intermediate-H

Course Outline

Designing a Relational Database

It is important to understand what database structure you need to create before you begin entering data. The correct design will ensure that you end up with a database that meets your needs and it will provide you with up-to-date information.
In this lesson, you will:
• Explain the relational database design process by deciding what information you will need.
• Create a table by dividing that information into appropriate tables and fields.
• Create table relationships by relating the tables to each other.

  • Relational Database Design
  • You may wish to invest some time in understanding the process of creating a relational database. It is absolutely crucial to get the picture of what relational database does and how it organizes data into related tables which are found in the same file. The tables can be related by linking common fields like Customer ID to link two tables called Customer and Invoice. In this topic you will learn fundamentals of database design so that you will be able to create an effective and accurate database.

  • Create a Table
  • The foundation of each database are tables and in order to establish a strong base for your data,you need to learn to create them. In addition to that,this topic will introduce various Table Views which help you to display your data in different ways.
    • Datasheet view presents records in rows similar to a table or spreadsheet.
    • Design view displays the underlying structure of the table’s fields, but does not display the data stored in the table.
    • The PivotTable view helps in analyzing data.
    • PivotChart view uses a graphical format.

  • Create Table Relationships
  • After you have created tables, you will need to define table relationships to connect the data.By learning how to create relationships between tables in a database and by setting them correctly, you can be sure that data stays synchronized across the tables.

Joining Tables

In order to retrieve data from unrelated tables,you need to create query joins to find what you want.
How to extract data from single or multiple tables with no common fields? This could be a tricky task if you do not know how such tables can be joined. This lesson will enable you to find and display data by using different types of joins, to view related information and records from another table, and create subqueries.

  • Create Query Joins
  • So far you have created a table relationship in the Relationships window. How to create several different types of temporary relationships by joining two or more tables is the next step in understanding one of the most powerful features of any relational database.
    There are different types of joins, and knowing how to use them will work in your advantage to control which records will be displayed. This will save you time and enable you to examine required data with ease.

  • Join Tables That Have No Common Fields
  • It is easy to join two tables with a common field. Again,joining unrelated tables with no common fields could be difficult without an insight how to retrieve data from tables that are indirectly related to each other. It is possible – all you need to do is to include a table in the middle that contains fields that are common to both of them. And…you know saying that practice makes it perfect.

  • Relate Data within a Table
  • Imagine that you need to find the supervisor of the employee XZ in the related data that could be found within a single table.
    So, you will have to use a special join type called a self join which can be then used to retrieve the desired data from within a table.To find out the supervisor’s name, the table will look itself up.

  • Work with Subdatasheets
  • A subdatasheet is a datasheet placed within another datasheet. Once a relationship between two tables is created, Access automatically adds a subdatasheet,but this can be also manually inserted. When you need to view data in related tables simultaneously, you will have to work with subdatasheets.Once you open them, you will be able to view and update data.

  • Create Subqueries
  • To get exactly the data you need,you remember correctly that you need to get advanced queries involved.
    But to filter your data calls for a powerful and flexible tool known as a subquery.In this topic you will need to find out about Structured Query Language (SQL) which works behind the scenes of query Design view and learn how to create a subquery by using it.

Organizing a Database for Efficiency

Everybody wants a database that is easy to understand and allows a speedy retrieval of data; but only those who get to know the rules of data normalization will be able to organise an efficient one. In this lesson you will use the Table Analyzer Wizard to create new tables and you will also examine the process for determining if your tables are structured correctly and how to make changes to the design that already exist.

  • Data Normalization
  • The expression”Data normalization” describes the process of making decision what data should be stored in each database table.
    Here you will become familiar with the rules for normalization and you will learn to use the Table Analyzer Wizard to create new tables that follow these rules.In this way, you will get a database that permits consistent storage and quick access of information.

  • Create a Junction Table
  • One of the ways to eliminate repetition in some of your tables is to create a junction table and use it as a link between two tables. You may have experienced two tables related in a many-to-many relationship where for every record (row) in the first table,there can be many records (rows) in the second table; and for every record (row) in the second table,there can be many records (rows) in the first table. In order to eliminate such inefficient design, you would need to create a third, linking table.

  • Improve Table Structure
  • At the time you created the table structure upon which you built your database, you must have followed proper design concepts and complied with all the rules. However, as business needs change, so the need to improve your database increases. Following the steps in this topic will better table structure and make it easy to manage changes.

Sharing Data Across Applications

It is possible to share Access data across several formats such as text files, Excel, and Word. This great application enables you to integrate data with other programs, and allows you to analyze and combine data without entering it again. Just imagine all the typing errors! You will be shown how to use this feature to your advantage to import external data into Access, as well as export information from Access to other applications.

  • Import Data into Access
  • When data that you want to use in Access is stored in Word document, Excel spreadsheet, text files or maybe Outlook file, you have two options:to type manually the records into Access tables directly, or you can import external data into Access. Surely,the choice is not a difficult one. To save you time and minimize mistakes,this topic will show you how to import the different data formats into Access.

  • Export Data to Text File Formats
  • You may find that sometimes you will need to transfer Access data to text file or other format. For this task you will use exporting feature in Access.In this process you will send out a copy of data from one application to another.
    If your data needs to be exported into a program that Access does not support, you will learn how converting it to text will enable the other program to open it.

  • Export Access Data to Excel
  • When faced with a request to send an Access table or selected information to Excel for further analysis, you will very likely send a spreadsheet with the exact data instead of the whole database. By understanding this process of exporting Access data to Excel, you can share your data with colleagues unfamiliar with Access.

  • Create a Mail Merge
  • You are aware that mail merge is a powerful tool and it can be even more impressive when your Access database full of customers’ names and addresses is combined with Word. By using both programs you can design personalized documents quickly and easily.
    In this topic, you will learn to create a mail merge and you will see how Word’s ability to create form, letters, mailing labels, envelopes, and other documents becomes much more efficient when merged with Access data.

Advanced Reporting

This lesson is all about creating customized reports and dispalying information in the most effective format so that reader can find data effortlessly. You will also learn to produce specific print layouts.

  • Organize Report Information
  • From your own experience, you know that attractive printouts which use grouping, are easier to read and understand. If you organize information that way and expand from simple report designs you can definitely produce more informative printouts.

  • Format Reports
  • A professional-looking report is an outcome you are hoping for while applying different formatting such as bold, underline, and indentation or control pagination.
    By including this feature,you will be able to capture the reader’s attention and give your document unique feel.

  • Include Control Formatting in a Report
  • If you want to display your data in a way that reader can understand what is going on with a quick glance, you will want to learn control formating that describe data graphically and you will need to understand how to use Data Bars.
    By adding them to your reports, you will create a visual display of comparative data for easier understanding.

  • Add a Calculated Field to a Report
  • This topic will teach you how you can display information that summarizes data by sections, pages, or for the whole report.You will see how Access can use a text box to perform calculations when you enter an expression and how that calculated field added to your report will provide valuable information to the users.

  • Add a Subreport to an Existing Report
  • When you already have created a main report but want to expand and display the linked information in a logical and readable way,you will then add a subreport to an existing report. A subreport is a report placed within a main report which will present related information in the form of a graph, datasheet, or report.

Appendix

  • Additional Reporting Options
  • In order to customize reports, you will need to look at some additional features found in this appendix. You will look closeley at generating mailing labels for your customers, printing address labels directly from Access database, as well as creating a report that can be viewed without opening the Microsoft Access database.

      Create a Mailing Label Report

      In this topic, you will learn to create a mailing label report by using information from a table, query, form, or report that contains the data you want on your labels…and so much more. You will easily produce name badges, divider tabs, product stickers, as well as many other label types.
      If you decide to use The Label Wizard,then many templates for many different types of standard labels will be already provided for you,but you can also design the layout of your own custom label formats.

      Publish a Report as a PDF

      Instead of printing your Access report,you may wish to distribute it electronically without changing the layout and formatting of the original report. You will need to learn how to export the report as a Portable Document Format (PDF) file. In this topic, you will create a report as a PDF which is quicker and easier than printing and distributing hard copies.
      Well done!You have completed Intermediate training and you are on the path to become an Access Wizard.
      Why not to look at the Access Advanced outline and to see what it has to offer?