Exam 77-604 Using Microsoft Offi ce Outlook 2007 453
2.7 Modify the design of reports and forms
In many computer applications, each document or object you work with is a separate file.
An Access 2007 database, by contrast, is a single .accdb file that can store many different database objects
An Access database can include the types of database objects shown in this table.
Object Use to
Table Store and view data.
Query Organize, combine, and filter data.
Form View and edit data in a custom format.
Report Print data in a custom format.
Tip A database can also include macros and modules. Because there are no exam objectives targeted at these objects, we do not discuss them in this book.
Tables are the core database objects. Their purpose is to store information. The purpose of every other database object is to interact in some manner with one or more tables.
This chapter guides you in studying the processes of creating and modifying databases, tables, forms, PivotCharts and PivotTables, and reports.
Important Before you can use the practice files in this chapter, you need to install them from the book’s companion CD to their default location. See “Using the Companion CD”
at the beginning of this book for more information.
Tip Graphics and operating system–related instructions in this book reflect the Windows Vista user interface. If your computer is running Windows XP and you experience trouble following the instructions as written, refer to the sidebar “If You Are Running Windows XP”
in “Working in the Microsoft Office Fluent User Interface” at the beginning of this book.
2.1 Create databases
Creating a database structure used to be a lot of work, and after you created it and entered data, making changes could be difficult. Templates have changed this process.
Committing yourself to a particular database structure is no longer the big decision it once was. By using pre-packaged templates, you can create a dozen database appli- cations in less time than it used to take to sketch the design of one on paper. Access templates might not create exactly the database application you want, but they can quickly create something very close that you can tweak to fit your needs.
If none of the database templates meets your needs, you can create a new blank data- base and then create the database structure from scratch. This might be the preferable method if you plan to import or link most of the tables.
➤To create a database by using a template
1. On the Getting Started with Microsoft Offi ce Access page, in the Template Categories list, click a category.
2. Click the template icon for the template you want to open.
3. In the File Name box, type a new name for the database.
Tip Naming conventions for Access database fi les follow those for Microsoft Windows fi les. A fi le name, including its path, can contain up to 260 characters, including spaces, but creating a fi le name that long is not recommended. File names cannot contain the following characters: \ / : * ? “ < > |.
4. If you want to change the default location, click the Browse for a location button, specify the storage location, and then click OK.
Tip To change the default save location, click the Microsoft Offi ce Button, click Access Options, and then on the Popular page, under Creating Databases, click the Browse button. In the Default Database Path dialog box, browse to the folder you want to select as the default database folder. Then click OK in each of the open dialog boxes.
5. Click Create.
➤To create a blank database
1. On the Getting Started with Microsoft Access page, click Blank Database.
2. In the File Name box, type the name for the database.
3. If you want to change the default location, click the Browse for a location button, specify the storage location, and then click OK.
4. Click Create.
Practice Tasks
There are no practice fi les for these tasks.
l Create a database application based on the Contacts template in the Local Templates category. Name it My Contacts, and store it in the default location.
Then open and explore the database.
l Create a new blank database called My Blank Database in the Documents\
Microsoft Press\MCAS\Access2007\Objective02 folder.
Practice Tasks
There are no practice fi les for these tasks.
There are no practice fi les for these tasks.
l Create a database application based on the Contacts template in the Local Create a database application based on the Contacts template in the Local Templates category. Name it
Templates category. Name it My ContactsMy Contacts, and store it in the default location. , and store it in the default location.
Then open and explore the database.
Then open and explore the database.
l Create a new blank database called Create a new blank database called My Blank DatabaseMy Blank Database in the in the Documents\Documents\
Microsoft Press\MCAS\Access2007\Objective02 Microsoft Press\MCAS\Access2007\Objective02 folder. folder.
2.2 Create tables
You can create simple default tables in Datasheet view, but because most tables require modification in Design view, it is often easier to start there first. You cannot save a table created in Design view without defining at least one field.
See Also For information about fields, see section 2.4, “Create fields and modify field properties.”
Although manually creating a table is relatively easy, if one of the available table templates is close to what you want, using it might save you a little time and effort.
If you need to create two similar tables, you can define one and duplicate its structure as the basis for the other. Then you can customize the structure for the second table.
➤To create a table in Datasheet view
1. On the Create tab, in the Tables group, click the Table button.
2. Save the table with the name you want.
Tip You must save a new table for it to become part of the database.
➤To create a table in Design view
1. On the Create tab, in the Tables group, click the Table Design button.
2. Enter at least one field name, and specify its data type.
See Also For information about data types, see section 1.1, “Define data needs and types.”
3. Save the table with the name you want.
Tip If you try to close the table without saving it, Access will prompt you to save.
➤To create a table from a template
1. On the Create tab, in the Tables group, click the Table Templates button.
2. Click the type of table you want in the list.
3. Save the table with the name you want.
➤To duplicate the structure of a table
1. In the Navigation Pane, right-click the table you want to use as the basis for the new table, and click Copy.
2. On the Home tab, in the Clipboard group, click the Paste button.
3. In the Paste Table As dialog box, name the new table, click Structure Only, and then click OK.
Practice Tasks
If you worked through the previous tasks, the practice fi le for these tasks is My Blank Database located in the Documents\Microsoft Press\MCAS\Access2007\
Objective02 folder. Otherwise, work with a new blank database.
l Open the blank database you created in the previous tasks, and add a new table in Datasheet view named Datasheet.
l In your database, add a new table in Design view with an AutoNumber primary key fi eld named ProjectID. Save the table with the name Design.
l In your database, add a table based on the Assets template, and save it with the name Template.
l In your database, duplicate the structure of the Template table, naming the new table Duplicate.
Practice Tasks
If you worked through the previous tasks, the practice fi le for these tasks is If you worked through the previous tasks, the practice fi le for these tasks is My My Blank Database
Blank Database located in the located in the Documents\Microsoft Press\MCAS\Access2007\Documents\Microsoft Press\MCAS\Access2007\
Objective02
Objective02 folder. Otherwise, work with a new blank database. folder. Otherwise, work with a new blank database.
l Open the blank database you created in the previous tasks, and add a new Open the blank database you created in the previous tasks, and add a new table in Datasheet view named
table in Datasheet view named DatasheetDatasheet..
l In your database, add a new table in Design view with an AutoNumber primary In your database, add a new table in Design view with an AutoNumber primary key fi eld named
key fi eld named ProjectIDProjectID. Save the table with the name . Save the table with the name DesignDesign..
l In your database, add a table based on the Assets template, and save it with In your database, add a table based on the Assets template, and save it with the name
the name TemplateTemplate..
l In your database, duplicate the structure of the Template table, naming the In your database, duplicate the structure of the Template table, naming the new table
new table DuplicateDuplicate..
2.3 Modify tables
After creating a table, you can modify the table as a whole in various ways. You can rename it at any time. You can also change various properties that affect the way the table looks and behaves by changing settings in the table’s Property Sheet.
New in Access 2007 is the ability to perform simple calculations in tables that contain numeric data. Instead of having to create a query, you can add a Totals row to the datasheet and then perform aggregate functions, such as sum, average, and count, depending on the data type of the field.
Choose an aggregate function from a list.
If you create a table and then no longer need it, you should delete the table rather than leaving it to clutter up the database.
➤To rename a table
1. Make sure the table is closed. Then in the Navigation Pane, right-click the table, and click Rename.
2. With the old name highlighted for editing in the Navigation Pane, type the new name, and press Enter.
➤To modify table properties
1. Display the table in Design view.
2. On the Design tab, in the Show/Hide group, click the Property Sheet button.
3. On the General tab, click the box of the property you want to change, and then either enter a new setting or click a list option.
4. Save the table.
➤To add a Totals row to a table
1. Display the table in Datasheet view.
2. On the Home tab, in the Records group, click the Totals button.
3. In the Totals row, click the column on which you want to perform an aggregate calculation, and then click the function you want in the list.
➤To delete a table
➜ Make sure the table is closed. Then in the Navigation Pane, right-click the table, and click Delete.
Practice Tasks
The practice fi les for these tasks are located in the Documents\Microsoft Press\
MCAS\Access2007\Objective02 folder.
l Open the ModifyTables database, and rename the Employees table as Associates.
l In the ModifyTables database, modify the Products table so that when you open it, the records are sorted alphabetically by product name.
l Open the Totals database, and in the Orders table, calculate the total freight charges to date.
l Delete the Sales table from the Totals database.
Practice Tasks
The practice fi les for these tasks are located in the
The practice fi les for these tasks are located in the Documents\Microsoft Press\Documents\Microsoft Press\
MCAS\Access2007\Objective02 MCAS\Access2007\Objective02 folder. folder.
l Open the Open the ModifyTablesModifyTables database, and rename the Employees table as database, and rename the Employees table as Associates
Associates..
l In the In the ModifyTablesModifyTables database, modify the Products table so that when you database, modify the Products table so that when you open it, the records are sorted alphabetically by product name.
open it, the records are sorted alphabetically by product name.
l Open the Open the TotalsTotals database, and in the Orders table, calculate the total freight database, and in the Orders table, calculate the total freight charges to date.
charges to date.
l Delete the Sales table from the Delete the Sales table from the TotalsTotals database. database.
2.4 Create fields and modify field properties
Strategy Structuring fields to make data entry efficient while minimizing the risk of input errors is an important part of database design. We can provide only a brief review of all the field properties that are available for different data types in this section. It is important that you thoroughly understand the effect of these properties and how to use them.
New Fields
With earlier versions of Access, you created and modified fields in Design view. With Access 2007, you can create fields in Datasheet view, but you still have to switch to Design view to refine the definitions.
When a table is displayed in Datasheet view, a blank column appears on the right labeled Add New Field. You can use this field to create a default text field. If you want the new field to appear elsewhere in the table, you can click any field and then insert a new default text field to its left. You can change the name and data type of both types of fields in Datasheet view, or you can switch to Design view for more specific customization.
You can quickly create a new field with a preset field name, data type, and appropriate property settings by basing the field on a template.
➤To create a field in Design view
1. In the next available row of the Field Name column, type the field name you want, and then press Tab.
2. In the Data Type list, click the data type you want, and then press Tab.
3. If you want, enter a description of the field.
➤To create a text field in Datasheet view
➜ At the right end of the table, double-click Add New Field, type the field name you want, and press Enter.
➜ Click the name of the field to the left of which you want to insert the new field, and then on the Datasheet tab, in the Fields & Columns group, click Insert.
➤To rename a field in Datasheet view
➜ Double-click the default field name, and type the name you want.
➤To change the data type of a selected field in Datasheet view
➜ On the Datasheet tab, in the Data Type & Formatting group, click the data type you want in the Data Type list.
➤To create a field from a template
1. On the Datasheet tab, in the Fields & Columns group, click the New Field button.
2. In the Field Templates pane, click the template you want, and then drag it where you want it in the table.
3. Close the Field Templates pane, and then save the table.
See Also Clicking a field in the Basic Fields list inserts a new field of the selected data type. For information about data types, see section 1.1, “Define data needs and types.”
➤To delete a selected field
1. In Datasheet view, on the Datasheet tab, in the Fields & Columns group, click the Delete button.
2. Click Yes to confirm the deletion, and then save the table.
Properties
You can specify a field’s data type to restrict the type of information that can be entered in the field. To further define and constrain the data, you can set the properties for the selected field in Design view. (Different properties are available depending on the data type of the selected field.) The following properties can be set:
l Allow Zero Length. This property, when set to Yes, allows a “” string value.
l Append Only. This property, when set to Yes, adds new text to the field, creating a field value history. This property is especially useful for memo fields.
l Caption. This value will be displayed instead of the field name in the datasheet, forms, reports, and queries.
l Decimal Places. This number of decimal places will be displayed.
l Default Value. This value will be entered in a new record, unless you change it.
l Field Size. This maximum size is allowed. (Options are specific to the data type.)
l Format. This property designates how the field value will appear by default.
(Options are specific to the data type.)
l IME Mode. This property is related to East Asian languages.
l IME Sentence Mode. This property is related to East Asian languages.
l Indexed. This property, when set to Yes, speeds up the searching and sorting of data. Setting the property to Yes (No Duplicates) limits the field to a unique value in each record.
l Input Mask. This property specifies that the field value must conform to the pattern set by the mask.
l New Values. This property, when set to Increment, increases the value for each new record; setting this property to Random assigns a random value.
l Required. This property, when set to Yes, ensures that every record will have a value in this field.
l Show Date Picker. This property, when set to For Dates, displays a calendar control when the field is activated for editing. If you use an input mask for a Date/Time field, this control is available, regardless of how you set the property.
l Smart Tags. This property allows the Date, Telephone Number, Financial Symbol, and Person Name smart tags to be attached to the field. This property is set by default.
l Text Align. This field, when set, controls the default alignment of the value.
l Text Format. This property, when set to Plain Text, stores only text; setting the property to Rich Text allows formatting because data is stored in HTML format.
l Unicode Compression. This property, when set to Yes, compresses values of fewer than 4096 characters.
l Validation Rule. This expression establishes criteria that any new or changed value must meet.
l Validation Text. This explanation appears when a value does not meet the corresponding validation rule.
Input Masks
You can use the Input Mask property to control how data is entered in text, number (except ReplicationID), date/time, and currency fields. This property has three sections, separated by semicolons, like the mask for a telephone number, shown here:
!\(000”) “000\-0000;1;#
The first section contains characters that are used as placeholders for the information to be typed, as well as characters such as parentheses and hyphens. Together, all these characters control the appearance of the entry. The following table explains the purpose of the most common input mask characters.
Character Description
0 Required digit (0 through 9).
9 Optional digit or space.
# Optional digit or space; blank positions are converted to spaces; plus and minus signs are allowed.
L Required letter (A through Z).
? Optional letter (A through Z).
A Required letter or digit.
a Optional letter or digit.
& Required character (any kind) or a space.
C Optional character (any kind) or a space.
< All characters that follow are converted to lowercase.
> All characters that follow are converted to uppercase.
! Characters typed into the mask fill it from left to right. You can include the exclamation point anywhere in the input mask.
\ Character that follows is displayed as a literal character.
“Literal Text“ Access treats the string enclosed in double quotation marks as a literal string.
Password Creates a password entry box. Any character typed in the box is stored as the character but displayed as an asterisk (*).