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.

With subforms, you can see data related to each record on the main form.  This lecture demonstrates how to create a subform in Access and use established or new table relationships to associate the data.

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 this lecture, you'll see how to use sorting and grouping in a report to adjust how the information is presented.  You'll also get a look at using report properties to fine-tune appearance and behavior.

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.

Printing to a label printer like the Dymo LabelWriter enables you to print a few labels at a time instead of an entire sheet. There are a few more steps need to set it up in Microsoft Access but it's really not difficult. This video will show you how to print custom labels with barcodes to the Dymo LabelWriter based on Access data. 

A free barcode font can be found at https://www.inflowinventory.com/blog/code-39-font-archon-free-barcode/.

Once you have a number of items in your database such as forms and reports, a menu can help you to organize your application and make it easier for users to navigate. The Switchboard Manager enables you to quickly create and maintain this type of application menu.  This lesson also shows you how to use Compact and Repair to maintain

Access database applications can be split between files in order to separate the data from the programming. This enables the developer to make changes in multi-user application without overwriting the data. It also enables the application to accommodate more users.

A short example of Visual Basic for Applications (VBA) programming in Microsoft Access.

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.