Microsoft Access Tutorial for Beginners

By Erika Williamson for Udemy

Interested in more than just a guide? Check out a full course.

TABLE OF CONTENTS: CLICK TO JUMP TO A SPECIFIC SECTION

Introduction

How to Use This Guide

Database Concepts

A Note About Versions

Access Terms and Definitions

Building a Database From Scratch

Table Design

Table Relationships

Form Design for Data Entry

Queries for Data Analysis and Reporting

Reports

Working with an Existing Database

External Data

Best Practices

Understanding and Using a Split Database

Sharing with Other Users

Backups

Compact and Repair

Microsoft Trust Center

Next Steps

---

INTRODUCTION

What is Access?

Access is the database software that is part of the Microsoft Office Suite, which contains familiar programs like Word, PowerPoint, and Excel.

An Access database is a place to store information such as names, addresses, activities, and any other type of personal or business data. You also use a database to manage information through data entry and edits. And, it has tools for creating reports and mailings using your data.

Reasons to use Access

How will Access help your work?

Generally speaking, an Access database is a logical next step after Excel or other data programs when the data reaches a certain level of complexity or high volume, and when having multiple people using the same data is causing errors and confusion.

What are the Pros and Cons of using Access?

The Pros:

• It is completely customizable to your individual and company needs.
• It is very affordable compared to other database packages.
• It is robust and flexible, and it can do almost anything an office or industrial environment needs it to do.

The Cons:

• It doesn’t deploy very easily to the web or hand-held devices without using additional services like Sharepoint.
• It requires a lot more learning and training than other Microsoft programs.
• It does have some size limits, although the limits are high enough that most offices can use Access with no problems.

Is Access any different from Excel?

Access differs from Excel in a couple of key ways, and in myriad small ways. The biggest difference is summed up by the Access adage: “Each piece of data is stored one time, in one place.”

Instead of having lots of worksheets or documents with similar, repeated data, you will have each piece of data stored one time, in an Access table. You can set up infinite ways to view and combine your data, but behind it all, the data will only ever exist in one place. “John David Smith” will never be accidentally duplicated as “John D. Smith” somewhere else.

The other key difference is that you can build highly functional data entry screens and report templates to create an elaborate and elegant database system. You and other users will be able to enter the data much more efficiently and accurately, and you will also be able to get a lot more out of the data using the reports and other analytical tools.

What You Need Before You Start

Before you get started on this tutorial and create a database, you should:

• have Microsoft Access software installed on your computer;
• be a comfortable computer user;
• have solid skills using Microsoft Office programs, such as Word or Excel;
• be familiar with the information that is going to go into the database, whether it’s customer data, equipment data, phone numbers and email addresses, etc.;
• have patience. Access is not learned in day, or a week, or even a month. Most Access professionals will tell you that even though they’ve been using Access for years, they learn something new about it every day. So take time to learn the fundamentals, then dive in and expect to learn a lot by trial and error.

HOW TO USE THIS GUIDE

If you are starting a database from scratch, we recommend that you scan through the entire guide once. Then gather your data and jot down some ideas and questions. Come back to the guide again for step-by-step guidance as you start building your first database.

If you are learning Access so that you can use a database that someone else created, you should still skim through this entire guide, but you can focus on the section called “Working with an Existing Database” to come up to speed on the necessities.

DATABASE CONCEPTS

Access Tables

A database is a collection of tables which store data. By themselves, tables look a lot like Excel spreadsheets. They have rows and columns filled with information.

That’s where the similarity ends.

In order to work with a database, don’t think of a table as an array of rows and columns, but as a group of records, appearing in rows. Each record is a unique item. In a table of DVDs, for example, each record would be a DVD title, such as “Jurassic Park.”

Each record is subdivided into fields. Each field is a piece of information about that item. The DVD “Jurassic Park” would have fields for title, rating, running time, director, and year released.

Usually, each record also has a “Primary Key,” which is a numerical unique identifier. While the title of the DVD might be “Jurassic Park,” its Primary Key will be a number, like 1. Throughout the rest of the database, that record will usually be referred to simply by its Primary Key.

Access is a relational database program. What does that mean?

A relational database is a database where multiple tables are related to each other in specific ways.

For example, a relational SALES database puts information about customers in one table, and information about what those customers buy in another table. This structure results in two separate tables, CUSTOMERS and ORDERS.

The reason for the separation is to eliminate redundancy or duplicates. Think about a single Excel spreadsheet: if John Smith had 14 orders, there would probably be 14  rows of order information for him. And his name, phone number, and address would probably be duplicated all 14 times. There could be typos there, and it is certainly inefficient for data entry. Or, there might be two Excel spreadsheets, one with names and addresses, and one with orders, but no easy way to link to the two together.

In an Access database, John Smith would appear one time in the Customers table. His 14 orders would be listed in the Orders table. Each Order record would contain his Primary Key number, thereby linking the information back to him.

Note: the data entry process for Orders creates an automatic entry for John Smith’s Primary Key, so that part is done by the system and not the user. It makes it quick and easy to enter orders for any customer, with fewer errors and duplication.

Beyond Tables, what is Access all about?

You store all your data in tables, but you don’t do data entry in tables, and you don’t analyze or print your data from the tables. Access provides tools to set up a controlled, secure, and efficient way to do those things and more. In the rest of this guide, we’ll be discussing all of those tools and how to use them to build your custom Access database.

A NOTE ABOUT VERSIONS

This tutorial contains screenshots from Access 2010. The concepts, tools, and specific instructions will apply to any desktop version from 2007, 2010, 2013, or Office 365. However, the screenshots will not reflect small visual differences among the versions.

This tutorial also focuses exclusively on using Access for Desktop, the most common usage of Access. This tutorial does not address the topics of web databases or databases for Sharepoint.

ACCESS TERMS AND DEFINITIONS

Tables

Tables store raw data that is the backbone of the entire database system. Tables are often related to other tables in the file.

Forms

Forms display live data from the tables for data entry or edit. They are sometimes called “screens” or “user interface.” Data in a form is selected from one or more tables or queries. Menus/Dashboards are also types of forms.

Queries

Queries answer a question by selecting and sorting data for you based on what you want to know. For example, a query can tell you “how many open orders are there this month?”

Reports

Reports display data in pre-formatted templates for printing or display. Data cannot be edited in reports. It must be edited through in data entry, prior to running the report.

Macros

Macros are mini computer programming shortcuts that allow you set up commands and processes in your forms, such as searching, moving to another record, or running a formula.

Modules

Modules are processes and functions that you write using Visual Basic for Applications (VBA).

Navigation Pane

The Navigation Pane is on the left-hand side of the main Access window. It displays a list of all of the Access Objects (Forms, Reports, Queries, Tables, Macros, and Modules) that you have built in your system. You can open the Navigation Pane at any time to work with any object.

Ribbon

The Ribbon is the toolbar at the top of the Access window. It is divided into tabs with different commands and functions. Its options will change slightly depending on what type of object you have open (Forms, Queries, etc.).

BUILDING A DATABASE FROM SCRATCH

Table Design

Designing a Table

Table Design is the single most important part of building a functional database. You should take the most time to organize your tables of anything that you do. If you are successful, you won’t have much trouble with the rest. If you don’t take the time to properly design your tables, you will run into problems later on in the project.

Step One: gather all of your information together in whatever format it’s currently in. Read through all of it, focusing on the types of data it is (phone numbers, names, descriptions, etc.) and identify the most important pieces.

Step Two: using pen and paper, draw a box for your core unit – whatever the central piece of your database will be. This could be customers, work orders, equipment, research projects, etc. In the example below, the core units are the students at a university. Fill in the box with the most important data for your core unit.

Step Three: create more boxes for the other data in your database – the related and supporting data for your core unit. Draw lines to show how they are related (see diagram).

The point of this exercise is to separate and categorize all your data into logical boxes, which will later become tables.

How do you know what boxes to put your data in?  The rule of thumb is this: if your core unit has ONE of something, it goes in their box. If they could have MORE THAN one of something – parents, programs – those go in a different box.

In the example above, the university’s study abroad students have certain demographic data that they can only have one of – a birth date or a gender, for example. So that data goes in the “Students” box, along with their name and any other unique data.

However, students may participate in more than one program, so their program participation must go in a separate box.

The Study Abroad Program locations will each have one unique name, such as “Paris Art Study,” so they will go in a box for Programs. However, each program will have many participating host families, so the host families go in a different box.

The “one-to-one” and “one-to-many” rule of thumb is the basis of a relational database. These relationships will determine how effective and efficient the database is.

A third type of relationship is called a “Lookup.” Lookup data is stored in its own table, but can be used in other tables. In the example above, the Program Names will be used in the students’ program participation records, so Program Name will be a form of Lookup data. The dotted line represents this relationship.

The next step will be to recreate your boxes as tables in your Access database. Follow these steps to create a blank database and build your tables.

1. Open the Access software.
2. In the top left, choose File and select New.
3. At the far right of the screen, change the default name “Database1.accdb” to the name you want for your database – for example, “Study Abroad Participation.accdb.”
4. You may click the small folder icon next to the database name to browse to a different folder location if desired, and click OK.
5. At the top-middle of the screen under “Available Templates,” select the option for “Blank Database.”
6. Click Create.

The new database you have created will appear with two things to look at: in the Navigation Pane, you will see a default table called Table1. In the main viewing area, Table1 is displayed, and has a default ID column and a space to start adding new columns.

Let’s stop here and explain what’s going on with the ID field and the other columns.

The ID field is going to be the Primary Key for your table. That means that each core unit piece – each student – will be uniquely identified by a number. The ID is also an autonumber field, which means that as you start entering students, Access will automatically assign them a sequential number as their ID.

The second column will become the next field of data for each of your records. So let’s get started adding fields.

Click the small down triangle in the “Click to Add” box. This will give you a drop-down list of the types of fields you may use in your table. Most of the time you will be using “Text,” but there could be plenty of numbers, dates, currency fields, and yes/no fields in your table too.

In this example, we’re going to start with “FirstName,” which will be a text field. When you select “Text” from the list, you will then be able to type a name for your new field, as shown below.

Repeat the process to add other pieces of data identified from the pen-and-paper exercise in the main box (table).

A note about field names – names should be short and clear. Although Access will allow spaces in field names, such as “First Name,” it’s best to avoid spaces. Use only letters and numbers, and no punctuation, except for the underscore, such as “First_Name.”

Once you have entered your fields, it’s time to save the table. You can always add, delete, and revise fields after saving. Select File from the Ribbon and choose Save.

You will be asked to enter a name for your table.

Like field names, table names should be short and clear and avoid spaces and punctuation. A recommended naming convention is to add the prefix “tbl” to the front of your table names. This will distinguish them from queries and reports that might have similar names.

Field Types and Indexes

When we created this table, we selected “Text” as the field type for each field. Now that the table is built, we can refine the fields so they will be more effective for each type of data.

The type of view of the table you’ve been seeing so far is called Datasheet View, which is the typical grid-style view used for seeing and editing data. To look at field types and make other changes to the table design, you will switch to Design View, which is like flipping up the hood of your car to look at the engine.

On the Ribbon, select the Home tab. (You might have to expand the Ribbon by clicking on the small up/down arrow on the far right.)

Click on Design View in the upper left – the icon is a blue protractor and yellow pencil.

This will take you to the Design View of your table. Here you will see that every field name that you created has lots of choices for settings, such as type, length, format, etc.

The first thing to do is to rename the field name “ID” to something descriptive, such as “StudentID.” The reason to change this name is that every table will have an ID field, and if they are all called “ID” by default, it will get confusing.

Simply overwrite the existing “ID” name with a new name.

Save the table by clicking the Save icon at the top left, or by keying Ctrl-S.

Next, let’s look at the Data Type column for each field. Starting with the StudentID field, you can see that the Data Type is AutoNumber, which is standard for an ID field. It is also already designated as the Primary Key. (Note: a table may only have one Primary Key.)

In the Field Properties window – in the lower portion of the screen – you can see in the Indexed property that the StudentID field is set to “Yes (No Duplicates).” This is also standard for a Primary Key field. This means that the database will always search and sort the data based on the Primary Key, unless told otherwise. It also means that there can never be a duplicate value in this field.

While a table can only have one Primary Key, it may have several indexed (sort) fields. For example, you may set the indexed property on the “LastName” field to “Yes (Duplicates OK).” This means that the database will search and sort that field alphabetically, and it will allow duplicates. (You want to allow duplicates because different people might have the same last name, i.e, you might have a Carla Smith and a John Smith).

In general, it is useful for tables to have a few indexed fields. It speeds up searching and it helps your data show up alphabetized or sorted properly by default. However, having more than a few indexed fields is not helpful and will be cumbersome for the database. So, for many fields, the Indexed property will be “No.”

As you can see in the screenshots, all of the fields have the Data Type of “Text.” However, the DOB field (Date of Birth) should really be a Date field. You can change the data type by clicking on the Data Type column next to the name of the DOB field, and selecting Date/Time. This will change the field type.

You can also define the Format of the field, so that the date will look the same in every student’s record, and so data entry will be easier. In the Field Properties window, select a format from the Format drop-down list.

In addition to Format, you can also change the Default Value of a field. This will mean that every time a new record is created, a field will automatically contain a certain value without the user having to perform data entry each time.

For example, many databases have tables that contain phone or email contact records for customers or clients who want to report problems or requests. In the “Date of Contact” field, the Default Value can be set to today’s date, so that every new record automatically captures today’s date without the user having to enter it.

As you can see, between the Data Type choices and the Field Properties you give to each field, there are a lot of customization options. We won’t go over any more specific ones here, but you can find many resources to examine Data Types and Field Properties online and in your software manual.

Manual Data Entry vs. Appending from External Data

Save the table and switch back to Datasheet View. Your table is now ready to populate with data, and there are two main ways to accomplish this.

1. Manual Data Entry. Using this method, you start typing your data into your table manually. Sometimes this is your only option, especially if you are starting from scratch or if it is a database for personal use. This method is fairly simple:

• Place your mouse in the second column (after the ID field), and begin typing.
• Use the Tab or Enter key to move to the next field.
• At the end of the row, use the Tab or Enter key to move to the next record. The autonumber will assign the ID value. There is no need to think about sorting your data at this point; you will do that later on in Queries, Forms, and Reports.
• Access will save your work as you go (no need to click the Save icon after each record).

2. Appending (Adding) from an External Source. If you’re lucky, your data already exists in some sort of usable electronic format that can be easily added to your empty Access table. If you have your data in Excel, .csv, .txt, .xml, or an Outlook folder, you can follow the steps below to append data to your table. For our example, we are going to append from an Excel file. Here are the steps:

• The Excel worksheet we have with student data has all the same fields as the Access table.

  

• The problem is, the column names are not exactly the same as the field names in our Access table. So we have to change all the column headers to be precisely the same as the Access field names. Make the changes, then save and close the file.

  

• Next, in your database, if your Access table is open, click the “x” in the top right of the table to close it.
• On the Ribbon, select the External Data tab, and click Excel from the Import & Link section.
• A dialog box will appear. Click the Browse button to locate your source data file in Excel.
• Select the option for “Append a copy of the records from the table” and select “tblStudents.”

  

• Click OK, and on the next page, select the named worksheet of the data you want to use.

  

• Click Next, and on the next page, determine whether your first row is column headings. Access will assume by default that it is.

  

• Click Next, and enter the name of the Access table you want the data to go into. On the next screen, click Finish, and the following screen will tell you if the data append was successful. If it was not successful, the system will display error messages, and you will need to modify your Excel spreadsheet to match your Access table more closely, and then run the process again.

• If the process was successful, open your table. In our example, we open tblStudents and see that the Excel data has been added. Access assigned a Primary Key (StudentID) number to each record as it was appended.

Generally, it is easier to append existing data from an electronic file than to manually enter data. However, your existing data very rarely matches your new Access tables exactly, so you will probably need to do a lot of modifications and cleanup of your source file (always make backup copies first!).

In particular, if your Access table is designed to hold unique records – no duplicates – then you need to clean up your Excel file so that there are absolutely no duplicate records. If duplicates exist, figure out why. The most common reason is that there is a one-to-many relationship in the data, and the extra pieces of information need to go into a different table.

Table Relationships

Now that you have built one table, you need to continue to build your database system with related and supporting tables. Refer back to your pen-and-paper exercise and create all the tables you sketched out in your planning phase.

In our database, we are going to build two more tables: tblPrograms and tblProgramParticipation. The following screenshots will show you how the tables turned out, and what fields they contain.

Take a look at the two screenshots together, and notice a couple of important things:

• Each table has a Primary Key or ID field, which is the unique identifier for each record in the table.
• The second table has additional numerical fields which contain the IDs from other tables. These fields link records in this table to their related records in other tables.

The Program Participation table might look perplexing at first glance, because it seems to contain only numbers and no useful information. Taking it column by column, we can interpret what these numbers are telling us.

• The ParticipationID column is the unique identifier for each record in this table.
• The StudentID column tells us what student this information belongs to.
• The ProgramID column tells us what program the student did.
• The Dates tell us what year the student went on the trip.
• The Notes field allows users to keep a narrative of any issues from the student’s experience.

Remember, these tables are where you STORE your data. Luckily, you won’t be doing data entry in the tables, which would be pretty difficult and confusing with all these ID fields. You’ll be doing data entry in forms, which will have tools like Drop-Down lists to make data entry clear and easy.

The Relationship Window

Now that our tables are built and our Primary Key fields are in place, we’re going to tell the Access system exactly how all of these tables are related.

First, go to the Database Tools tab of the Ribbon and click Relationships.

The Relationships Window will open and be blank. The Show Table dialog box will appear. Highlight and add all of the tables shown.

When you are done adding tables, click Close to close the Show Table box.

You will now see boxes in your Relationship Window that represent your tables. You can see that all the fields you built into your tables are listed in each box.

You might want to use your mouse to reorganize the boxes (drag them around) and expand the borders so you can see everything more clearly.

Now it’s time to define relationships by creating Links or “Joins” between tables.

This is a drag-and-drop process. Starting with the Students table, we click on the StudentID field and drag it over to the StudentID field in the next table (tblProgramParticipation). As soon as we click off the mouse, a new window will appear that displays the options for defining the relationship.

This box essentially says, “Use the StudentID field from tblStudents and link it to the StudentID field in tblProgramParticipation.”

Check the box to Enforce Referential Integrity (this prevents problems down the line).

Note that the type of join it is creating is called “one-to-many,” which means one Student may have many Programs. Most Students will only do one study abroad trip, but some might do two or three, so this is the correct option.

Click Create to finish. Now our Relationships Window looks like this:

The black line represents the link or join that we just created. The tiny “1” and “∞” (infinity symbol) tell us that the type of join is one-to-many.

Using Tables for Lookups

Next, we’re going to define a relationship between tblProgramParticipation and tblPrograms. We could do the exact same thing we just did – drag and drop to create the link between the two ProgramID fields.

However, we’re going to build added functionality by using the Lookup Wizard to create this relationship.

You might recall that we want to be able to look up the Program Names from tblPrograms to store in the participation records. Using lookups means that the user can select a name from a drop-down list (instead of typing), and the user can’t accidentally type a program name that doesn’t exist.

Here’s how:

1. Save and close the Relationships Window.
2. Open the Navigation Pane. Select tblProgramParticipation and double-click on it to open.
3. Switch to Design View from the Home tab in the Ribbon.
4. In the Data Type column for the ProgramID field, click the drop-down arrow.
5. Select “Lookup Wizard”.

   

6. Selecting Lookup Wizard will bring up a series of prompts for you to click through. First, select the option to use a table or query for your lookup.

   

7. Next, choose the table you want to use for your lookup source. Choose tblPrograms.

   

8. Then, choose one or two fields to include in your lookup. For our example, we want both the ProgramID field and the Program Name field. Use the > arrow to select fields.

   

9. Now select the field you want to use as your sort field. We want our list to be in alphabetical order by Program Name, so we select that.

   

10. On the next screen, use your mouse to expand the column size to be readable. The box for “Hide Key Column” should be checked.

    

11. Next, choose a name for your lookup field. It will default to the name you already have, and you should leave it that way. Leave all the default options at this point (no boxes checked). Click Finish.

    

12. The last step will prompt you to save the table. Click Yes.

You will now be returned to the Design View of your table, which won’t appear different right away. The field type for ProgramID is still “Number.”

However, when you switch to Datasheet View, you will see that the ProgramID field is now displaying the NAME of the Program, instead of the ID number. Also, if you click into that field with your cursor, you will now have a drop-down arrow to give you a list of Program Names to choose from.

How is it doing this? The ProgramID field is actually still STORING the ID number. The real value in that field is still an integer like 1, 2, or 3. coming from tblPrograms. But, we have told it to use that number to look up the Program Name for our convenience in viewing and editing.

Now, close the table. Go back to your Relationships Window. You will see that our Lookup Wizard created the link we needed between tblProgramParticipation and tblPrograms.

Form Design for Data Entry

Now that your tables and relationships are done, you are ready to develop forms for data entry.

Forms (data entry screens or menus) are designed to help users enter and navigate their data. On any form, you can create text boxes that are tied to the fields in tables to allow you to add or edit data to those fields.

You can also create buttons that execute procedures, like moving back and forth between records or creating a new record.

Using Tables as Data for Forms

To continue our demonstration, we will create a form that allows us to enter and view our students and their study abroad trips. There are several different ways to create forms, including starting from scratch with a completely blank form. As a beginner, you will probably find using the Form Wizard the best way to get started.

1. Go to the Create tab on the Ribbon, and click Form Wizard.

   

2. On the first prompt, select your main table (tblStudents) from the list, and all the fields from the table using the >> button.

   

3. On the next screen, select “Columnar” for the layout. That is the most common layout and will show you one record per screen, i.e., one Student at a time.

   

4. On the next screen, give the form a name. It will default to the name of the table we used, tblStudents. We definitely do not want to call it tblStudents! That will lead to confusion with the table of the same name. We’re going to use a naming convention which adds the prefix “frm” to every form, and name it “frmStudents.” Click Finish.

Here’s what the Form Wizard created:

Let’s take a minute to dig deeper into what you’re seeing in the above screenshot.

• Navigation Pane: You can see that our new form is now listed in our Navigation Pane (far left) in the Forms section. Any time we want to use this form, we can choose it from the Navigation Pane.
• Title: The default form title is “frmStudents.” We will change that to something more readable.
• Fields: You can see that all the fields we created in tblStudents are displayed on the form.
• Navigation Bar: At the very bottom of the form, we can see the Navigation Bar. It tells us that we are on “Record 1 of 15.” The form can scroll through all 15 Students in our tblStudents, but we are currently looking at the first one. We can move to the next record by clicking the right navigation arrow. We could also click into the Search space and search any field.
• Sort Order: You can see that the form is showing us our students in order of their Primary Key, with Lisa Smith (1) appearing first. Most users will be more comfortable with seeing data in alphabetical or another logical order, so we will change the Sort Order of the form.

The view you’re seeing is called Form View. Like tables, forms also have a Design View where you can change settings and options. Click the View button from the Home tab on the Ribbon to switch views.

In Design View, you can see the grid of the form and how the fields are laid out visually. These fields are called textboxes, and their captions or names are called labels. Labels and textboxes are types of controls and every form is made up of a variety of controls.

The first thing on our to-do list is to change the title in the header of the form. To edit the title label, click into it with your cursor, and overwrite the title with a new one, such as “Students in Study Abroad.”

Note: this doesn’t change the actual name of the form, as seen in the Navigation Pane. This just changes what the user sees at the top of the form during data entry.

You can use the Format tab on the Ribbon to change the font color, font size, font, and alignment of this label.

Next, we want to reduce the height of all our fields. The field contents are not very long, and they don’t need such giant boxes. The boxes take up space, and we have more information we want to add later.

1. Use your mouse to select all the text boxes and their labels. You can do this quickly by drawing a rectangle around all of them together. They will appear with a highlighted border once they are selected.
2. Right-click to bring up a Properties menu, and select “Size” => “To Shortest.” This will make all the boxes the same height as the shortest one.

   

3. While they are all selected, right-click again, and choose “Align” => “Top.” This will organize all the boxes more compactly and give us room to add program participation.

   

Again, you can use the Format tab on the Ribbon to change font, color, backgrounds, size, etc. We will not go into detail on format properties in this tutorial, but you can experiment with various custom options as you go.

Save and switch to Form View to see your changes.

Last, we’re going to change the Sort Order of the form so that the students appear in alphabetical order.

1. Switch to Design View of the form.
2. In the upper left-hand part of the form, there is a small gray square. Click once on the square to produce a small black square inside it (see screenshot below). That action selects the form itself, rather than individual controls.
3. Then click on the Property Sheet icon in the Design tab of the Ribbon. This will bring up the property selections available for the form.

   

4. The Property Sheet allows you to adjust settings that determine how the the form works and what it looks like.
5. To change sort order, click the Data tab in the Property Sheet.
6. In the “Order By” row, type “[LastName].” Make sure “Order By On Load” is set to Yes.

   

7. Save and switch to Form View to see the changes. The students will now be sorted on their LastName field.

Subforms with Master/Child relationship

Now that our main Students form is set up, we want to incorporate the information from tblProgramParticipation. We will do this by creating a subform.

1. Switch to Design View of the form.
2. From the Navigation Pane, click and drag tblProgramParticipation over to the Students form and drop it in the lower portion of the form.
3. This action will bring up the Subform Wizard.
4. The first prompt will ask you how you want to link your main form records to your subform. The system already knows the relationship between the tables we’re using, so the default options are correct. So, use the option for “Choose from a list” and use the first option from the list. Click Next.

   

5. On the next prompt, provide a name for the new subform you are creating. There are a variety of naming conventions for subforms; the important thing is to be consistent. We’ll use “frmStudents_sub.” Click Finish.

   

The Subform Wizard inserts a new form at the bottom of your main form. This subform will allow the user to enter participation data for each student in tblStudents. Save and switch to Form View to see the changes.

What you’re seeing in the screenshot above is the main record for Jessica Avery, and her program participation records below. Because of the relationships between ID fields, the system knows to display only Jessica’s records from tblProgramParticipation.

There are a few revisions that will make this subform more user-friendly.

First, we don’t need to see the first two ID columns. They function to make everything work together, but we don’t need to SEE them.

Switch to Design View of frmStudents. If we highlight the two fields in the subform, “ParticipationID” and “ProgramID,” and hit the delete key, we remove them from view. This does not affect the functionality of the subform.

Once you have hit Delete, save and switch back to Form View to see the changes.

Now the form is ready for data entry. For any student, the user may edit any field (other than the ID field) in the upper portion of the form.

Users may also add or edit participation records in the lower portion of the form. To add a new study abroad trip, the user can select one from the drop-down list in the ProgramID column. This is the lookup field that we created earlier.

One last thing to note when using subforms: at the bottom of the main form, you see the Navigation Bar with the total number of students in the table (15). But now, you can also see a Navigation Bar for the subform we created. The subform in the screenshot above shows 2 records for the current student (one that already existed, and a new one that we are adding).

Add command buttons such as find, move, delete, print

Although users can move through the students’ records using the Navigation Buttons at the bottom of the form, you may want to add command buttons to your form to make navigation easier. To activate the Command Button Wizard, switch to Design View of the form and follow the steps below.

1. Click the icon for the Command Button.

   

2. This converts your mouse pointer to a button creator. Move into the form header area and use your mouse to draw a rectangle. This action will bring up the Command Button Wizard.

   

3. From this list of options, the user can select a wide variety of different commands to place onto the form. In this example, we are choosing to create a button that will move the user to the next student record.
4. Continue to follow the prompts in the series. Make sure to give your command button a descriptive name, like cmdGoToNext, rather than the default name. Click Finish and then switch to Form View to see your new button.

Typically, forms will have several command buttons that can move back and forth between records, search records, create new records, and delete records. Other command buttons can run procedures that update fields with calculations or default values.

Creating a Navigation Form

Now that we have introduced the process for creating command buttons, we can demonstrate how to build a Navigation Form (main menu, startup form, or dashboard) for your database.

A startup form is the first screen that the user sees when he or she opens the database. It should have a clear “road map” for any task the user needs to do. Typically, the startup form consists of command buttons that take the user to other forms and reports that are part of the database. This clear road map is more user-friendly than hunting for the form or report you want from the Navigation Pane.

Here is an example of a startup form. It consists of command buttons that open other objects in the system. Users can also add colors, logos, and help text to the form.

1. To create a menu form, start by clicking Form Design from the Create tab. This opens a blank form template that is not connected to any data table. Since the menu is not a data entry form, it doesn’t need to be bound to a data table.

   

2. Next, use the Command Button Wizard to create a new button. The button we’re creating will be used to open frmStudents.

   

3. Follow the prompts to select frmStudents as the destination form, and give your button a descriptive name, such as “cmdOpenStudents.” You can also type in the text you want to appear on the button itself, such as “Open Students Form.” Click Finish. The button will now appear on the form.
4. Next, you can use a label control to create a title for your form. Click the Label icon and draw a rectangle on your form. Type in the text you want to appear as the title.

   

5. Save and switch to Form View to see the new controls.

This is the foundation for a typical startup screen. Continue to create all the buttons you need for your database.

Note: this is a fairly simple and straightforward way to create a navigation form, but there are a variety of approaches that are used for different purposes. However, if you are creating a web database, this method will not apply. Web databases have a unique method for creating a navigation screen.

The last step in creating a startup screen is to tell the system to automatically open the screen when a user starts the database. This command is found in File => Options=> Current Database.

Next to Display Form, select the form you want to open at startup, and click OK.

The next time the database starts, your startup form will open automatically.

Queries for Data Analysis and Reporting       

Now that you have tables full of data, and forms to allow data entry, you will need some way to view your data in various combinations. Access queries allow you to sort through a specified selection or subset of your data so that you don’t have to scroll through multiple tables looking for answers.

A query is a template, or structure, of a question. It allows you to pick and choose from your tables exactly what you need to see. Every time you open the query, you get a view of the most current data, so if you save a query you can go back to it over and over again for the most up-to-date information.

To introduce query building, we’re going to demonstrate a simple query design process.

1. From the Create tab in the Ribbon, select Query Design.
2. The first dialog box that will appear is called the Show Table box. Choose one or more tables that you want to get information out of. For this demonstration, we will choose two tables: tblStudents and tblProgramParticipation. To select tables, highlight them with your mouse and click the Add button. Click Close when done.
3. Now you will see the basic template for a query. In the upper portion, you see boxes that represent the tables you have selected. In the boxes, you see all the fields that the tables contain. You can use your mouse to expand the box borders so you can see all the fields.

• Because the system recognizes the relationship between tblStudents and tblProgramParticipation, Access automatically creates the correct link between the tables. The black line between the tables represents this link. This will tell the query to connect the students with their records in the other table.

  

4. The lower portion of the query screen, the grid-style view, is where you choose which fields you want to see from each table. You can select one, a few, or all of the fields. This is a drag-and-drop process or a double-click. To select the FirstName field from tblStudents, either click on it with your mouse and drag and drop it into the grid area, or double-click on it and it will pop into the grid. Continue for any other fields you wish to see.

In the top row of the grid area you see the field names you have selected. In the second row, you see the name of the table the field came from.

5. To view the data you have selected in your query, click the red exclamation point icon at the top left of the screen, in the Design tab of the Ribbon.

   

6. Clicking the Run icon will display the data you have requested from the query.

   

As you can see, the five fields we picked in our query appear in the order that we put them in the grid.

You will notice that Lisa Smith appears twice. This is because the query is showing a combination of records between both tblStudents and tblProgramParticipation. Because Lisa has two participation records, she appears once for each record.

You can also see that only four students are listed – not all 15 that we know are in tblStudents. This is again because of the combination with the participation table. We are using the two tables linked together, so the only results we will see will be students who have records in the second table. The other students don’t have any data in that table, so they do not appear on the list.

Last, you might notice that the students are appearing in order of their Primary Key number (not shown). Most users will want to see the students in alphabetical order, so let’s make that change now.

1. Switch to Design View of the query.
2. In the grid area below, there is a row called “Sort.” You can use this row to sort on one or more fields. Simply select “Ascending” or “Descending” in the field you want to sort. In our example, we are sorting ascending using the LastName field.

When we run the query again (using the red exclamation point), the data will be sorted on LastName instead of StudentID.

The final row in the query grid is the Criteria row. This is where you further refine the results you want to see.

At this point, we are seeing all the students who have participated in study abroad. Now we want to know, “How many students participated in study abroad in 2015 or beyond?”

1. Switch to Design View of the query.
2. In the grid area, you will see a row called “Criteria.” Some examples of criteria could be City = “London,” or DOB > 1/1/1992, or OrderQuantity is between 1 and 10, or Country field is Null (blank). Learning all the tips and tricks, syntax and structure for criteria expressions could be an entire lesson in itself, but we will demonstrate one basic example here.
3. In our query, we will set the criteria in the StartDate field to “>12/31/2014.” This will give us all the records where the start date is greater than, or after, the end of December 2014 – in other words, all records in 2015 or higher.

   

4. Now when we run the query again, we will see only the two students who traveled in 2015.

   

As always, when you save the query, you should give it a descriptive name, and use a prefix to distinguish it as a query. We will name ours qryStudents, and it will appear in the Navigation Pane so we can go back to it any time.

While you can do most of your sorting and filtering using criteria in the Design View of queries, you can also sort and filter in Datasheet View. Much like in Excel, there are sort and filter options available on the Ribbon. In the screenshot below, you can see that we have sorted ascending on StartDate, and so we see the records reorganized in that order. This does not alter the original design of the query; it’s only a temporary view.

Another useful function in the Datasheet View is using a field to filter the records. If you place your cursor into any field and right-click, you will see some filtering options. For example, if we right-click in the ProgramID field, we can refine the list to show us the fields from only one Program.

The query we’ve been demonstrating is called a SELECT query. It pulls up data for viewing or editing when you run it using the red exclamation point icon.

The other types of queries are Make-Table, Append, Update, and Delete. These queries perform an action – or change – on the data you have selected. There is no “undo” function, so use action queries with caution.

A few things to keep in mind about queries:

• The data you see in the datasheet view of a query is LIVE. Editing data there alters the real table data in real time, so use with caution.
• You can alter query designs over and over until they produce the results you need.
• You can use queries as data sources for forms and reports, or Mail Merge into Word.
• You can export query data to Excel.
• ALWAYS open a query in Design view, in case it is an Action Query.

Reports

Reports are the final output of all your data entry and database organization. To create a report, you build a formatted template that you can open again and again with up-to-date data each time.

When building a report, you combine elements such as data from tables and queries and static content such as titles, headers, and logos, You also make decisions about how to group and sort the data so that it appears the way you want it to. Report building requires extensive knowledge about your data as well as the relational structure of your database.

Building Simple Reports

We’re going to use the Report Wizard to build a simple report that shows us all the students who have participated in study abroad.

1. From the Create tab, click the Report Wizard button.

   

2. You can use tables or queries as the underlying data for reports. We’re going to use the query we created called qryStudents.

   

3. Select all the fields and click Next.
4. On the next screen, you’ll choose how you want your records grouped. For example, do you want your information listed by Student Name? Or grouped by Gender or Language? Or, you can group the records by Program. For our first demonstration, we’ll group our report by Student.

   

5. On the next screen, you can add additional grouping levels if desired.
6. Next, you add a sort field for your Detail records.

• Detail records are the data that appear under your main grouping level. Our main group is student. The Detail records for each student are the programs. If the student did two programs, we want them to appear in order by date, so we choose that here.

  

7. Next, we choose a basic layout for the report. Usually the default “Stepped” layout is adequate. You will do a lot of reorganizing anyway, so it doesn’t matter too much at this point.

   

8. Last, type a descriptive name for the report and click Finish.

   

9. Voila! The wizard creates a basic report. However, as you can see in the screenshot below, there are some problems. Spacing, sizing, and sorting are all not quite right.

   

10. In order to clean up the report and adjust settings, switch to the report’s Design View by clicking the Close Print Preview button.

Seeing the Design View of a report for the first time can be confusing. Think of a report as a template that you create using various building blocks. Each piece has settings that determine how the final result will look once it’s populated with data. Let’s take a minute to look at each element.

• Report Header: This is a label containing text that will appear on

  the first page of the report. To change the label, click into the box with your cursor and add or edit text.

• Page Header: This is text that will appear at the top of every page. In the screenshot above, the page header contains labels that will act as column headings for the data below.
• StudentID Header: Because we have the report grouped by student, we have a Header area for student data.
• Detail: The Detail area is where each student’s detail information will appear. In our report, that includes the dates and names of each program the student did.
• Page Footer: The page footer contains dynamic elements that will appear at the bottom of each page. On the left is a time/date box that will display today’s date and current time. On the right is the “Page 1 of XXX” marker.

The first thing we’ll do is select and drag the labels and text boxes to resize them for a better visual layout.

To make these changes, select any box with your mouse and use the mouse to expand or move the boxes.

Switch to Print Preview to see the changes. Already the report is looking a little better.

Next, we’re going to move the program data to appear under the students’ names, rather than way over on the right. We’re also going to move the program name to the left of the StartDate.

Switch to Print Preview again:

With these types of changes, you can modify the look of your report according to your preferences.

There are also numerous formatting options in the Format tab in Design View. For each label and text box, you can change the font, font color, font size, border color, border transparency, background colors, alignment, etc.

Grouping and Sorting

Grouping and Sorting are a little more complex than simple formatting changes. First, let’s change the sort order so that the students are in alphabetical order.

1. Switch to Design View.
2. Click the Group and Sort button in the Design tab of the Ribbon.
3. This will open the Group, Sort and Total window at the bottom of the screen. Click on “Add a Sort.”

   

4. Select a new field to sort on – LastName. Then, use your mouse to drag and drop the sort field up so that it is at the top of the group/sort list.

   

5. Switch to Print Preview to see your changes. The report will now be sorted on LastName.

We can also change the primary grouping to be by program instead of by student.

1. Close Print Preview to switch to Design View.
2. Click on the Group and Sort icon in the Ribbon.
3. Click Add a Group and choose ProgramID.

   

4. Use your mouse to drag and drop the ProgramID group line up to the top of the list, above LastName.
5. Access will create a new header area for ProgramID.
6. Use your mouse to move some of your text boxes around.
   • Move the ProgramID box into the header area for ProgramID.
   • Move the StartDate and EndDate boxes into the header area for StudentID.
   • Move the column labels that were in the Page Header to right under the ProgramID text box.

7. Switch to Print Preview and see the changes. All the same data is there, but reorganized into a completely different order.

Creating Group Sums and other Totals/Counts

Access Reports can also be useful for finding sums, averages, and counts of data. When you add a textbox in the footer area of a group, such as ProgramID, you can tell it to give you a total number of students in each program. You can also add a grand total in the report footer.

1. Switch to Design View of the Report.
2. In the Group, Sort and Total window, click the arrow next to ProgramID and choose “more.”
3. Click “with totals.” Select “Count Values” as the type of total, and check the box for “Show subtotal in group footer.”

   

4. This will add a footer section to your StudentID group. The footer will show a new textbox that says “=Count([StudentID]).” This is the expression formula to count all the records in the section.

   

5. When you switch to Print Preview, you will now see a total number of students in each program.

   

You can use formatting options to add a label to the subtotal, increase the font size, etc.

Now we’ll add a similar box in the report footer that will tell us the total number of student participation records in the whole report.

1. Switch to Design View.
2. Expand the report footer area by using your mouse to drag the bottom border of the report template downwards.
3. Copy the “Count” textbox from the ProgramID footer by selecting it with your mouse, and hitting Ctrl-C to copy.
4. Paste the “Count” textbox into the report footer by clicking on the bar that says Report Footer and hitting Ctrl-V to paste.

When you switch to Print Preview, you will now see a total on the last page of the report that tells us that we have a total of 17 Study Abroad trips.

One last note about Reports: when you run a report, you might notice data errors. For example, you might see a misspelling or a wrong date. To fix data errors, you must return to a data entry form, complete your edits there, and then run the report again. You cannot change data on a report.

Of all the tasks in Access, many users find reporting to be the one that requires the most trial and error. Try lots of combinations of grouping and sorting, headers and details, and it will eventually click!

WORKING WITH AN EXISTING DATABASE

Tables

The first step in working with your database is knowing your tables and what data is stored in each of them. View the tables by opening the Navigation Pane (use the F11 key to open if necessary). Double-click on each table name to open it up and scan through what’s in it, taking note of field names.

After reviewing the tables, open the Relationships Window to see how the tables are related. The Relationships icon is on the Database Tools tab of the Ribbon.

Navigation

With any luck, the database you’ve been handed will be have a user-friendly design, starting with a helpful main menu or dashboard upon opening the database. To get to know the database, begin by clicking through the command buttons and menus available to you to see what the main functionality and workflow of the system is.

When you come to a data entry form, these are the main things to keep in mind:

• When you open a form, sometimes your cursor will land by default in the first field. If you start typing or hit the delete key at that point, you will delete the data. So be cautious not to start typing until you are certain where your cursor is. If you notice you have changed data, type Ctrl-Z to “undo.”
• Move from field to field using the Tab key.
• You can usually tell what record you are on by viewing the Navigation Bar at the bottom of the form. It will say “Record 1 of XXXX” (however many records there are).
• Be aware that subforms that are nested within main forms will usually have their own Navigation Bar.
• You can search for a record by name or other text field in the Search space in the Navigation Bar.
• Data entry changes are usually saved as you go. You rarely have to click “Save” unless the database has been set up to do it that way for a reason.

Queries and Reports

If you are using an existing database, most of the time you will probably be doing data entry and running pre-formatted reports. However, you might be asked to answer a question, analyze some data, or produce a new report on the fly. Therefore, you should take some time to go over the sections above for both Queries and Reports.

Hidden Objects

Some tables and queries are hidden in the Navigation Pane if they are not needed for general day-to-day use. They may appear as “grayed out” or they may not appear at all.

To view hidden objects, right-click on the double arrow on the Shutter Bar to the right of the Navigation Pane. Select Navigation Options, then check the box for Show Hidden Objects.

EXTERNAL DATA

Access can export to many different file types, including Excel, Word, .txt., .csv, .pdf, and .html.

Exporting data to Excel lets you do things that some users find easier in Excel, like charts and graphs, or mail merges. It also removes the “danger” of working with live data in the database.

You can export tables or queries. However, once they are exported, they are no longer connected to the database in any way, so edits will not be reflected in the database.

You can also export reports to Word or pdf, but they will have limited or no ability to be edited.

To export any object to an external file type, select an export option from the External Data tab on the Ribbon and follow the prompts.

BEST PRACTICES

Understanding and Using a Split Database

When you build an Access database, everything you build is contained in one .accdb file. You refer to that as your database file. However, once you deploy your database in an office environment, and especially if you share your database with other users, it will almost certainly need to be a Split Database.

A Split Database is a database system comprised of TWO .accdb files, not just one:

• a “Front-End” file which houses all the forms, queries, reports, macros, and modules;
• and a “Back-End” file which houses all the tables.

The front-end and the back-end files are linked via the Linked Table Manager, which stores the file path location of the tables.

The Split Database model allows multiple users to share the database because the tables are stored in a central location, such as a shared server folder. Each user has a copy of the front-end on their desktop or user folder. The users all enter and manipulate the same central data from their individual user locations.

To split a database that you have built, click on the Access Database icon on the Database Tools tab on the Ribbon. Follow the prompts to designate a shared location for the back-end file.

The Split Database Wizard will move all your tables into a new file, and it will have a descriptive name such as “Study Abroad Participation_be.accdb,” where the “_be” signifies that it is the back-end file. All of the rest of your database objects will remain your original file.

The Navigation Pane will show you that your tables are now stored in an external back-end file, as shown below. The small arrows to the left of the table names show that they are now linked to an external file, rather than local. If you mouse-hover over a table name, it will show you the file path to the back-end file.

You manage the links between the front-end and back-end files using the Linked Table Manager (from the External Data tab on the Ribbon). Clicking the Linked Table Manager icon will open a series of prompts that will allow you to specify the folder location of the back-end file.

Every user must have a copy of the front-end file, with the tables properly linked to the back-end on the server. There will be no discernible difference in using the database once the tables have been moved. It simply allows multiple users to share the same data.

Sharing with Other Users

In a multiple user scenario, including a split database, it is considered best practice to close the database when you are not using it, and at the end of the day when you go home.

It is also helpful not to leave a database form open on one particular record. While you have that record open, it may be locked to other users. Always close a data entry form and return to the main menu when you are finished with a single record.

Backups

It is vitally important to back up the back-end of the database on a regular basis.

In order to make a backup, all users must exit the database. Open the back-end file. Go to the File tab. Select “Save & Publish” => “Save Database As” => “Backup Database” and follow the prompts. The prompts will save a copy of the database file to the default folder location, and will add today’s date to the file name. You can change these defaults if desired.

Compact and Repair

After you have backed it up, you should run a Compact and Repair process on the back-end file. The Compact and Repair command is located on the main File tab. This process will reduce the file size and clean up any small corruptions or errors that have occurred, sort of like a defragmentation process.

NOTE: It is also important to do an occasional backup and compact/repair of the front-end. It is possible for forms and macros to fail and cause errors, and you can solve this by importing “good” forms and macros from archived backups.

MICROSOFT TRUST CENTER

When you open an Access database, you will often see a security message warning you that “Some active content has been disabled.” Microsoft is concerned that viruses and other security issues may travel in macros, so it opens database files with macros disabled by default.

If you trust the database file and the source you received it from, it is generally safe to click “Enable Content.” If you don’t do that, things like command buttons won’t operate, since they are run by macros.

You can prevent this message from coming up each time you open the database by designating the folder it resides in as a trusted location. Note: these instructions apply to Access 2010. The individual steps are slightly different in other versions, but the trust settings will be essentially the same.

1. Go to the File tab => Options
2. Choose Trust Center => Trust Center Settings
3. Choose Trusted Locations and click Add New Location

   

4. Browse to the folder where your database is located and click OK.
5. If applicable, click the “Allow Trusted Locations on my network” check box.
6. Click OK to back out of the Trust Center settings.
7. Note: in some high-security environments, you could be prevented from choosing any server folder as a trusted location.

NEXT STEPS

• Build a practice database and start experimenting.
• Continue your training with additional online courses and reading.
• Learn about web databases and deploying to the web via Sharepoint.
• Learn VBA.

By Erika Williamson, August 24, 2015