MS Access 2003

Introduction to Data Base

A database can help you enter data easily, find it quickly, use it to create labels or mailings, and summarize it in printed and online reports.

Benefits of Using a Database

A database is much more than just a list or table.
It gives you true command of your data, enabling you to retrieve it, sort it, analyze it, summarize it, and report results in moments. It can combine data from various files, so that you never have to enter information twice. It can even make data entry more efficient and accurate.
A key benefit of Database is avoiding the complications of multiple lists. Let's say that you're the secretary of a large hiking club. You have a list of recycling volunteers, a list of holiday party volunteers, addresses for newsletter labels, a membership list, and so on.
Suppose that a club member, who appears on a number of lists, changes her e-mail for the second time this year. With only a set of lists, you'd have the tiresome job of changing that information everywhere it occurs. With a well-structured database, you'd have to change it only once. The database takes care of everything else.
If you're just working with 10 or so items, then you'll probably want to create a simple list, perhaps as a worksheet in Microsoft Excel or a bulleted list or table in Microsoft Word.
If your data is more complex, or changes frequently, then an Access database gives you an advantage.
Relational Data base & MS Access

Relationships link data from individual tables to increase its usefulness.
Access creates relational databases, which means that data is stored in various separate tables by subject or task, but the data is related and can be brought together in ways that you specify.
Even though a club's database might store member contact information separately from its lists of recycling volunteers or holiday planning data, the database can pull all this information together whenever you want.
So, you could quickly print a list of who's volunteered to recycle newspapers this Saturday, along with their up-to-date addresses and phone numbers.
The two sets of data are relational, so that information in one set of data (such as Nancy Davolio's name on the recycling list) is associated with, or "knows about," the applicable information in the other set of data (Nancy Davolio's contact information).
To make the most of your database, you'll want to set up the tables of data to reflect the subjects and tasks associated with your data.
While planning your database, consider the scenarios in which people will be entering data, looking up data, or reporting data. A little forethought can go a long way.

Database structure
Objects are the most important parts of a database.
Access databases consist of objects. Later in this course, we'll describe the following four important objects in more detail:
Tables store your data in rows and columns. All databases contain one or more tables.
Queries retrieve and process your data. They can combine data from different tables, update your data, and perform calculations on your data.
Forms control data entry and data views. They provide visual cues that make data easier to work with.
Reports summarize and print your data. They turn the data in your tables and queries into documents for communicating ideas.

Practice
This practice session does not involve using Access or downloading any file; all you need is pen and paper or a word processing program to jot down your thoughts.

1. Remember a database.

Think about recent situations in which you've seen a database in use. Most likely, a store that you've visited recently uses a database to manage inventory, update customer information, and produce receipts or invoices. Or perhaps your company uses a database for managing customer or employee information.

2. Note database uses.

Jot down how people used the database: Did they look up your customer information? Scan price tags into the register or computer? Check inventory for availability of merchandise? Print receipts?

3. Imagine database activities.

If you're planning to create a database, jot down two or more situations in which you (or someone else in your organization) are likely to use the data, such as creating a monthly status report, reviewing sales data, sending out form letters, or entering student grades on assignments.

Tables
Tables organize your data. All databases in Access contain at least one table.

Figure 1 The row containing information about United Package is a record.
Figure 2 The column containing phone numbers is a field.
Tables store data, so they're essential building blocks of any database.
A database should have a separate table for every major subject, such as employee records, customer orders, shipping methods, or suppliers. Data should not be duplicated in multiple tables. Duplicating data is a common error, but it's easy to avoid if you structure your tables well.
Each table contains rows called records and columns called fields.
A record is a collection of facts about a particular person, event, CD, or other item of interest. For example, Nancy Davolio and her employment details are a record in an Employees table. Speedy Express and its contact information are a record in a Shippers table.
A field is a single kind of fact that may apply to each person, event, or other record. For example, Postal Code is a field in an Employees table. Phone is a field in a Shippers table.
Fields
[pic] If an existing field name isn't descriptive enough, you can rename the field.
[pic] A field's data type limits and describes the kind of information you can enter in a field, such as Number or Currency.
[pic] You use a unique identifier, called a primary key, for each record in your table.
[pic] Field properties are a set of characteristics that provide additional control over data.
The fields in your database have settings that determine the type of data they can store, how the data is displayed, and what you can do with the data.
For example, you can use the field settings to make sure that everyone enters every shipping date with two numbers for the month, two numbers for the day, four numbers for the year, and slashes in between: 03/09/2008. You can also require that the shipping date be at least two days after the entry date.
One important setting for fields is the data type, including number, text, currency (money), and date/time (shown together as one type in Access). The data type limits and describes the kind of information in the field. The data type also determines the actions you can perform on a field and how much memory the data uses.
Fields also have properties that control the details of information inside them, including a character length, a default value, and a validation rule that makes sure the data meets certain criteria. The properties make it easier to enter and manage data.
Records
An employee and his or her employment details form a record in the Employees table. This information should not be repeated in other tables or other records.

You may have heard that no two snowflakes are alike—this characteristic also applies to records in a well-structured database.
Each record in each table should be unique—in other words, you wouldn't have two identical records about Nancy Davolio in the same database. But what if you have two employees named Nancy Davolio?
To distinguish one record from another, tables can contain a primary key field.
The primary key is an identifier—such as a part number, a product code, or an employee ID—that's unique to each record.
The primary key should be a piece of information that won't change frequently.
If you want, Access can assign a numeric primary key that increases by 1 each time you add a record to a table. This number continues to be associated with this record, even if you add and delete other records entered before this record in your database.
If the primary key is a number, how will people recognize the record? Don't worry, the database can associate each primary key with a friendly name, such as an employee's name. That way, you can work with familiar information, even though the underlying table is storing a number.
[pic] Employee ID appears in both tables—as a primary key...in Employees Table.
[pic] ...and as a foreign key… in Orders Table.

A primary key separates similar information and makes each record unique. It also brings information together. You relate one table to another using a primary key. This is how tables share data, and how you can avoid repeating information in both the tables.
Primary keys allow you to tap into the power of a relational database, instead of working with a fistful of repetitive lists that are hard to maintain and cannot cooperate.
When tables relate, the primary key of one table becomes a foreign key of the other table.
Let's say that you have an Employees table and an Orders table. The Employee ID numbnoer is the primary key for the Employees table and a foreign key for the Orders table. The Orders table has its own primary key, the Order ID number.
When Nancy Davolio takes an order, her Employee ID number is entered into the Orders table. This ID number refers to the details about Nancy in the Employees table, so there's no need to repeat data about Nancy (such as her phone extension) in the Orders table.
For more details about primary keys, foreign keys, and table relationships, see "Table that data."

Queries
Once you get your data into your database, how do you get the data out again?
A database becomes really valuable when you use the data it contains as a resource to answer questions or perform tasks.
Whether it's comparing weekly sales figures, tracking packages, finding all the members of your club who live in Texas, or a multitude of other scenarios, a query can help you retrieve data and put it to good use. Do you have questions that you want to answer with your data?
For example:
Which of your employees live in the United Kingdom?
How many regions had more than $250,000 in sales last month?
Which schools had the highest absenteeism rate?
Queries can answer those questions by assembling stored data from your database, or by performing calculations with the data to provide further information.
To answer questions, queries retrieve, filter, sort, and assemble data on command. Another important power of queries is to combine the data from several tables into a single view.
When a query finds data and shows it to you, it can also process that data according to your instructions. A query can perform calculations using the data: What is the sales total minus shipping costs?
A query can also remove data: Delete names of members who have not paid dues for 24 months. You should use caution when executing queries that change data, and consider backing up your data first.
Queries can select items from your data and make it more useful. Queries let you pull just the data you need out of a database so you can perform tasks and get questions answered.
With queries, you can retrieve, combine, reuse, and analyze your data. You can use queries to retrieve data from multiple tables, or as a source for forms and reports.
When you work with queries, you need to keep in mind the questions that you want your data to answer. The better you state the question, the more precisely you can define the query.
Here are some examples that will help you think of others.
A corporate sales and products database ➢ What's the company's most popular sales item in the United Kingdom? ➢ What would we gain and lose if we dropped our slowest-moving product line? ➢ What item costs the most to make?
A personal media collection database ➢ How many of my DVDs are on loan to my friends? ➢ Which CDs contain my all-time favorite tunes?
A sports league database ➢ Which team had the highest total score in all games last year? ➢ Who ordered team jerseys this year, and what sizes do they need? ➢ What questions do you want your data to answer?
Before you start thinking about the nitty-gritty of creating a query, it's a good idea to think through the questions you want to answer, logically and in detail.
How do you want to choose your data? Do you want, for example, the 10 most of something, all items above or below a certain amount, or all employees who live in a different county from the location of your office?
What database fields do you need? For example, for a list of your company's five best-selling beverages, you might want beverage names, manufacturers, and suppliers, but you probably don't need the states they're bottled in.
Once the data is returned, do you want to do more with it? Do you want to multiply sales quantities times price, or view the impact on sales figures of a recent discount?
It can help to put your question into full sentence form, as in this example:
"I want to know the 10 best-selling products in our Midwest region. I need to know the product names, the product IDs, and the department that produces each product."
Don't forget: If at first you don't succeed, if your query doesn't get just the results you need, you can tinker with the query later.
Whenever you run a query, it checks for the latest data in your database. The data returned by a query is called a recordset.
You can browse through the recordset, select from it, sort it, and print it. Typically, the recordset you produce with a query isn't saved, but the query structure and criteria you used to get the results are saved. You can check for the most recent data again at any time, simply by rerunning the query. Queries have titles, so you can easily find them and use them again.
Because Access saves query structures and criteria, if you frequently need a certain set of information, such as sales during a specific year, you can avoid hunting down that data each time, just by rerunning the query. Chances are, you have better things to do with your time.
You can create and save multiple queries to retrieve data in different ways. Queries are easy to revise as well, so if you don't set them up right the first time, or if your needs change, you can easily change your criteria. You can even use one or more queries as the source of data for another query, increasing your efficiency as you select more and more precisely to get just the data you want.
Types of Queries
|Query type |Description |
|Select query |Retrieves data from one or more tables and displays the recordset in a datasheet. This is the most common type of|
| |query. |
|Parameter query |Prompts the user to enter values that define the query, such as a specified region for sales results, or a |
| |specified price range for houses. |
|Cross-tab query |Arranges a recordset to make it more easily visible, using both row headings and column headings. |
|Action query |Creates a new table or changes an existing table. |
|SQL query |An advanced query that is created by using an SQL statement. |

Access provides many different types of queries to meet many different needs for data.
We'll briefly describe most of the query types, although in this course we'll focus on the most commonly used query type, the select query.
A select query retrieves data from one or more tables and displays the recordset in a datasheet. You can also use a select query to group data, and to calculate sums, counts, averages, and other types of totals.
As with forms and reports, Access provides two basic ways to create queries: by using a wizard, and in Design view.
The wizard, also known as the Simple Query Wizard, gives you a head start in setting up your query's structure by making some arrangements for you.
In Design view you have total control when creating a query. You drag the fields you want to a grid, and then you enter the criteria for selecting the data to be retrieved.
You can also create your query using the wizard and then refine the query in Design view to get just what you need.
The Simple Query Wizard begins by prompting you to select the database fields you need. You can select fields from tables. You can also select fields from preexisting queries.
For example, if you were retrieving a list of people from an Employees table, the wizard would prompt you to choose fields to include in your query, such as each employee's name, manager, and extension.
The wizard also enables you to group and summarize data, which isn't necessary for simple queries but can help you analyze more complex recordsets.
After you create your query, the wizard can run it, or the wizard can take you to Design view. In Design view you can specify criteria and refine your query. For example, you could specify in Design view that the retrieved list show only employees in the Marketing Department and that it be sorted by the employees' last names.
You can create and modify queries in Design view.
[pic] Design view shows the tables or the existing queries that you can base your query
[pic] ...and a grid where you can add criteria and modify your query.
In Design view, you begin by choosing the tables or existing queries that contain the fields you want to use.
Then, you select and drag those fields to a grid. The fields can come from just one table, or from multiple tables. In this course, we focus on working with one table.
After you have added your fields, you can specify criteria and other settings, such as whether to sort the results.
Note You can change the fields used in a query after you have made your original selection. For details on making changes, see the Quick Reference Card.
Whether you create your query with the wizard or in Design view, testing it in Design view lets you easily tweak your query if you don't get the results you want.
Specify the Fields
[pic] In Design view, you can double-click...
[pic] ...or drag fields...
[pic] ...to add them to the grid.
The fields you specify for a query control the data that the query retrieves.
For example, in a list of the best-selling products, you might want to see the name of each product, its profit margin, its sales figures, and its distributors. You might not need to know information such as the product's manufacture dates.
You can specify the fields you want whether you use the wizard or Design view.
The wizard prompts you to choose the tables or queries you want to use, and then which fields you want to use.
In Design view, you also start by choosing the tables or queries you want to use. Then you add each desired field to your query by dragging it from the table summary displayed above the grid. This action will populate both the Field and the Table boxes in the query.
You can also double-click fields to select them, and they will appear in the grid in the order in which you click them.
Specify the Criteria / Condition
Criteria help focus data by specifying precisely what you want.
For example, if you're hungry for lunch, you might want to display only the recipes for lunch items.

To do this, you would specify a criterion that limits the recordset to recipes that say "Lunch" in the WhichMeal field. Recipes that have any other data, or no data, in that field do not fulfill this criterion and so are not retrieved by this query.
To set a criterion, you type the text or value that focuses the query into the Criteria row in the query grid. For example, to limit the recordset to lunch recipes, you simply type Lunch, surrounded by quotation marks, into the Criteria row under WhichMeal.
Criteria can be as simple as "Lunch" or more complex. Complex criteria can include multiple conditions (for example, all lunch recipes that also use chicken in their ingredients). Or you can compare values (for example, all recipes that have more than 200 calories per serving).
What If the fields are not visible in the Query Or you don’t want to make few fields visible?
You can specify which of the fields that you use in your query are displayed in the results.
Sometimes you'll prefer not to display all the data that a query retrieves. The information may not be necessary, or you may not wish to advertise the criteria that you used in the query.
For example, if you were retrieving just your recipes for lunch dishes, you'd want to display each recipe name and its description. But you wouldn't need to display the WhichMeal field, since all recipes returned by that query would be for lunches. If you titled your query something like Lunch Recipes, it would be redundant for people to see the WhichMeal field.
The Show box lets you decide whether to display each field used in your query. You can display or not display a field, whether or not you have specified a criterion for that field. You can check or clear the Show box for each field any time that you use the query. This feature also enables you to reuse a query but to display only some of the results, if you need only a subset of the recordset on a particular occasion. You can show two fields instead of a dozen, without having to create a new query for the purpose.
Run the Query
[pic] In Design view...
[pic] ...you can run a query...
[pic] ...and view its results.
You can test a new query in Design view. Just click the Run button [pic] on the Query Design toolbar. This will switch you to the results view.
If you don't see the results you want, click the Design button [pic] to return to Design view for more tweaks.
To run a query, all you need to do is to open it by double-clicking it in the list of queries, which appears when you click Queries under Objects in the Database window.
To better display your query results, you may want to create a form; to better print your query results, you can create a report. For more information, see the courses "Forms follow function" and "Reports I: Show off your data."
A parameter query displays a dialog box when it runs, prompting the user to enter information to use as criteria for the query. You can design a parameter query to prompt for more than one piece of information; for example, you can design it to prompt for two dates. Access will then retrieve all data with values between those two dates.
A cross-tab query arranges a recordset to make it more easily visible, using both row headings and column headings. Data can be seen in terms of two categories at once.
An action query creates a new table or changes an existing table by adding data to it, deleting data from it, or updating it. Because an action query is so powerful, actually changing table data, you should consider backing up your data before running an action query.
An SQL query is created by using a statement in Structured Query Language (SQL). SQL is an advanced way to query, update, and manage relational databases. Access can create the SQL statement for you when you create this type of query, or you can create your own SQL statement.
Forms
Databases are made to be used. All sorts of people need to add and review data, quickly and effectively. The data must remain reliable—and reliably controlled. You can enhance the value and performance of your database by adding forms created and customized for entering and viewing data. Access provides the tools to make working with forms quick and easy.
Forms enable people to enter or view data in your database easily. You might consider forms as windows through which people can work with data.
Forms control and simplify data input. As people enter data into a form, it is saved in an underlying table.
Forms make data from a table or a query easier to understand by presenting it in visually appealing designs. Forms can also provide a start-up screen, with easy ways to launch database tasks.
Forms provide drop-down lists, instructions, navigational controls, and graphics to help users work with your data. One way and another, forms make data friendlier.
[pic] Graphic elements, such as lines and rectangles, are stored in the form's design.
[pic] Data comes from the fields in the underlying table or query.
[pic] A calculation comes from an expression, which is stored in the form's design.
[pic] Descriptive text is stored in the form's design.
Why use Forms?
An effective form speeds the use of your database, because people don't have to search for what they need. A visually attractive form makes working with the database more pleasant and more efficient. Forms are convenient because they are so adaptable to so many particular purposes. You can create forms, for example, to:

➢ Note students' grades

➢ Track inventory items

➢ Enter new employee names

➢ Review weekly output numbers

➢ Update customer addresses

➢ Create sales or service orders

➢ Open other forms or print database reports

[pic] The structure of data in a table…
[pic] …is reflected in the structure of a form.
When you create a form, there are a couple of key purposes that you should keep in mind. Your form is good at its job if it achieves clarity and control.
Databases store data in tables, often very large tables. Although it's possible to enter or read table data directly, this process is likely to be cumbersome. The larger the table, the harder it is to be sure that the data is in the right field (column) and record (row).
Forms help people to focus on just what they need when they enter or view data. You should design a form so that it is easy to read and understand. You can include labels to describe data, and instructions for entering it, right next to each working area.
Forms let you select, from the many fields on a table, just those that you want the users to focus on. This selectivity also means that you don't have to expose all the data to everyone who works with it. You may not want the person entering names into the employee table to see every employee's salary, for instance.
[pic] Data may be difficult to locate in a large table.
[pic] A form makes data easier to work with.

Types of Forms
Forms serve a variety of functions with various designs.
Although most frequently used to enter or view data, forms have several other valuable uses. Since form follows function, each of these uses has its own type of form.
Switchboards
You can create a form that appears as a switchboard when a user opens a particular database. The switchboard provides a friendly and controlled way to open forms, reports, and other objects. It guides users to the actions that you want them to perform, and screens parts of your database that you don't want people messing with.
Messages
Forms can display messages about a database. A form can provide instructions for using the database, or assistance for troubleshooting any problems.
Subforms
A subform looks like a part of another form. In fact, while the larger main form connects to one table, the subform connects to a different table. A user can enter data into one form and view related data in the other, without ever knowing that they are actually separate and equal.
Creating a Form
Access gives you three main ways to create a form: with the Form Wizard, with an AutoForm, or in Design view. Once you understand all three ways, you can choose the method or methods that will be best for your purposes.
By using the Wizard The Form Wizard asks you questions, and your answers determine the form it creates.
The Form Wizard is best when you want to be guided step by step through the process of creating a form. The Form Wizard asks you questions and creates a form based on your answers.
You will need to tell the wizard:

➢ Which table or query the form data comes from.

➢ Which fields to use on the form.

➢ Which form layout to apply.

➢ Which visual style to apply.

To start working with the Form Wizard, you would click Forms under Objects in the Database window, and then click New on the Database toolbar. In the New Form dialog box, you would click Form Wizard to select this method.
By using Auto Forms

Figure 1 A columnar AutoForm Figure 2 A tabular AutoForm
AutoForms are best when you don't need to make many choices. If your new form will contain all the fields from a table or query, and you don't need much control over the form's appearance, an AutoForm is simply perfect.
Unlike the Form Wizard, an AutoForm doesn't ask you questions or offer you choices. Creating an AutoForm is a two-step process. Pick a type of AutoForm, pick a table or query, and Access does the rest.
There are several types of AutoForm, but the two shown here are all you need to know about now. AutoForm: Columnar shows records one at a time, with each field on a separate labeled line. AutoForm: Tabular shows all records at once, with each field shown for each record, and field labels at the top of the form.
To start working with an AutoForm, you would click Forms under Objects in the Database window, and then click New on the Database toolbar. In the New Form dialog box, you would click one of the AutoForm options to select it.
By Using the Design View
[pic] The toolbox in Design view contains controls, such as text boxes and labels, to add to your form.
[pic] The Design view gridlines and dots organize the area where you insert and arrange controls.
Design view is the best way to create a form when you want full control and complete freedom. In Design view, everything is up to you.
The challenge is that you're on your own, without the prearrangements of the Form Wizard or an AutoForm. However, you can also create a form by either of those methods, and then change its details in Design view.
To work with a form in Design view, you choose items from the toolbox. These items, such as check boxes, pictures, and labels, are called controls. You can set their properties and format them to look as you want on the form. For details, see the course, "Take the controls."
In Design view, you can move the controls around, like rearranging pictures on a wall. For example, you may want to group together the text boxes for address, city, state, and ZIP code—but on two lines, or three, or four? You can easily try them all and pick the best.
To see the form as it will appear when someone works with it, you can open it in Form view, and keep switching between Form and Design views until the form suits you completely.
To start working in Design view, you would click Forms under Objects in the Database window, and then click New on the Database toolbar. In the New Form dialog box, you would click Design View to select this method.

Parts of a Form
Understanding the parts of a form is the first step in adapting it to your needs.
Once you know how to create forms, it's time to start making those forms look and act the way you want. To do that, you need to know your way around the parts of a form.
All forms share an underlying structure and contain a shared set of working items. Once you are familiar with these, you will be able to see how a form is put together. And what you understand, you can change.

Controls
[pic] Data from a table
[pic] Calculation result (price after discount)
[pic] Instructions and identifications
Controls are the parts of forms that most users see and work with. Controls can show data or accept data entry. They can perform calculations on data. They can display messages. They can also add visual effects, like lines or pictures, to make forms easier and more interesting to use.
Some controls are bound. A bound control is attached directly to a specific field in a selected table or query. The direct attachment means that a bound control can add, change, or display live data. (This lesson will discuss controls bound to fields in tables. To learn more about queries, see the course "Queries I: Get answers with queries.")
When someone enters or changes data in a bound form control, that new data or changed data immediately enters the table. Data viewed in a bound control will change in the form whenever it changes in the table.
Controls such as decorative lines and instructions are not bound to table data, because these controls stay the same. Calculated controls are not bound, because values that can be calculated do not need to be stored in a table.

Handles show selections and help you resize or move parts of a form. You select controls by clicking them or by dragging a rectangle around them.
[pic] Handles appear when a control is selected.
[pic] An open hand appears when a control can be dragged.
[pic] The Select Objects button selects controls inside the dragged rectangle.
To change a control, first you would need to select it in Design view. That’s easy: You'd just click it. Small black square handles would appear around the selected control.
Applying a change to several controls at once saves time and ensures that the change is applied to all of them in the same way.
To select several controls at once in Design view, you would hold down the SHIFT key and click each control you want. After looking at the handles to confirm the choices, you'd make your change. The controls remain selected after a change, in case you want to continue working with this selected group.
Tip You could also select a group of controls using the Select Objects button [pic]in the toolbox. You would click that button, click in the form, hold the mouse button down, and drag a rectangle around the controls you want. When you release the mouse button, handles appear to show your selection.
Changing the Tab order
The tab order determines how people move through the form if they press the TAB key. Some people prefer or need to move from control to control this way.
Some people use the TAB key to move through a form, from one control to another, as they enter or view data. When you design or customize a form, you can change the preset tab order of movement from control to control.
It's important that the tab order be logical and easy to work with. It's often simplest to have the order run from left to right and from top to bottom. If your form contains groups of controls, however, or if it is based on a handwritten form, your best tab order may have a different pattern.
You could change the tab order in Design view. You would click anywhere in the form, and then on the View menu you would click Tab Order to open that dialog box. Under Section, you would click the name of the form section for which to change the tab order. Dragging the names of controls up or down in the Custom Order list would set a new tab order.
Tip If you wanted Access to create a left-to-right and top-to-bottom tab order, you would click Auto Order in the Tab Order dialog box.
Sections in a Form
[pic] Form Header often contains general information.
[pic] Page Header is used for printing.
[pic] Detail usually contains most controls.
[pic] Page Footer is used for printing.
[pic] Form Footer often helps with navigation.
When you open a form in Design view, you can see the sections by which the form is organized. All form sections can contain controls.
The Detail section appears in all forms, usually containing most of the controls. Controls in which people view or enter data, such as text boxes and list boxes, are typically here. But the Detail section can also contain controls that don't change, such as labels and instructions.
The Form Header section, at the top of the form, is a good place for the form's title, a company logo, or a general message about the form.
The Form Footer section, at the bottom of the form, often contains navigation information or reminder text.
The Page Header and Page Footer sections are useful in organizing complex forms designed to appear as multiple pages. Page headers and page footers appear on every page of a form, but only when the form is printed or previewed for printing.

[pic] Section selector
[pic] Section bar
[pic] Section background
There are three ways to select a section of a form in Design view.
You could click the section selector, the box in the vertical ruler to the left of the section name. (The rulers help you keep track of a form's size as you work on it. A form should be not too big, not too small, just right for the users.)
You could click in the section bar, the rectangle with the section name in it at the top of the section.
You could click anywhere in the background of the section. (Anywhere not on a control, that is.)
When a section is selected by any of these methods, the section bar will be highlighted.
Adding header & Footer
One sequence of actions (Design view, right-click, choose command) will either add or delete a header and footer.
[pic] Form in Design view
[pic] Command to add or remove header and footer
You may find that the form you are working on does not have Page Header and Page Footer sections. The form may even lack the Form Header and Form Footer sections. To add header and footer sections, for pages or for the entire form, you would need to be looking at the form in Design view.
After right-clicking the form at any of the points that can be used to select a section, you would choose the relevant command from the shortcut menu to add the header and footer sections for the page or the form. You could then add controls, for instance labels or text boxes, to the header and footer sections.
Note You could remove a header and footer by the same process, which deletes or adds depending on whether a header and footer are present or not. If you remove a header and footer, Access deletes any controls they contained.
Form Properties
[pic] Double-clicking the form selector...
[pic] ...will open a dialog box where you can determine the overall look and behavior of the form.
Forms also have properties, associated with the entire form, that affect the user's experience of the form. You can select or change these properties to determine how the overall form looks and behaves.

For example, you can decide whether the form has buttons at the bottom to move back and forth through the records. Does the form have buttons at the top to minimize, maximize, and close it? Can the user resize the buttons? It's your choice, and so is the form's background.
To set or change properties, you would open the form in Design view and double-click the form selector, the box where the rulers meet. A dialog box would open and show you settings from which you could choose.
To see details of any property, you would click in the box to the right of that property's name, and then press F1.
Navigation Buttons
[pic] First record
[pic] Previous record
[pic] Record number
[pic] Next record
[pic] Last record
[pic] New record
We mentioned the buttons at the bottom of a form that enable users to move back and forth among the records. Access automatically adds these navigation buttons to every new form.
The navigation buttons aren't visible in Design view, but they appear whenever you switch to Form view, and they'll appear when anyone uses the form to view or enter data.
Access adds these buttons automatically because they are so convenient. They let users move quickly and easily back and forth among the records, or to the end of the existing records to create a new record.
Customizing the appearance of the Form
It's easy to change the appearance of your form.
A well-designed form actually improves function, by making it easier for people to enter and view data. An attractive form will attract more users, for longer times, than a dull one. See how your forms can become more interesting, and more pleasant to use.
Backgrounds, borders, colors, and text make a form easy for users to understand and work with.
You can select visual effects for a whole form, for a section of the form, or for individual controls. Backgrounds, borders, colors, and text are among the many visual effects you can customize in a form.
To use a picture as the background of a form, you would open the form in Design view, and then double-click the form selector (the box where the rulers meet) to display the form's property sheet.
You would select the Format tab, and then scroll down to Picture and click that word or the box next to it, causing the three-dot Build button [pic] to appear. Clicking that button would open the Insert Picture dialog box, where you could choose the image for the background.
To change the look of a form section, you would open the form in Design view, right-click in the section you want to change, and then click Properties on the shortcut menu. Selecting the Format tab would show a list of choices, including background color, height, and several other settings.

Access gives you so many ways to select and change the appearance of controls on a form, we offer a separate course entirely about controls. When you have finished learning about forms, you will be ready for "Take the controls."
Resizing the Sections in the Form
[pic] To change the height of a section, drag the pointer up or down.
[pic] To change the width of a section, drag the pointer left or right.
[pic] To change both height and width, drag the pointer diagonally in any direction.
If you change the number or the size of form controls, you may want to resize the form sections also, to suit the controls they contain and make the form easier to work with.
You can increase or decrease the heights of form sections individually. If you change the width of any section, however, you change the width of the entire form.
To change the height of a section, place the pointer on the top edge of the section bar and drag the pointer up or down.
To change the width of the form, place the pointer on the right edge of any section and drag the pointer left or right.
To change both the height of a section and the width of the form, place the pointer in the lower-right corner of the section and drag the pointer diagonally in any direction.

Auto Formatting
AutoFormat is a quick and easy way to apply preset formats to a form.
Would you like your form to have a blueprint effect? A fancy, blended style? Or an international look and feel? With AutoFormat, touch-up is practically a one-touch process.
Access offers several predesigned AutoFormats. Each contains a style and color for fonts and borders, plus a background effect for entire forms.
You can apply an AutoFormat to a single control, several controls, a section, or the entire form. In Design view, you would select what you want to change, and then click AutoFormat on the Format menu and make your choices.
You have seen these predesigned formats earlier, as the options for creating a form using an AutoForm. If you apply an AutoFormat to any form, that AutoFormat will also become the format for all new AutoForms, until you make another change in AutoFormat.
Reports
A report converts data into a formatted document.
Do you routinely report sales figures? Provide a catalog of your inventory? Print out labels for CDs, DVDs, and videos at home? Produce invoices and receipts?
Reports convert data into documents. Reports come in various shapes and sizes, but they are all designed to present your data in print. Reports provide methods to format the printed appearance of your data in the ways that are most effective for your purpose.
Using reports, you can group your data, perform calculations on it, and add headings and other formatting to make it more meaningful and easier to read.
After you've created a report, you can save its format, so that it looks the same each time you print it, however the data changes.
For meetings, for mailings, for making your point, there's nothing better than clear, elegant data. With reports, you can organize data to make it attractive and informative, on a printed page or on a computer.
Whether you are sharing a weekly status report with your team or sending a product catalog to your customers, reports draw attention to your data and make it work for you.
Reports can make your data more useful.
Reports provide many ways to organize, categorize, and even summarize your data. They help you show your data to best effect, and they offer forms of presenting data that may be new to you.
As with other parts of Access, the first question you need to consider is, what do you want the report to do?
Reports can summarize and group data to give an overview.
Reports range in complexity from a simple list—such as a project status report or a weekly sales report—to a detailed product catalog.
With reports you can also: ➢ Prepare a customer invoice. ➢ Create mailing labels. ➢ Produce a directory, such as an alphabetical list of employees with their contact information.
Present a data summary, such as sales grouped by region.
Reports are often used to present a big-picture overview, highlighting main facts and trends.
Grouping data and sorting data to make it more visibly meaningful are key benefits of reports. Reports let people take in the big picture at a glance.
So a sales report might group sales by region, by salesperson, and by quarter, showing subtotals as well as a grand total. A club newsletter could show how many people have been members for 5, 10, or 20 years; report meeting attendance by month and overall; and list how many members live in which cities.
Although reports enable you to dress your data for success, grouping and presenting data in many different ways, they don't alter the underlying data in the tables in your database.
[pic] Logo (decorative element)
[pic] Separator line (decorative element) & report title (text box)
[pic] Data (text & graphics) from database grouped by headings
[pic] Separator line
Report is made of controls. Some controls, such as text boxes, display data. Other controls are decorative elements, such as separator lines or company logos.
A report may be made of pages and headings. A page may contain several headings grouping data, or one heading may include data filling several pages.
A report is made of data. Data in a database consists of fields and records. A field, shown in a table as a column, is a single category of fact that may apply to each record. A record, shown in a table as a row, is a collection of facts about a particular person or thing. So the postal code for employees is a field, while employee Nancy Davolio is a record.
Creating Reports
There are three basic ways to create a report: by using an AutoReport, by using a wizard, and in Design view.
This lesson will show you how to choose the method that works best for your situation.
To create a report, you use data from one or more tables or queries. To use several tables, you would first create a query to retrieve data from those tables.

Auto Report
An AutoReport is the quickest way to create a report, but it gives you the least control over the report's structure and appearance.
An AutoReport automatically contains all the fields in its data source, whether table or query. Its structure is either columnar or tabular.
AutoReport: Columnar Each field appears on a separate line with a label to its left. Only one record is visible at a time.
AutoReport: Tabular The labels for all fields appear on one line at the top of the report, with all records visible below them.
An AutoReport works best in two situations: when you need a quick overview of data from a table or query (say your boss needs a printout for a meeting that starts in 10 minutes), or when you want a quick head start on a report that you'll customize later.
[pic] The same data presented as a columnar AutoReport...
[pic] ...and as a tabular AutoReport.
Report Wizard
The Report Wizard asks you questions and creates a report based on your answers.
This method is best when you want to be stepped through the process of creating a report.
The Report Wizard asks you which tables or queries you will base your report on, and which fields to use from those data sources. It asks whether you want to group data, & how you want to sort & summarize it.
After asking about the data, the Report Wizard asks about the appearance of your report: layout, orientation, and choice of six visual styles. Finally it asks you to give the report a title, and then it creates a report based on your answers.

Design View
While an AutoReport and the Report Wizard are great ways to get started, you may find you want to customize the reports they create, to suit your needs and preferences.
Design view enables you to make changes ranging from the underlying data source to the color of the text. For details about customizing your reports in Design view, see the next lesson. For using Design view to create a report from scratch, read on.
If you want total freedom and control, right from the beginning—perhaps you like to start sewing without a pattern or cooking without a recipe—then you'll like creating reports in Design view.
[pic] Toolbox
[pic] Design view grid

Design view provides you with a toolbox from which you drag selected controls and arrange them on a grid. Controls can be bound to data from your database, displaying it directly, or they can be unbound, without a link to a data source. Descriptive text, dividing lines, a product logo, and other decorative controls are usually unbound.
Previewing The Created reports
When you create a report using AutoReport or the Report Wizard, the completed report appears on your computer in the form that most users will see, either on a computer screen or on a printed page. This view is Print Preview.
[pic] In Print Preview, you can see in detail how your report will appear.
[pic] In Layout Preview, you can see quickly how your report will appear. Usually only a few of the records will be displayed in this view.
To change from one view to another, make a selection on the View menu or click the arrow next to the View button [pic]and choose from the list that appears.
The data in a preview or in a printed report is static. Each time you open a preview or print the report, however, you will see the most recent data from the report's table or query data source.
Customizing The Reports
Once you've created your report, you may want to move its parts around and change their appearance to suit your purposes and preference. Access makes it easy to choose how a report looks, on a computer or in print. You can customize the details, or you can pick a handy AutoFormat from a range of useful styles.
You may want to change the structure of your report—to group data by headings, for instance, or to separate parts of your report.
Or you may want a different look—more casual or compact, or with new colors or lettering.
Design view is where you add sections, group data, set report properties to control appearance and behavior, resize parts of a report, or change report formatting.
To see the report as it will appear to someone working with it, view it in Print Preview. You can keep switching back and forth between these two views until you get it perfect.
[pic] Change the look of headings and other text.
[pic] Add, remove, or resize sections.

Reports & Sections
Reports have three types of sections:
Header sections The header sections contain information that appears either at the top of the report, or at the top of every page in the report.
The report header appears only once, at the beginning of the report. The topmost contents of the report (such as company name, address, and logo) belong in the report header.
The page header appears on every page of the report. Contents that you want to appear at the top of each page, such as column names, belong in the page header.
Detail The detail section contains most of the information in a report. For example, the detail section of an invoice lists all the individual items purchased and their costs.
Footer The page footer appears on every page. The report footer appears once, at the end of a report. The page footer section may contain the page number; the report footer section may contain the conclusion, such as a grand total.

[pic] Report header with report title
[pic] Page header with headings
[pic] Detail section with data
[pic] Page footer with separator line & page numbers
Grouping The Data In Reports
You can group data in a report by selecting one or more values.
For example, selecting a date could group all the orders shipped on that date, as shown in the illustration.
You can select as many as 10 values for grouping in a report.
You can create or change data grouping in Design view by clicking the Sorting and Grouping button [pic]on the Report Design toolbar. You can also use Report Wizard to group data while creating a report in the wizard.
Report Properties
You can set many properties for a report, to change how it is viewed or printed.
For example, a report's Page Header property determines whether the page header is printed on the page that begins with the Report Header.
To view or change a report's properties, just double-click the report selector [pic]where the rulers meet. To see more details about a property, click that property and then press F1.
Each report section and each control (such as a text box) on a report has properties also. To view a section's properties, double-click the section name or the section selector.
To view a control's properties, double-click the control. You can also right-click a control, section selector, or report selector, and then click Properties.

[pic] Double-click the report selector...
[pic] ...to display a report's properties.

Inserting Page Breaks
In reports, you use a page break control to mark where you want to start a new page within a section.
Suppose, for example, that you have included both a report's title page and its introduction in the Report Header. If you want the title and the introduction printed on separate pages, you would place a page break in the Report Header between the controls containing them.
[pic] The Page Break tool...
[pic] ...inserts a page break, shown as a dotted line
Note You don't need to insert a page break unless you specifically want a new page. The report will automatically assign pages to continuous data.
In Design view, use the Page Break tool [pic]in the Toolbox to show where you want to place a page break. Be careful to place the page break above or below any other control to avoid splitting data in the other control.
A short dotted line on the left edge of the report indicates the page break.
Applying Auto Format
To save time, you can apply a preset format—known as an AutoFormat—to all or part of your report. (You can apply different AutoFormats to different parts of the report, if you wish.)
Would you like your report to have a casual look? Or would you prefer to make the text and headings more compact so as to reduce the number of pages?
An AutoFormat sets the style and color for text, headings, and dividing lines (such as lines between sections). You can apply it to your whole report, to a selected section, or to just one control.
The AutoFormat choices are the same format choices presented in the Report Wizard. If you apply an AutoFormat to any report, that AutoFormat will also become the format for all new AutoReports, until you make another change in AutoFormat.
Note Applying an AutoFormat may remove custom formatting that you've applied previously.
Customized Formatting
If an AutoFormat doesn't give you quite the look you want, you can adapt it by applying custom formatting to your report in Design view. Or if you are creating your report from scratch, you can make format choices as you go along.
You can select the size and appearance of a report's sections and controls. Background colors, borders, and text styles are all yours to choose.
Just select the section or control, then click the buttons you want on the Formatting (Form/Report) toolbar.
You can also customize the appearance of a control, a section, or the whole report by setting its properties, as you learned earlier in this lesson.
And you can perform calculations in the report controls, such as subtotals and totals. For more information about performing calculations in controls, see the course "Take the controls."
[pic] Click the Bold button on the Formatting (Form/Report) toolbar...
[pic] ...to format selected text in the report.

Practice Test
Complete the following test so you can be sure you understand the material. Your answers are private, and test results are not scored.
Access Fundamentals

1. Which of the following is not a database object? a. Table b. Report c. Query d. Worksheet

2. Which of the following describes a relational database? a. It provides a relationship between integers. b. It consists of separate tables of related data. c. It retrieves data related to its queries.

3. What are the main building blocks of a database? a. Lists. b. Queries. c. Tables.

Tables

4. Data should be organized into tables based on: a. Field properties. b. Subjects associated with the data. c. Character lengths.

5. A row in a table is also known as: a. A field. b. A record. c. A data type.

6. A primary key is: a. A unique identifier that can be used to relate tables. b. The key that precedes the secondary key. c. The password that validates a database.

Queries

7. Which of the following is a reason to use a query? a. A query makes data entry easier. b. A query retrieves data, using criteria that you specify. c. A query produces a formatted, printed report of your data.

8. When you create a query, what does Access save? a. Y netThe data. b. The query structure and criteria to retrieve data. c. The way the data looks when viewed or printed.

9. Data returned by a query is called a _____. a. recordset b. parameter c. selected field

10. What are the two ways to create a query? a. By using the AutoQuery feature or by clicking the Run button . b. By using a wizard or in Design view. c. By using the Save As command on the File menu to convert a table to a query.

11. Which of the following is true when you specify criteria for a field? a. You must display that field in your query results. b. You can't display that field in your query results. c. You can choose whether or not to display that field in the query results.
12. How can you modify criteria in a select query? a. In the Simple Query Wizard. b. When you run the query. c. Use the query design grid.

Forms

13. Entering data in a form is more efficient than entering it in a table, because: a. You have to save the data in a form before it's entered into the database. b. A form automatically validates your data. c. A form controls how people view and enter data.

14. Which statement describes a subform? a. It opens reports and other forms. b. It is a form that looks like part of another form. c. It is used for printing data.

15. Forms are used only for viewing and entering data. a. True b. False

16. Which of the following is a benefit of creating a form in Design view? a. You have complete control over the organization and look of your form. b. Access steps you through the process. c. You automatically bind controls to a specified table.

17. A tabular AutoForm is the best choice when you want to: a. Add up numbers on the form. b. Design a form from scratch. c. Show all fields aligned vertically and all records aligned horizontally.

18. In Design view, use the _____ to add controls to your form. a. Expression Builder b. Control property sheet c. Toolbox

19. When a control on a form is bound to your data, which of the following is true? a. It automatically prints your data. b. It displays live data from a table or enters data straight into a table. c. It performs calculations on your data.

20. What does a form's tab order determine? a. How people move through your form by pressing the TAB key. b. How your form is tabulated, by links to tables. c. Where controls are located on the form.

21. How do you set properties for the whole form? a. In the Design view Toolbox. b. By double-clicking the form selector in Design view. c. In the Form Wizard.

22. Which parts of a form can you change to look as you prefer? a. Just the controls. b. Just the whole form at once. c. Just what you want.

23. If you change the width of a form section, the width of your form adjusts with it. a. True. b. False.

24. What does the AutoFormat feature do? a. It updates your data whenever someone uses your form. b. It applies pre-designed colors and styles to your form. c. It changes the color of controls to indicate something about data entered in them.

Reports

25. What are the two main purposes of reports? a. To view and enter data. b. To store and relate data. c. To search for data. d. To organize and present data.

26. What are the two major types of report controls? a. Data display and decorative elements. b. Boxes and cells. c. AutoReport and Design view

27. Reports alter the underlying data in your database. a. True. b. False.

28. What does an AutoReport do? a. It creates reports at specified intervals of time. b. It steps you through the process of creating a report.

29. When should you use Design view to create a report? a. When you want Access to ask you questions and step you through creating a report. b. When you want a quick report that shows all the fields from a table or query. c. When you want complete control over every part of the report.

30. If someone changes table or query data that your report uses, what will happen when you next open the report? a. You will see a message that the data has changed. b. Nothing. Once you create a report, the data will never change. c. The updated data will appear when you open the report again.

31. How can you arrange the data in your report by products? a. Insert a header or footer section for each product. b. Group the data by using the Product field. c. Use AutoFormat to set up the structure.

32. Suppose you want to have a list of sales figures in your report, followed in the same section by a new page with a list of your sales force. How do you keep that page separate? a. Create two separate reports and staple them together. b. Insert a section header between the two lists. c. Insert a page break between the two lists.

33. You want the date and your department's name to appear on every page of a report, so you add those items to _____: a. a page group. b. a page header. c. the report's property sheet.

Overall

34. Which of the following best describes a query? a. A query enables people to enter or view data in your database easily. b. A query summarizes and prints data. c. A query retrieves data that satisfies conditions that you set.

35. Which of the following can you do in a report? a. Perform calculations on your data. b. Add headings and formatting to your data. c. Group your data. d. All of the above.

36. Why is it often more efficient to use a form than a table? a. Because a form always presents your data in rows and columns. b. Because a form is always printed. c. Because a form simplifies data entry or viewing.

Answers:

1.D 2.B 3.C 4.B 5.B 6.A 7.B 8.B 9.A 10.B
11.C 12.C 13.C 14.B 15.B 16.A 17.C 18.C 19.B 20.A
21.B 22.C 23.A 24.B 25.D 26.A 27.B 28.C 29.C 30.C
31.B 32.C 33.B 34.C 35.C 36.C

[pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic][pic]