Introduction to Microsoft Access

Lecture 1. CREATING DATABASE TABLES

A database is a tool that organizes and collects data. An example of database is a phone book which keeps names, addresses and phone numbers. To keep this data, a database management system, such as Microsoft Access, helps you collecting data organized into categories and related information. Access allows you to store, organize and manage your data, and retrieve to present it in many formats and reports. To create a database you must find first a purpose, and a design that accommodates all the data-processing and reporting needs. In our case we can begin with a simple phonebook. To open Microsoft Access we must click on the Start button located in the lower left corner of our screen, scroll the menu until we find our program and click on it. When you open Microsoft Access, the start screen gives you the option to create a new database, blank or from a template, or open an existing one. After selecting our option, a Navigation Pane appears on the left side of our screen. It allows us to open, copy and delete tables, forms and reports. Like every other Microsoft product, on the top of the screen there is the Ribbon, which contains tabs and group of commands. Access has already a first Table created, but if we need to create a new table, select the Create tab on the Ribbon and click Table, after this we need to create the Fields. Right click on the table and enter in Design View. Access will ask us to give a name to our table which we will call PhoneBook. A first field is already there, which is ID, we can leave it like that or change its name. Just select and edit the field if you want to. To add new fields just write their name under the Field Name column and select the right Data Type. ID should stay as AutoNumber. For FirstName and LastName we should select short text since 255 characters are more than enough. For the phone number I would suggest to use short text as well since we don’t need to make operations on that number.

Lecture 2. TABLES AND RELATIONS

In this lesson we’ll create relationships between tables and set the referential integrity. Let’s first open Microsoft Access, and create a new table which we will later call Books. We will insert the Title of the book and the price. We will also create another table called Authors with first and last name of the author. To connect these two tables we need to create a field in Books with a reference to the author. So we will create an AuthorID field with a numeric value, same as the other table. After saving the two tables we go to the database tools tab, then click on the relationships button. Click on the show tables button, and then double click each of the tables. Let’s drag the primary key field from the Authors table and drop it on Books so it brings up an edit relationships window showing the source field and the destination field. We want to click in the check box to enforce referential integrity then check both cascade update and cascade delete related records cascade update means if we make any changes to the AuthorID field it will make the same changes in any other table, if we didn’t check this we could potentially find authors in the books table that no longer existed. Those are called orphaned records. Cascade delete means if we delete an Author from the table it would delete that ID in the Books table as well leaving the field blank we could then assign a new Author to that Book we also have a join type to set click this button here there are three states only include rows where the join fields from both tables are equal include all records. Now you can notice the one-to-many designation indicating that each book can have only one author and each Author can write as many books as they want.

Lecture 3. Queries

As with many of the objects in the whole Microsoft Office platform, the easiest way to create a query is to use the wizard following through the steps it brings us during the creation.

If we take a look at the existing data tables, we can see that we already have a relationship between our two tables: Books and Authors.
Our goal is to retrieve an object which will look like a table, that will show both Books and relative Authors, and will give us the chance to view, add, modify and delete.

So, we can go over to the create tab and click on the Query Wizard button. That will start a new query window where we will choose to create a Simple Query. After selecting which fields we want to see in our query results, we have the chance to move individual fields back or forward and hide the ones we don’t want to see. In our case, we need the Title, the Price, and the Author fist and last name.

After selecting our options we can press Finish. That will show all the record set results for our query and we will see that MS Access has gone through both tables and matched the Books and the Authors.

This query will permit to add, modify and delete both tables in the same view.

Lecture 4. Forms

The next Access object that we are going to learn is forms. Form objects create the user interface for your database and allow you to populate and manipulate the data of the database.

You can personalize your database adding images and using different font and color schemes to make the user experience pleasing or to support the organization’s brand with a logo for example. You can also add controls to your data, like toggle buttons or dropdowns.

In this lesson, we will learn to use forms mainly to interact with data. First, we will select the table from which we want to create the form from, then on the Create tab of the ribbon, just click the Form button. Access will set up the form based on our selected table, and this form will show a single guest record in our table with all the fields. Then, at the bottom of the screen, we can see the record selectors from which we can page through all the records of our table.

Access also recognizes the relationship between our tables and created a sub-data sheet on the lower part of the screen displaying all of the related records. Also in the sub-data set, we can page through different records looking at how many records are related to the one that is displaying in the upper part. So, we can use forms to enter information into more than one table at the same time without manually insert one by one.

Lecture 5. Reports

Reports prepare the data contained in our database for printing output.

Access gathers the records from the tables or queries and adds our personalized formatting and page layout in order to organize the data in the way we want to show it and make it more legible.
On the Create tab, there are lots of different ways to create a report. Today we will learn the easiest way to create a basic report which is to use the one-click method on the Report button.

To start we will first click on the table or query that we want to build the report off of. Access will create the report and place us in the Layout view.
There is a dashed line around the outside edge which represents the printable edge of our future piece of printed paper. To personalize our sheet we can click on each of the columns and reduce the width to fit on the paper. Moreover, we can make design changes in the Layout view of our report, so we can make it easier to read.

If we want to see how this would look like on a piece of paper, just switch over to the Design tab, and then on the View button, click on the bottom portion of it, and switch our view into Print Preview.

Finally, close Print Preview and press the Save icon on the quick access toolbar so we will save our report.

Conclusion

Thank you for participating to this Introduction Course to Microsoft Access.
If you want to learn more I would suggest to read the following books.