Managing Your Data with Microsoft Access

The Collier Public Library Database is an example database that we will be building throughout this section of the course. It's based on a fictional small lending library which loans books out to its members and needs to track its inventory and transactions.

By following along with these lessons and building the database on your own, you will gain hands-on experience with Microsoft Access. I encourage you to take the time to complete this exercise, even if you have your own application in mind.

In this lesson, you'll see how to create a new database in Microsoft Access and some of the settings you can use to control your application.

An essential part of designing a robust Microsoft Access application is organizing your data into tables that focus on specific subjects and concepts.  These tables are sometimes called relations in database terminology, hence the term relational database. The tables can then be related to each other so that data can be stored once and referenced as needed instead of being repeated again and again.  This minimizes duplication and error and reduces the size of your data file.

In this lesson, we'll look at some of the basic concepts of data modeling with the Job Search Plus database as a guide. We'll also look at possible data requirements for the Collier Public Library Database.

Selecting the appropriate data type is an important part of modeling your data in Microsoft Access or any other database software. Data types determine how a value will be stored in the database and what operations can be performed on it.  In this lesson, we'll look at the available types in Access and some examples of how they're used.

See the lesson resources for a downloadable summary of Microsoft Access data types with links to additional resources.

In this lesson, I demonstrate step-by-step how to create the central table in the library demo database - tblBooks. You'll see how to define fields and adjust their settings to store data accurately and efficiently.

In this second lesson on table design, we look at the Customers table as an extra example and how they link back to the Books table from the previous lesson.

In this lesson, we're adding the Checkouts table which links the Books and Authors tables together.  You'll also see how to establish relationships between the tables in Access.

An important feature of Microsoft Access is the ability to draw data from many sources together in a single database file and build queries and reports on it.  This lesson shows how to import data from Excel into new and existing tables.

In this lesson, you'll see how to compile the Author's table from existing data and replace the author names in the Books table with the appropriate record ID.

In this introduction to forms, you'll see how to create a quick form to enter author information into the new Authors table.

This lesson shows in greater detail how you can use the available controls and properties on forms to add functionality and enhance the user experience on your forms.

This lesson is an introduction to the report design environment using the Lead Detail Report from Job Search Plus as an example.

This lesson goes into more detail about how to create a report and customize it to provide just the right information. You'll see how to create a query to base an Overdue Items report on and how to work with the report controls to show the data clearly.

In addition to reports, Microsoft Access enables you to generate labels from your data. These are handy for managing mailings, product labeling and other projects. This video will show you how to create a basic set of address labels from the Collier Library sample database.

A bonus video that offers clarification on the concept of Referential Integrity in Microsoft Access using a customer orders table as a guide.

When you're developing a new application, sometimes you need test data to ensure that everything is working right. Mockaroo.com is an online service that will generate random test data to your specifications. This video shows how to work with the site.