A Complete Guide to MICROSOFT ACCESS, Slides of Computer Science

Download A Complete Guide to MICROSOFT ACCESS and more Slides Computer Science in PDF only on Docsity!

David Murray

MICROSOFT ACCESS 201 9

Tutorial and Lab Manual

Mr Oddo

v

Table of Contents

• Chapter 1 – Overview of Microsoft Access Databases ................................ Preface .....................................................................................................vi
• Chapter 2 – Design and Create Tables to Store Data ..................................
• Chapter 3 – Simplify Data Entry with Forms .............................................
• Chapter 4 – Obtain Valuable Information Using Queries ..........................3
• Chapter 5 – Create Professional Quality Output with Reports ..................
• Chapter 6 – Design and Implement Powerful Relational Databases …..…..
• Chapter 7 – Build User-Friendly Database Systems
• Chapter 8 – Team Building and Group Work Exercises .............................
• Chapter 9 – Supplemental Lab Exercises ..................................................9
• Appendix A: Relational Database Design Reference ...............................

Overview of Microsoft Access Databases 1

Chapter 1 Overview of Microsoft Access Databases

Introduction

This first introductory chapter will provide you with a broad overview of Microsoft Access and will introduce you to some general database terminology used throughout the book. This is accomplished by looking at Access databases from an end-user perspective so that you can familiarize yourself with what databases are, what they contain, and in general how they are used. Subsequent chapters focus on the technical details of databases, and by the end of this book, the topics come full circle in an attempt to tie together all of the concepts covered in this book. Each chapter begins with a brief textual overview of the material, followed by a Guided Exercise, which takes you step-by-step through a hands-on database example of the chapter contents. At the conclusion of most chapters, you have the opportunity to complete an Applied Exercise to check your knowledge and application of the material learned. Adhering to the chapter readings and paying attention to the “Guided Exercises” should prepare you to successfully complete the Applied Exercises, which are a true test of the material you have learned.

Have You Ever Used a Database?

If you are learning Microsoft Access for the first time, you may instinctively answer “no” to the question of whether you have ever used a database. I’m absolutely positive, however, that you have interacted with many databases, whether you realize it or not. To understand this, let’s examine what the term database truly means by listing some basic characteristics of databases.

• Databases store all kinds of data.
• Databases are either low-tech (manual) or high-tech (electronic).
• Databases are highly structured and organized.
• Databases are somewhat analogous to multiple spreadsheets that are linked together.
• Databases are designed to allow easy extraction and use of the stored data. So, if you have ever used a phone book or a library card catalog, you have indeed used a database. Also, many online search engines and e-commerce websites rely on databases for their proper functioning, so you likely have interacted with them. See, you may already be more familiar with databases than you realize! Although the concept of a database may still seem foreign and new to you, it is helpful to relate the material in this book to examples of databases you are familiar with. Doing so will often help you to demystify and better understand these strange things called databases.

2 Chapter 1

What Will I Find in a Microsoft Access Database?

There are five main things (objects) you will find in an Access database: tables, queries, forms, reports, and macros. Each of these topics will be covered in greater detail throughout the subsequent chapters. Take a minute to familiarize yourself with these terms. The Guided Exercise later in this chapter will show you examples of each of these.

1. Tables store the data in the database and are analogous to the foundation of the database. It is critically important that the tables be designed properly; they provide the foundation for building the remainder of the database.
2. Queries enable you to extract data from your database tables and allow us to answer questions we have about the data. Queries may combine data from multiple tables and manipulate data output through the use of expressions, formulas, and functions.
3. Forms are based on tables or queries, and they are used for entering data into the database in a user-friendly manner. They are also used for displaying data to the end user and can be used to create a menu system for the database.
4. Reports use data from a table or query and format the output in a professional-looking manner. Reports provide you with the ability to summarize, sort, group, and display the data in many different ways suited to the needs of the end user. Often, the purpose of a report is to provide a printed output of some data in your database.
5. Macros are small programs that you build into Microsoft Access; they perform some advanced operations, making the database more user-friendly and/or functional.

Where Can I Get Help?

F1. Simply, press the F1 key in Microsoft Access to use the built-in help anytime you need it. You should also use the numerous online resources that are available. Aside from the Applied Exercises at the conclusion of some chapters, nothing in this book is intended to be extraordinarily difficult or challenging to complete. If you find yourself stuck on a step of a Guided Exercise, try re-reading a few steps back to see if you either missed something or misunderstood the instructions. It is important to read each step carefully and follow the directions closely.

4 Chapter 1

5. To begin looking at examples of these database objects, double-click the table named Students to open it. You should see a few records of data already in the table.

6. Add a new record for yourself in the table. You can do this by entering your data below the last record in the table. Make sure you add your name, person number, email, phone number, date of birth, class, major, second major (optional), grade, and performance. As you navigate from record to record, Access automatically saves any data entry changes that have been made.

7. Find the record for Susan Ward and change her name from Ward, Susan to Ward, Sue. As you make this edit, you will see that it is very simple to make changes to data in the database tables.

8. Close the table by clicking on the LOWER X in the upper right-hand corner of the database. Clicking the UPPER X will close the entire database instead of just the table. It is a common mistake, and you will likely close the entire database accidentally at least a few times while working through this book.

9. The Main Menu should be visible once again. Click on the first menu button named Student Data Entry Form (open normally) to see an example of a form used for data entry.

10. View the various data records on the form using the navigation buttons at the bottom of the form. This form is built off of the Students table.

11. Click on the Student Name control so it is selected and then click the Find Record button to see if you can find your record in the database. You may have to adjust the search options on the Find and Replace window in order to find your record.

12. Click the Add Record button and add the following data. Student Name: Duffield, John Person Number: 9999 - 8888 Phone Number: (555) 867- 5309 Email: [email protected] Date of Birth: 8/20/ Class: FR Major: MG Second Major: CS Grade: B Performance: Satisfactory

13. Click the Close button on the form.

14. Open the Students table and verify that a record has been added for Duffield, John. You have just seen how forms are connected to tables, which allow you to edit, add, and even delete data in the table from the form. Close the table to return to the Main Menu.

15. Click the Students by Class Grouped Report button on the menu to open an example of a report. This particular report is based directly on the data in the MGS table. You will notice that the report provides a way to format the database output in a professional manner. This particular report groups the report data by Class (FR, SO, JR, or SR). Click the Close button to close the report and return to the Main Menu.

Overview of Microsoft Access Databases 5

16. In the Navigation Pane to the left, double-click the Query named ClassParameter. Type JR and click the OK button when you are prompted to enter a particular Class. This is a special type of query called a parameterized query, which allows the user to enter in different criterion each time the query is executed.
17. To see how the parameter works, close the query results (click the LOWER X), double-click ClassParameter again to reopen it, and enter FR. This time, different data results will be returned. Parameterized queries are powerful and quite easy to develop in Access, as you will learn later in Chapter 4. Close the query results to return to the Main Menu.
18. We will come back to the query in just a second, but first, open the Students table and switch the class for Duffield, John from FR to SR. Next, close the table, and reopen the ClassParameter query. This time enter SR when prompted for a Class. You should see that the record for Duffield, John is now included in this query result. This demonstrates an important point: each time a query is executed, it is connecting to the table to get the most recent data. Close the query results to return to the Main Menu.
19. Reports can be built from tables or queries. Click the Student Roster Report Parameterized by Class button on the Main Menu to display a report based on the ClassParameter query introduced previously. When prompted for a Class, enter SR. You are prompted to enter a Class, because every time the report is run, it also executes the query the report is based on. This always results in the query retrieving the most up-to-date data from the table and displaying those results in the report. Click the Close button to close the report and return to the Main Menu.
20. The final database object for you to explore are macros. The Main Menu that automatically appeared when you first opened the database was controlled with a special macro named Autoexec. Any macro saved with this name will be automatically executed when the database is opened. Often, you will use this macro to open your Main Menu so that the database users have access to their forms and reports.
21. Another example of a macro can be demonstrated by clicking on the Student Data Entry (open using macro) button on the Main Menu. Watch and read the pop-up windows carefully as they describe step- by-step what this particular macro is doing. Click the Close Form button to return to the Main Menu.
22. Before exiting the database entirely, click on the File ribbon in the upper left-hand corner of Access and click the Compact & Repair Database button. You will notice that the database closes and reopens quickly when this option is selected.
23. The Compact & Repair Database option is very important to know about in Access. Access database files are unique because they will never shrink in size and will only get larger! As you add and remove items to a database over time, it will become much larger and “bloated” in size. Running the Compact & Repair Database option will shrink the database to its smallest size. Although it is not necessary to do this every time when finished with a database, you will find it useful to run periodically.
24. Click the Exit button on the Main Menu to exit the database.

Design and Create Tables to Store Data 7

Chapter 2 Design and Create Tables to Store Data

Introduction

In the introductory chapter, you had an opportunity to explore and learn about databases from the end- user perspective. In addition, you discovered that Microsoft Access databases are composed of objects called tables, forms, queries, reports, and macros. If you have not completed the Guided Exercise in Chapter 1, it is strongly recommended that you do so before continuing in order to familiarize yourself with the database objects. This book devotes a chapter to each database object, with this chapter beginning a detailed look at tables. Here is the description of database tables provided in Chapter 1. “Tables store the data in the database and are analogous to the foundation of the database. It is critically important that the tables be designed properly; they provide the foundation for building the remainder of the database.” Throughout portions of this chapter, a construction analogy is used to describe databases and tables because designing and building a database shares similarities with designing and building a house. In addition to learning about the specific properties and settings for tables, some basic table design principles to follow will be introduced. You will begin by exploring what databases are used for, which gives insight into how they should be properly designed and constructed.

Decisions, Decisions, Decisions!

We constantly make decisions. Some are big, some are small, some are conscious, and some are subconscious, but every single one of them has some impact. In the same way, managers and employees of a business make many decisions every single day that affect the profitability of the business. Ideally, we want managers and their employees to make the best possible decisions that will benefit the business. One way to achieve this is to provide employees with timely and good information to help them with their daily decision making. By their nature, databases are well suited for providing information that supports decision making. As explained in Chapter 1, “Databases are designed to allow easy extraction and use of the stored data.” In other words, databases store all kinds of data, allowing us to later process the stored data into useful information. Once data is transformed into information, it can then be used to support decision making. The following diagram illustrates this principle of data being processed into information to be used in decision making. For example, a database with tens of thousands of records is essentially useless if we have to pore through the data records one by one. In contrast, when we summarize, filter, group, and/or sort the data, it then

Input

(Data)

Process

Output

(Information)

8 Chapter 2 becomes information because it can be used for business and managerial decision making. To be successful in today’s information age, businesses must act on good information in a timely fashion.

Garbage In, Garbage Out

To make good decisions, it is important to have good information based on good data. Allowing incorrect, incomplete, or inconsistent (bad) data into a database will usually result in incorrect information being generated. This is described with the basic computing principle of GIGO (garbage in, garbage out), which explains how computers and databases treat bad data. As much as possible, we want to avoid GIGO because the database does not automatically discern whether the data are “good” or “bad.” Designing database tables properly is one way to help ensure that only good data make their way into the database. As a result, we can be fairly confident that the good data can be processed into good information, and hopefully, good decisions will follow.

How Do You Properly Design the Foundation?

The most important part of any building is the foundation. It is extremely important that a structure have a good foundation; otherwise, the structure may fall, shift, crack, and ultimately fail. In the same way, a database must have a solid foundation or it too will fail. The tables of a database are likened to the foundation because everything else is built upon them. As much as possible, you want to create the tables only once and limit changes to them after work begins on the remainder of the database. Making drastic design changes to the tables may result in having to rebuild other portions of the database. Although this chapter does not cover the advanced topic of relational database design, we still need to keep in mind good database design principles. I cannot emphasize enough that proper database design is critically important to the success of a database system! Following these four principles will help ensure that your database has a solid foundation and will lessen the possibility of “bad” data making their way into your database.

1. Do not store redundant data in the database tables. Why? Data stored more than once in your database makes updating data difficult and often leads to inconsistent (bad) data.
2. Do not store calculated or derived data in database tables. Why? Calculated or derived data should not be stored because the values used in the calculation may change, which would also require an update of the calculated data. In addition, calculations based on time would eventually result in inaccurate data being stored in the table. Consider the option of storing the age or date of birth of an employee in a table. If you choose to store age, within a year, all the data will be incorrect in the table unless it is constantly updated! Instead, you should store date of birth, which does not change. But how then would you display the age of an employee on a report if only the date of birth is stored in the table? There are two options. First, you can always build a query to generate a calculated value instead of having to store it in a table.

10 Chapter 2

• Long Text stores up to 63,999 characters of text, numbers, and symbols.
• Number stores numbers that are used in mathematical calculations. You should not use this type to store data such as zip codes because zip code data are not used for mathematical calculations. Instead, use a Short Text data type for zip code fields and any other fields that have numbers in them but are not used in mathematical calculations (Social Security number, phone number, student person number, etc.).
• Large Number stores and efficiently performs calculations with very large numbers.
• Date/Time stores dates and times.
• Currency stores currency values up to four decimal places.
• AutoNumber automatically assigns a unique number to each record. This assignment can be done sequentially or randomly.
• Yes/No stores any binary representation of data such as Yes or No, True or False, or On or Off. A field with this data type appears as a checkbox in the Datasheet view.
• OLE Object stores or links to an object such as a Microsoft Word document, an image file, or a Microsoft Excel spreadsheet. It is recommended that you use the new Attachment data type instead of the OLE Object data type.
• Hyperlink stores website addresses, email addresses, and hyperlink data.
• Attachment stores various file formats in the database with the option of editing files within the database.
• Calculated is a special data type in Microsoft Access. It is used to display results of calculations or expressions based on other fields in the table. The results of a calculated field are read-only.
• Lookup Wizard is technically not a data type, but it does appear in the data type list because it easily enables you to create a drop-down list (combo box) of values that the user can select from in the table. This list of values can be typed in manually or dynamically based on another table in the database. This feature makes data entry easier for the end user and can help eliminate bad data from being entered into the database. Depending on the data type, each field also has specific properties that can be set. Although the following list of properties is not exhaustive, remember that you can always click F1 to get help about a specific property you encounter using Microsoft Access. Included here are some of the common properties used for a Short Text field.
• Field Size sets the maximum number of characters that can be entered for this field. For Short Text data types, the maximum value is 255 characters. Generally, this should be set as small as possible but large enough to accommodate potentially large entries of data.

Design and Create Tables to Store Data 11

• Format adjusts how the data are displayed as output. For Date/Time data types, this will adjust the date and time format used. For Number data types, this provides the option to select from Fixed, Standard, Percent, Scientific, or General Number formats. Different settings are also available for Short Text, Currency, and Yes/No data types.
• Input Mask creates a predefined structure into which the data for this field must be entered. Phone number and Social Security number fields commonly use Input Masks because the data follow a set structure every time.
• Caption provides an alternative field name to be displayed on all database objects that reference this field. For example, in a field named SSN, you may enter Social Security number as the Caption. Captions are used to display more descriptive field names to the end user.
• Default Value Automatically adds this set value for property to each new record in the table. This can be useful for reducing data entry when a large percentage of records use the same field data.
• Validation Rule is a property that works in conjunction with the Validation Text described next. The Validation Rule property enables you to set specific data entry rules that must be strictly followed. For example, you can force the users to enter data in a prespecified list or range of data.
• Validation Text is the text displayed in the error or warning message that appears when data entered into the database violate the corresponding Validation Rule.
• Required, when selected, forces the end user to enter data into this field before continuing to another record. Make sure this is enforced only when you need this field data 100% of the time. If there are situations when the data may not be available or do not exist, it is advised not to enforce this property. One final, but very important, option to set in the Design View of a table is the primary key. A primary key is a field or combination of fields that uniquely identifies a record in a table. Every table in a database must have a primary key established. To understand primary keys, consider an example of a database table containing a list of vehicles registered for on-campus parking at a university. The fields in the table are VIN, Make, Model, Color, LicensePlate, LicenseState, RegistrationDate, and VehicleType, and each record in the table is a separate vehicle. Given this example, the following are three ways you can set a primary key in the table.

1. Use an existing field that uniquely identifies each record in the table. Example: Use Vehicle Identification Number (VIN).
2. Create a new field to act as the primary key and assign it the AutoNumber data type. Example: Add a field named VehicleID and set the data type to AutoNumber.
3. Combine multiple fields to create a concatenated primary key. Example: Use both LicensePlate and LicenseState as a concatenated key.

Design and Create Tables to Store Data 13 Datasheet and Design View of a table. Click once more on the View button to switch back to the Design View of the Employees table.

8. Click on the EmployeeID field to select it. Select the Design ribbon and click on the Primary Key button. A small picture of a key appears next to EmployeeID; this indicates that the field has been set as the primary key.
9. Beneath the PhoneNum field, add a field named Email. Set the data type to Hyperlink. Set the Caption property for this field to Email Address. 1 0. Add another field named Salary and set the data type as Currency. Set the Validation Rule property for this field to >=20000. Set the Validation Text property for this field to Enter a salary above $20,000. 1 1. Add another field named HireDate and set the data type as Date/Time. Set the Format property for this field to Short Date.
10. Click the PhoneNum field to select it. Click the Input Mask property. A build button (small button with three dots) should appear to the right of the Input Mask row. Click the build button to open the Input Mask Wizard. Click Yes if prompted to Save the table. Click Yes if prompted with the message, “ Data integrity rules have been changed; existing data may not be valid for the new rules.” Click Yes if prompted with the message, “ Existing data violates the new setting for the ‘Validation Rule’ property for field ‘Salary’”.
11. Select the Input Mask for phone number and click Next. Click Next twice more and then click Finish to close the wizard. Notice the symbols and numbers used to create the input mask are now in the PhoneNum Input Mask property.
12. With the Input Mask property selected, Press F1 to open the help system and look at the different characters and symbols that can be used to create a custom input mask. Close the help system and return to the Design View of the Employees tables.
13. Click the State field to select it. Click the Default Value property and set it to NY. Set the Field Size property to 2.
14. Click the City field to select it. Change the data type from Short Text to Lookup Wizard.
15. Select the radio button labeled “ I will type in the values that I want.” Click Next.
16. Enter Amherst in the first row under Col1. Enter Buffalo below Amherst. Enter Clarence below Buffalo. Click Next. Click Finish.
17. Click the City field to select it. Click the Lookup tab in the Field Properties section.

14 Chapter 2

20. Set the Limit to List property to Yes as indicated in the preceding screenshot. This setting will only allow the data in the combo box to be added to the City field. Click on the General tab.
21. Click the City field to select it. Click the Required property and set it to Yes. Click the Allow Zero Length property and set it to No. Click the View button to switch back to the Datasheet View of the table. Click Yes if prompted to Save the table. Click Yes if prompted with the message, “Some data may be lost.” Click Yes if prompted with the message, “Data integrity rules have been changed; existing data may not be valid for the new rules.”
22. Add a new record for yourself in the table. You can do this by entering your data below the last record in the table. Make sure you add your correct first name, last name, phone number, and email address. The rest of the data record should be made up. Because of the design of the database, you may only choose a City value that is in the drop-down list.
23. You should notice and/or test the following items covered throughout this exercise:

• What is the Caption of the Email field?

  ---

• Can you enter 15000 in the salary field? What happens when you do?

  ---

• Is the City drop-down list working properly? What values appear in the list?

  ---

• What happens when you type Rochester into the City field for your record?

  ---

• Can you leave the City field blank?

  ---

16 Chapter 2

Chapter 2 Applied Exercise

This is your first Applied Exercise, so it is important for you to understand that this is designed to test your knowledge of what you have learned so far and to expand your knowledge a little beyond what is explicitly covered in this textbook. You will often find the F1 key to be helpful in accomplishing what needs to be done. Prior to working on this Applied Exercise, you must complete the Chapter 2 Guided Exercise. Once you have completed the Guided Exercise, continue using the Employees.accdb database for this Exercise. Do NOT use a new version of the database. You need to continue using the same file you have been working on already.

1. Add a field named Evaluation to the Employees table and use the Lookup Wizard data type to create a drop-down list of the following values: Excellent, Very Good, Good, Average, Below Average, Poor.
2. Using the drop-down list you just created, assign sample Evaluation data for every data record in the Employees table.
3. Create a new table named Titles; it should contain the fields TitleCode and TitleName. Both fields should be assigned Short Text data types. Enter the following three records of data as indicated in the table below. To create a new table, select the Create ribbon and click the Table icon. The default field named ID should be deleted so your final table looks like this. TitleCode TitleName MGR Manager REP Sales Representative EX Executive
4. Assign appropriate Captions and Field Sizes for the two fields. Assign TitleCode as the primary key.
5. Add a field named TitleCode to the Employees table and use the Lookup Wizard data type to create a drop-down list based on the values in the Titles table. Do not select the option “I will type in the values that I want.” The drop-down list should display both the TitleCode and TitleName in the drop-down list. To do this, make sure you uncheck the option for Hide Key Column as shown in the following screenshot.

Design and Create Tables to Store Data 17

6. Adjust the properties of the TitleCode field in the Employees table so that the end user can enter only data items from the drop-down list.
7. Using the drop-down list you just created, assign TitleCodes for each record in the Employees table.
8. Add a Validation Rule and appropriate Validation Text to the HireDate field in the Employees table. This rule should allow only hire dates on or before today’s date. Hint: Part of the solution is to use the Date() function in the Validation Rule. Date() will always return the current date.
9. Change your HireDate to today’s date.
10. Add a field named Resume to the Employees table that can store your resume in the table. Do not use the OLE Object data type.
11. Add your resume to the database table in your record.
12. Add a field named Employee Code to the Employees table. Assign this field as a Short Text data type.
13. Create a custom Input Mask for the Employee Code field that requires the entry of three letters, then a dash, then three numbers (e.g., ABC-123). The input mask should also display all of the letters in uppercase. Hint: Use F1 to search the Input Mask help as suggested in the previous Guided Exercise.
14. Add sample Employee Code data for each record in the table. Throughout the Chapter 2 Guided and Applied Exercises, you have had the opportunity to work with tables in the Datasheet and Design View. Chapter 3 will build upon this chapter and explain how forms are used to simplify the entry of data into database tables. Before continuing, make sure you are comfortable building tables and are familiar with how data types and field properties prevent GIGO in a database.