Introduction to Microsoft Access 2003
Your Instructor: Craig Power
Lesson 01
Chapter 1

Welcome!

In this hands-on workshop, you're going to learn how to build and manipulate a Microsoft Access database.

For best results, I recommend that you print this and all subsequent lessons before working through the exercises. When you're done with each lesson, you'll definitely want to store it with the others in a folder or three-ring binder for future reference.

Before we can start working with a database application like Microsoft Access, we need to understand specifically what a database is and what it can do for you.

Microsoft defines a database as "a collection of interrelated tables, records, fields, forms, queries, reports, pages, macros, and modules."

That definition tells us that a database is not really a single entity, but a collection of many different interrelated components.

Once you understand the function of each component that makes up a database, you will be better able to understand the capabilities of a database program like Microsoft Access.

As such, I would like to begin this course by briefly describing each of the important database components that appear in Microsoft's definition of a database:

• tables
  
• records and fields
  
• forms
  
• queries
  
• reports
  
• pages
  
• macros
  
• modules
  

Once you have a basic understanding of what these components are and what they do, we will spend the rest of our time together learning how to assemble many of these components into a finished database.

Tables

Tables are used to store all of the information you might want to hold in a database.

• For example, suppose you wanted to use a database to track all the people you know. You would begin building that database by constructing tables filled with names, addresses, telephone numbers, email addresses, birthdates, and other similar pieces of information.

• If you wanted to use a database to keep track of products that you sell, you would probably start off by building tables filled with the names and descriptions of each of those products, as well as the quantity you have on hand, the wholesale price, the retail price, sizes, colors, locations, inventory numbers, and perhaps even photographs of the product.
  

Records and Fields

All of the information you stuff into a table doesn't just land haphazardly all over the place for you to sort through later. That wouldn't be good. Instead, the table carefully organizes any information you feed it into orderly columns and rows.

However, Microsoft does not use the word column or the word row when referrring to these organizing devices.

Instead, one row of information in a table is known as a record. And one column of information is known as a field.

For example, if you'd like to use an Access database to help you keep track of your customers' names and addresses, you'd need to first set up a table to hold this information.

As I recently mentioned, this table will organize that information into neat columns and rows.

One column or, better yet, field in your table will be devoted to nothing but customer names.

Another field in your table will be dedicated to storing the cities in which each of your customers live.

And a third field might hold nothing but customer phone numbers.

Additional fields in the table might hold street addresses, states, zip codes, job titles, and any other types of information you feel are relevant.

Each row or, more correctly, record in your table would hold all of the fields for one specific customer.

The first record would hold the name, street address, city, state, zip, phone number, e-mail address, and job title for your very first customer.

The second record would contain the very same information for customer number two, and so on.

To summarize, a database is made up, at least in part, of tables.

In turn, a table is made up of a number of individual records.

And each record is made up of fields.

If you'd like, you can think of a database as a big automated file cabinet.

A table would be like a drawer in that file cabinet.

A record would be like an individual folder in that drawer.

And a field would be equivalent to the contents of that folder.

Forms

Forms are used to simplify and enhance the data entry experience. While it is possible to type information directly into a table, it isn't pleasant. When working with tables, it can be difficult to see all of the fields you're working with at once. You usually have to do a lot of back and forth scrolling in order to move from column to column. This scrolling activity can slow data entry down significantly.

Tables also tend to show you several records at a time, and they make it far too easy for you to accidentally slip from one record to the next. This can lead to serious errors. I can't count the number of times I've caught myself entering information on the wrong row of my table!

Unlike tables, forms provide you with a delightful data entry environment. A data entry form can be asked to display all of your table fields on one screen, totally eliminating the need to scroll. Most forms will also display only one record at a time, reducing the risk that you'll inadvertently make changes to the wrong record.

Queries

Queries are used to extract or rearrange information in your tables in a way that makes better sense to you or helps you answer a question. For example, suppose you have a table of customer names, addresses, and other contact information. You can use queries to obtain any of the following lists:

• A list of customer names and addresses, sorted by ZIP code.
• A list of customer names and phone numbers, sorted by last name.
• A list of all customer names and phone numbers, but only if the phone number is in the 212 area code.

• A list of all customer names and addresses, but only if the customer lives in Western Australia.
• A list of all customer names and e-mail addresses, but only for customers who have actually supplied you with a valid e-mail address.

These are just a few examples of the types of queries that you could perform on such a table. In reality, queries can be used to filter and sort your tables in an almost infinite number of ways.

Examples of queries abound in everyday life:

• If you've ever shopped for a home, you probably remember asking a real estate agent to show you a list of homes within a certain price range, in a community with good schools, and with a certain number of bedrooms and bathrooms. The real estate agent was able to quickly provide you with this information because all he or she had to do was perform a query on his or her table of homes for sale.
  
  
• When you dial information and ask the operator to give you a phone number for a certain person or business in a certain city, the operator simply performs a query on a table of names, cities, and phone numbers to extract the information you need.
  
  
• When you make reservations for tickets with a certain airline on a certain date and a certain time between a specific pair of cities, the agent simply performs a query on a table containing airline schedules.
  
  
• When you bring your car in for service at the auto dealership, the mechanic will perform a query to see if you've had any prior service done on that vehicle at any dealership.
  
  
• When you call or visit your bank to see if a check has cleared, the teller will perform a query to see a list of all recent transactions related to your account.
  
  

Computer databases are ideal repositories for information because they can help us sort and locate the information we need far more quickly and efficiently than a box, a bulletin board, a desk drawer, a filing cabinet, or any other storage device. Queries are the primary tool we use to find and extract information from a database.

I can think of no better reason to use a database than the fact that a database allows you to query your data.

A database gives you the ability to sift through hundreds, thousands, or even millions of records to extract just the information you need--and all in a matter of seconds! Just try and do that with a paper-based filing system.

Reports

In general, reports contain the same information you'd find in your tables and queries. However, while tables and queries are generally optimized for display on a screen, reports are made to be printed on paper. Any time you need a summary of table or query data custom formatted for your printer, you'll want to create a report. Most reports also come with mathematical capabilities, which gives you the opportunity to include subtotals, totals, and other calculations on your printed copy.

Pages

A page is similar to a report, except that it will be formatted for display not on paper, but for display and distribution across the Internet.

Macros

Macros are used to automate tedious tasks that you frequently perform. You can create macros that will allow you to add records to a form, run a query, or create a report with little more than a click of your mouse.

Modules

Modules are sophisticated routines you can write to add decision-making capabilities to your database. Modules can also carry out complicated and repetitive procedures that would take us a long time to perform manually. A module is essentially a small computer program written in a language called Visual Basic. They're not used too frequently, and we won't talk much about them in this course.