Microsoft Access 2007 by yaosaigeng

VIEWS: 9 PAGES: 40

									University of Sunderland



Microsoft Access 2007




21/11/2008
                                   Microsoft Access 2007




                                       Access 2007
Ribbons
The Office Fluent Ribbon is the primary replacement for menus and toolbars and provides the main
command interface in Office Access 2007. One of the main advantages of the Ribbon is that it
consolidates, in one place, those tasks or entry points that used to require menus, toolbars, task
panes, and other UI components to display. This way, you have only one place in which to look for
commands, instead of a multitude of places.


When you open a database, the Ribbon appears at the top of the main Office Access 2007 window,
where it displays the commands in the active command tab.




The Ribbon contains a series of command tabs that contain commands. In Office Access 2007, the
main command tabs are Home, Create, External Data, and Database Tools. Each tab contains
groups of related commands, and these groups surface some of the additional new UI elements,
such as the gallery, which is a new type of control that presents choices visually.

The commands on the Ribbon take into account the currently active object. For example, if you
have a table opened in Datasheet view and you click Form on the Create tab, in the Forms group,
Office Access 2007 creates the form, based on the active table. That is, the name of the active table
is entered in the form's RecordSource property.


You can use keyboard shortcuts with the Ribbon. All of the keyboard shortcuts from an earlier
version of Access continue to work. The Keyboard Access System replaces the menu accelerators
from earlier versions of Access. This system uses small indicators with a single letter or combination
of letters that appear on the Ribbon and indicate what keyboard shortcut actives the control
underneath.


When you have selected a command tab, you can browse the commands available within that tab.
                                                   2
University of Sunderland                                                     Garry Storer
                                   Microsoft Access 2007

Quick Access Toolbar
The Quick Access Toolbar is a customizable toolbar containing a set of commands that are
independent of the tab that is currently displayed.

The Quick Access Toolbar can be located in one of two places:


       Upper-left corner next to the Microsoft Office Button      (default location)




       Below the Ribbon, which is part of the Microsoft Office Fluent user interface




If you don't want the Quick Access Toolbar to be displayed in its current location, you can move it
to the other location. If you find that the default location next to the Microsoft Office Button      is
too far from your work area to be convenient, you may want to move it closer to your work area.
The location below the Ribbon encroaches on the work area. Therefore, if you want to maximize
the work area, you may want to keep the Quick Access Toolbar in its default location.

1.      Click Customize Quick Access Toolbar .

2.      In the list, click Show Below the Ribbon.




                                                    3
University of Sunderland                                                     Garry Storer
                                   Microsoft Access 2007

Add a command to the Quick Access Toolbar
You can add a command to the Quick Access Toolbar directly from commands that are displayed on
the Office Fluent Ribbon.
1.      On the Ribbon, click the appropriate tab or group to display the command that you
     want to add to the Quick Access Toolbar.

2.      Right-click the command, and then click Add to Quick Access Toolbar on the shortcut
     menu.

 NOTES

       You cannot increase the size of the buttons representing the commands by an option in
     Microsoft Office. The only way to increase the size of the buttons is to lower the screen
     resolution you use.

       You cannot display the Quick Access Toolbar on multiple lines.

       Only commands can be added to the Quick Access Toolbar. The contents of most lists,
     such as indent and spacing values and individual styles, which also appear on the Ribbon,
     cannot be added to the Quick Access Toolbar.




                                                   4
University of Sunderland                                                     Garry Storer
                                  Microsoft Access 2007

Navigation Pane
                                  When you open a database or create a new one, the names of
                                  your database objects appear in the Navigation Pane. The
                                  database objects include your tables, forms, reports, pages,
                                  macros, and modules. The Navigation Pane replaces the
                                  Database window used by earlier versions of Access —— if you
                                  used the Database window to perform a task in a previous
                                  version, you now perform that task by using the Navigation Pane
                                  in Office Access 2007. For example, if you want to add a row to a
                                  table in Datasheet view, you open that table from the Navigation
                                  Pane.


                                  To open a database object or apply a command to a database
                                  object, right-click the object and select a menu item from the
                                  context menu. The commands on the context menu vary
                                  according to the object type.

                                  Open a database object, such as a table, form, or report
                                         In the Navigation Pane, double-click the object.
                                  -or-

                                  In the Navigation Pane, select the object, and then press
                                  ENTER.

                                  -or-

                                   1.     In the Navigation Pane, right-click an object.
 2.     In the context menu, click a menu item.
Note that you can set an option to open objects with a single click in the Navigation Options dialog
box.

The Navigation Pane divides your database objects into categories, and these categories contain
groups. Some categories are predefined for you, and you can also create your own custom groups.

By default, the Navigation Pane appears when you open a database in Office Access 2007, including
databases created in earlier versions of Access. You can prevent the Navigation Pane from
appearing by default by setting a program option. The following set of steps explains how to take
each action.




                                                  5
University of Sunderland                                                    Garry Storer
                                   Microsoft Access 2007

To show or hide the Navigation Pane
     Click the button in the upper-right corner of the Navigation Pane (         ), or press F11.
Prevent the Navigation Pane from appearing by default

 1.     Click the Microsoft Office Button      , and then click Access Options.
      The Access Options dialog box appears.

 2.     In the left pane, click Current Database.
 3.     Under Navigation, clear the Display Navigation Pane check box, and then click OK.



Tabbed documents
Starting with Office Access 2007 you can display your database objects in tabbed documents
instead of overlapping windows. For everyday interactive use, you may prefer the tabbed
document interface. You can enable or disable tabbed documents by setting your Access Options.
However, if you change the tabbed document settings, you must close and reopen your database
for the new settings to take effect.




Show or hide document tabs
 1.     Click the Microsoft Office Button    , and then click Access Options.
      The Access Options dialog box appears.

 2.     In the left pane, click Current Database.
 3.     In the Application Options section, under Document Window Options, select Tabbed
      Documents.
 4.     Select or clear the Display Document Tabs check box. Clearing the check box turns off
      document tabs.
 5.     Click OK.
 NOTES

        The Display Document Tabs setting is a per-database setting. You must set this
      independently for each database.
        After you change the Display Document Tabs setting, you must close and reopen your
      database to see the change take effect.
       New databases created by using Office Access 2007 display document tabs by default.
        Databases created by using an earlier version of Access use overlapping windows by
      default.
                                                    6
University of Sunderland                                                     Garry Storer
                                 Microsoft Access 2007


Dialogue Box
You can view the dialogue box of Word 2003 for certain functions. Click on the icon from the
ribbon.




                                                7
University of Sunderland                                                  Garry Storer
                                    Microsoft Access 2007

Open
When you start Office Access 2007 by clicking the Windows Start button or a desktop shortcut (but
not when you click on a database), the Getting Started with Microsoft Office Access page appears.
This page shows what you can do to get started in Office Access 2007.




For example, you can create a new blank database, create a database from a template, or open a
recent database (if you have opened some databases before). You can also go directly to Microsoft
Office Online to learn more about 2007 Microsoft Office system and Office Access 2007, or you can
click the Microsoft Office Button      and use the menu to open a existing database.

Open a new blank database
 1.     Start Access from the Start menu or from a shortcut.
      The Getting Started with Microsoft Office Access page appears.

 2.       On the Getting Started with Microsoft Office Access page, under New Blank Database,
      click Blank Database.
 3.       In the Blank Database pane, in the File Name box, type a file name or use the one that
      is provided for you.
 4.     Click Create.
      The new database is created, and a new table is opened in Datasheet view.

Office Access 2007 provides a number of templates with the product, and you can download more
from Microsoft Office Online. What is a template? In the context of Office Access 2007, it is a
predesigned database complete with professionally designed tables, forms, and reports. Templates
give you a big head start when you create a new database.

                                                   8
University of Sunderland                                                    Garry Storer
                                   Microsoft Access 2007

Create a new database from a featured template
 1.     Start Access from the Start menu or from a shortcut.
      The Getting Started with Microsoft Office Access page appears.

 2.     On the Getting Started with Microsoft Office Access page, under Featured Online
      Templates, click a template.
 3.     In the File Name box, type a file name or use the one that is provided for you.
 4.      Optionally, check the Create and link your database to a Windows SharePoint Services
      site if you want to link to a Windows SharePoint Services site.
 5.      Click Create
      -or-
      Click Download
      Access creates the new database from the template and opens it.

You can download additional templates from Office Online directly from within the Office Access
2007 user interface.

Create a new database from a Microsoft Office Online Template
 1.     Start Access from the Start menu or from a shortcut.
      The Getting Started with Microsoft Office Access page appears.

 2.     On the Getting Started with Microsoft Office Access page, in the Template Categories
      pane, click a category and then, when the templates in that category appear, click a
      template.
 3.     In the File Name box, type a file name or use the one that is provided for you.
 4.     Click Download.
      Access automatically downloads the template, creates a new database based on that
      template, stores it in your documents folder (for example, the My Documents folder), and
      opens the database.

When you open (or create and open) a database, Access adds the file name and location of the
database to an internal list of the most recently used documents. This list is displayed on the
Getting Started with Microsoft Office Access page so that you can easily open your most recently
used databases.

Open a recently used database
 1.     Start Access.

 2.     On the Getting Started with Microsoft Office Access page, under Open Recent
      Database, click the database that you want to open.
      Access opens the database.

                                                  9
University of Sunderland                                                    Garry Storer
                                        Microsoft Access 2007

Open a database by using the Microsoft Office Access button
 1.         Start Access.

 2.          Click the Microsoft Office Button       , and then click the database that you want to
          open, if it appears in the right pane of the menu.
          -or-
          Click the Microsoft Office Button     , and then click Open. When the Open dialog box
          appears, enter a file name, and then click Open.


Convert an Access 2007 database to an earlier file format
Do the following to create a copy of an Access 2007 database in a file format that you specify:

 1.         Click the Microsoft Office Button      , and then point to Save As.

           NOTE If no object is currently selected in the Navigation Pane, the Save As command
          may be unavailable (it appears dimmed). However, the items on the submenu are still
          available.
 2.         Under Save the database in another format, do one of the following:
                  To save a copy of the database in a format that can be opened by using Access
            2002 or later, click Access 2002 - 2003 Database.
                  To save a copy of the database in a format that can be opened by using Access
            2000 or later, click Access 2000 Database.
 3.          In the Save As dialog box, in the File name box, type a name for the database file, and
          then click Save.

Access closes the original database and opens a new copy of the database in the format that you
specified. Any changes that you make to the database affect only the new copy. To make changes
to the original database, you must reopen the original database.

 NOTE To convert an Access 2007 database to a file format that is compatible with Access 97 or
earlier, you must first use Access 2007 to convert the database to the Access 2000 file format or the
Access 2002 - 2003 file format. Then you use an earlier version of Access to convert the database to
the format that you want. For example, by using the Convert Database command in Access 2003,
you can convert an Access 2000 database or an Access 2002 - 2003 database to the Access 97 file
format.

Create a new table in an existing database
 1.         Click the Microsoft Office Button      , and then click Open.

 2.         In the Open dialog box, select the database that you want to open, and then click Open.

 3.         On the Create tab, in the Tables group, click Table.


                                                       10
University of Sunderland                                                          Garry Storer
                                    Microsoft Access 2007




      A new table is inserted in the database and the table opens in Datasheet view.

Use a table template to create a table
In earlier versions of Access, you used the Table Wizard to quickly create a table from sample tables
and fields. In Office Access 2007, you use table templates and field templates instead.

A table template is an empty table that you can start using as-is, or modify to suit your needs.
Office Access 2007 comes with the following table templates, designed to be compatible with the
Microsoft Windows SharePoint Services 3.0 lists of the same name:

       Contacts A table for managing business contact information, which includes e-mail
      addresses, Web page URLs, and attachments, such as a service contract and a photo.
        Tasks A table for tracking tasks, which includes a field for attachments.
       Issues A table for tracking issues, which includes a field for attachments and an
      append-only Memo field that keeps a history of old field values.
        Events A table for managing events, which includes a rich text Memo field and a field
      for attachments.
       Assets A table for managing business assets, which includes two currency fields so that
      you can track asset depreciation.

After you create a table by using a table template, you may want to add fields by using field
templates. A field template is a predefined field that you can add to any table in Datasheet view.

Create a new table by using a table template
 1.      Click the Microsoft Office Button     , and then click Open.

 2.      In the Open dialog box, select and open the database in which you wish to create a
      table.

 3.     On the Create tab, in the Tables group, click Table Templates and then select one of the
      available templates from the list.




      A new table is inserted, based on the table template that you chose.


                                                   11
University of Sunderland                                                      Garry Storer
                                     Microsoft Access 2007

Set a table's primary key
Unless you have a specific reason not to, you should specify a primary key for a table. Access
automatically creates an index for the primary key, which can help improve database performance.
Access also makes sure that every record has a value in the primary key field, and that the value is
always unique. Unique values are crucial, because otherwise there is no way to reliably distinguish a
particular row from other rows.

When you create a new table in Datasheet view, Access automatically creates a primary key for you
and assigns it a field name of ID and the AutoNumber data type.

In Design view, you can change or remove the primary key, or set the primary key for a table that
doesn't already have one.

Determine which fields to use as a primary key
Sometimes, you might already have data that you want to use as a primary key. For example, you
may have existing ID numbers for your employees. If you create a table to track employee
information, you might decide to use the existing employee ID as the primary key for the table. Or,
perhaps employee ID is only unique in combination with department ID, requiring that you use
both fields together as the primary key. A good candidate for the primary key has the following
characteristics:

       Each record has a unique value for the field or combination of fields.

       The field or combination of fields is never empty or null — there is always a value.
       The values do not change.

If no suitable data exists to use as a primary key, you can create a new field to use as a primary key.
When you create a new field to use as a primary key, set the field's data type to AutoNumber to
help make sure that it meets the three characteristics in the preceding list.

Set or change the primary key
 1.     Select the table whose primary key you want to set or change.

 2.     On the Home tab, in the Views group, click View, and then click Design View.

 3.     In the table design grid, select the field or fields that you want to use as the primary
      key.

      To select one field, click the row selector for the field that you want.

      To select more than one field, hold down CTRL, and then click the row selector for each
      field.

 4.      On the Design tab, in the Tools group, click
      Primary Key.


                                                    12
University of Sunderland                                                         Garry Storer
                                     Microsoft Access 2007

      A key indicator appears to the left of the field or fields that you specify as the primary key.

Remove the primary key
 1.     Select the table whose primary key you want to remove.

 2.     On the Home tab, in the Views group, click View, and then click Design View.

 3.      Click the row selector for the current primary key. If the primary key consists of multiple
      fields, hold down CTRL, and then click the row selector for each field.
 4.     On the Design tab, in the Tools group, click Primary Key.




      The key indicator is removed from the field or fields that you previously specified as the
      primary key.

 NOTE When you save a new table without setting a primary key, Access prompts you to create a
new field for the primary key. If you click Yes, Access creates an ID field that uses the AutoNumber
data type to provide a unique value for each record. If your table already includes an AutoNumber
field, Access uses it as the primary key. If you click No, Access does not add a field, and no primary
key is set.

Add a field to a table
You store each piece of data that you want to track in a field. For example, in a contacts table you
create fields for Last Name, First Name, Telephone Number, and Address. In a products table you
create fields for Product Name, Product ID, and Price.

Before you create fields, try to separate data into its smallest useful parts. It is much easier to
combine data later than it is to pull it apart. For example, instead of a Full Name field, consider
creating separate fields for Last Name and First Name. Then, you can easily search or sort by First
Name, Last Name, or both. If you plan to report, sort, search, or calculate on an item of data, put
that item in a field by itself. For more information about designing a database and creating fields,
see the links in the See Also section.

After you create a field, you can also set field properties to control its appearance and behaviour.

Add a field by entering data
When you create a new table or open an existing table in Datasheet view, you can add a field to the
table by entering data in the Add New Field column of the datasheet.




                                                    13
University of Sunderland                                                        Garry Storer
                                     Microsoft Access 2007




  Enter data in the Add New Field column.
 1.     Create or open a table in Datasheet view.
 2.     In the Add New Field column, enter the name of the field that you want to create.

      Use a descriptive name so that the field will be easier to identify.

 3.     Enter data in the new field.

Add a field by using a field template

Sometimes it is easier to choose from a predefined list of fields that fit your needs than to manually
create a field. You can use the Field Templates task pane to choose from a list of field templates. A
field template is a predefined set of characteristics and properties that describes a field. The field
template definition includes a field name, a data type, a setting for the field's Format property, and
other field properties.

 1.     On the Home tab, in the Views group, click View, and then click Datasheet View.
 2.     On the Datasheet tab, in the Fields & Columns group, click New Field.




 3.     Select one or more fields in the Field Templates pane, and then drag them to the table
      where you want to insert the new column.

Add a field from an existing table
If your database already contains tables, you can add a field from one of those tables to your new
table by using the Field List pane. The Field List pane lists all the other tables in your database,
grouped into two categories: Fields available in related tables and Fields available in other tables.
If the table you are adding a field to is in relationships with other tables, the related tables' fields
are listed first in the Field List pane.


Click the Microsoft Office Button       , and then click Open.

 1.     In the Open dialog box, select and open the database that you want to work with.
                                                    14
University of Sunderland                                                      Garry Storer
                                    Microsoft Access 2007

 2.      In the Navigation Pane, double-click the table to which you want to add an existing
      field.

      The table opens in Datasheet view.

                                         3.      On the Datasheet tab, in the Fields & Columns
                                              group, click Add Existing Fields.




                                         4.      Click the plus sign (+) next to a table in the Field
                                              List pane to display the list of fields in that table.
                                         5.      Select the field that you want, and drag it to the
                                              table where you want to insert the new column.
                                         6.      Follow the instructions in the Lookup Wizard.

                                        After you have completed the wizard, the field appears in
                                        the table in Datasheet view.

                                         NOTE When you add a field from an unrelated table and
                                        then complete the Lookup Wizard, a new one-to-many
                                        relationship is automatically created between the table in the
                                        Field List pane and the table that you have open.

Set field properties
After you create a field, you can set field properties to control its appearance and behaviour.

For example, by setting field properties, you can:

       Control the appearance of data in a field
       Help prevent incorrect data entry in a field
       Specify default values for a field
       Help speed up searching and sorting on a field

You can set some of the available field properties while you work in Datasheet view. To have access
to and set the complete list of field properties; however, you must use Design view.

Set field properties in Datasheet view
You can rename a field, change its data type, change its Format property, and change some of a
field's other properties while you work in Datasheet view.
Open a table in Datasheet view
                                                     15
University of Sunderland                                                         Garry Storer
                                    Microsoft Access 2007
 1.     In the Navigation Pane, right-click the table that you want to open.
 2.     On the shortcut menu, click Datasheet view.

Rename a field
When you add a field by entering data in Datasheet view, Access automatically assigns a generic
name to the field. Access assigns the name Field1 to the first new field, Field2 to the second new
field, and so on. By default, a field's name is used as its label wherever the field is displayed, such as
a column heading on a datasheet. Renaming fields so that they have more descriptive names helps
make them easier to use when you view or edit records.
 1.     Right-click the heading of the field that you want to rename (for example, Field1).
 2.     On the shortcut menu, click Rename Column.
 3.     Enter the new name in the field heading.

      Field names can consist of up to 64 characters (letters or numbers), including spaces.

Change a field's data type
When you create a field by entering data in Datasheet view, Access examines that data to
determine the appropriate data type for the field. For example, if you enter 1/1/2006, Access
recognizes that data as a date and sets the data type for the field to Date/Time. If Access can't
definitively determine the data type, the data type is set to Text by default.
The data type of the field determines which other field properties you can set. For example, you
can set only the Append Only property for a field that has the Hyperlink data type or the Memo
data type.

There may be cases where you want to manually change a field's data type. For example, suppose
you have room numbers that resemble dates, such as 10/2001. If you enter 10/2001 into a new
field in Datasheet view, the automatic data type detection feature selects the Date/Time data type
for the field. Because room numbers are labels, and not dates, they should use the Text data type.
Use the following procedure to change a field's data type.

 1.     On the Ribbon, click the Datasheet tab.
 2.     In the Data Type list, in the Data Type & Formatting group, select the data type that
      you want.




                                                    16
University of Sunderland                                                       Garry Storer
                                  Microsoft Access 2007


           Data type                      Use to store                                  Size

    Text                   Alphanumeric characters                        Up to 255 characters.

                           Use for text, or for numbers that are not
                           used in calculations (for example, a
                           product ID). A numeric value that is stored
                           as text can be sorted and filtered more
                           logically, but cannot be easily used in
                           calculations.


    Memo                   Alphanumeric characters (longer than 255       Up to 1 gigabyte of
                           characters in length) or text that uses rich   characters, or 2 gigabytes of
                           text formatting.                               storage (2 bytes per
                                                                          character), of which you can
                           Use for text that is more than 255             display 65,535 characters in a
                           characters in length, or for text that uses    control.
                           rich text formatting. Notes, lengthy
                           descriptions, and paragraphs that use text
                           formatting, such as bold or italics, are
                           good examples of where to use a Memo
                           field.


    Number                 Numeric values (integers or fractional         1, 2, 4, or 8 bytes, or 16 bytes
                           values).                                       when used for a replication
                                                                          ID.
                           Use for storing numbers that are used in
                           calculations, except for monetary values
                           (use the Currency data type for monetary
                           values).


    Date/Time              Dates and times.                               8 bytes.

                           Use for storing date/time values. Note
                           that each value stored includes both a
                           date component and a time component.


    Currency               Monetary values.                               8 bytes.

                           Use for storing monetary values
                           (currency).


    AutoNumber             A unique numeric value that Access             4 bytes or 16 bytes when used
                           automatically inserts when a record is         for replication ID.
                                                  17
University of Sunderland                                                      Garry Storer
                                     Microsoft Access 2007

                              added.

                              Use for generating unique values that can
                              be used as a primary key. Note that values
                              for AutoNumber fields can be
                              incremented sequentially or by a specified
                              value, or assigned randomly.


    Yes/No                    Boolean values (yes/no).                        1 bit (8 bits = 1 byte).

                              You can use one of three formats: Yes/No,
                              True/False, or On/Off.


    OLE Object                OLE objects or other binary data.               Up to 1 gigabyte.

                              Use for storing OLE objects from other
                              Microsoft Windows programs.


    Attachment                Pictures, Images, Binary files, Office files.   For compressed attachments,
                                                                              2 gigabytes. For
                              This is the preferred data type for storing     uncompressed attachments,
                              digital images and any type of binary file.     approximately 700kb,
                                                                              depending on the degree to
                                                                              which the attachment can be
                                                                              compressed.

    Hyperlink                 Hyperlinks.                                     Up to 1 gigabyte of
                                                                              characters, or 2 gigabytes of
                              Use for storing hyperlinks to provide           storage (2 bytes per
                              single-click access to Web pages through a      character), of which you can
                              URL (Uniform Resource Locator) or files         display 65,535 characters in a
                              through a name in UNC (universal naming         control.
                              convention) format. You can also link to
                              Access objects stored in a database.


    Lookup Wizard             Not actually a data type; instead, this         Table or query based: The size
                              starts the Lookup Wizard.                       of the bound column.

                              Use to start the Lookup Wizard so that you      Value based: The size of the
                              can create a field that uses a combo box to     Text field used to store the
                              look up a value in another table, query, or     value.
                              list of values.



                To optimize performance, you should use the most appropriate Field Size
                 when you create a Text or Number field. For example, if you expect to store
                                                       18
University of Sunderland                                                          Garry Storer
                                     Microsoft Access 2007

                 postal codes of a predictable length, specify that length as the field size.
                 You can specify the field size by setting a value in the FieldSize property
                 box.

                For phone numbers, part numbers, and other numbers that you don't
                 intend to use for mathematical calculations, you should select the Text
                 data type instead of the Number data type. A numeric value that is
                 stored as text can be sorted and filtered more logically.

Change a field's format
In addition to determining the data type of a new field, Access may also set the Format property for
the field, depending on what you enter. For example, if you enter 10:50 a.m., Access sets the data
type to Date/Time and the Format property to Medium Time. To manually change a field's Format
property, do the following:

 1.     On the Ribbon, click the Datasheet tab.
 2.     In the Format list, in the Data Type & Formatting group, enter the format that you
      want.

       NOTE The Format list may be unavailable for some fields (for example, Text), depending
      on the data type of the field.

Set other field properties
 1.     In Datasheet view, click the field for which you want to set the property.

 2.     On the Datasheet tab, in the Data Type & Formatting group, select the properties that
      you want.




Set field properties in Design view
You can set any field property while you work with a table in Design view. In Design view, you set a
field's data type in the table design grid, and you set other properties in the Field Properties pane.

Open a table in Design view
 1.   In the Navigation Pane, right-click the table.
 2.     On the shortcut menu, click Design view.

Change a field's data type
 1.   In the table design grid, locate the field for which you want to set the data type.

 2.     In the Data Type column, choose a data type from the list.


                                                     19
University of Sunderland                                                        Garry Storer
                                         Microsoft Access 2007


                    For phone numbers, part numbers, and other numbers that you don't intend
            to use for mathematical calculations, you should select the Text data type instead of
            the Number data type. A numeric value that is stored as text can be sorted and
            filtered more logically, but cannot be easily used in calculations.
                  For the Text and Number data types, you can specify the field size or data
            type more precisely by setting a value in the FieldSize property box.

Set other field properties
 1.          In the table design grid, select the field for which you want to set properties. Access
          displays the properties for this field in the Field Properties pane.

          NOTE The data type of the field determines the properties that you can set.

 2.          In the Field Properties pane, enter the settings that you want for each property, or
          press F6 and then use the arrow keys to select a property.
 3.          To provide more space for entering or editing a property setting in the property box,
          press SHIFT+F2 to display the Zoom box.

          TIP If you are entering an input mask or validation expression and would like help in
          building it, click   next to the property box to display the appropriate builder.

 4.          To save your changes, press CTRL+S.




                                                        20
University of Sunderland                                                           Garry Storer
                                   Microsoft Access 2007

Select data by using a query
When you want to select specific data from one or more sources, you can use a select query. A
select query helps you retrieve only the data that you want, and also helps you combine data from
several data sources. You can use tables and other select queries as data sources for a select query.

This topic provides an overview of select queries, and gives steps for creating a select query, by
using the Query Wizard or in Design view.

Overview
When you want to use data, you rarely want to use all of the data from one table. For example,
when you want to use data from a Contacts table, you usually want to look at one specific record,
or maybe just the telephone number. Sometimes you want to combine data from more than one
table, such as combining Customer information with Order information. To select the data that you
want to use, you use a select query.

A select query is a database object that shows information in Datasheet view. A query does not
store data, it displays data that is stored in tables. A query can show data from one or more tables,
from other queries, or from a combination of the two.

Benefits of using a query
A query lets you:
    View data only from the fields you are interested in viewing. When you open a table, you
       see all the fields. A query is a handy way to save a selection of fields.

 Note A query only points to data, it does not store data. When you save a query, you are not
saving a copy of the data.

       Combine data from several data sources. A table usually only displays data that it stores. A
        query lets you pick and choose fields from various sources, and specify how the information
        should be combined.

       Use expressions as fields. For example, you could use the Date function as a field, or you
        could use the Format function with a field to control the way the data from the field is
        formatted in the query results.

       View records that meet criteria that you specify. When you open a table, you see all the
        records. A query is a handy way to save a selection of records.

Basic steps to create a select query
You can create a select query by using the Query Wizard or by working in Design view. Some design
elements are not available when you use the wizard, but you can add these elements later by using
Design view. Although the two methods are somewhat different from each other, the basic steps
are essentially the same:

    1. Choose the tables or queries that you want to use as sources of data.

    2. Specify the fields that you want to include from the data sources.

    3. Optionally, specify criteria to limit the records that the query returns.

                                                   21
University of Sunderland                                                      Garry Storer
                                     Microsoft Access 2007

After you have created a select query, you run it to see the results. To run a select query, you open
it in Datasheet view. If you save the query, you can reuse it whenever you need, for example, as a
data source for a form, report, or another query.

Use the Query Wizard to create a select query
You can use the Query Wizard to automatically create a select query. When you use the wizard, you
have less control over the details of the query design, but the query is usually created faster than if
you did not use the wizard. Moreover, the wizard can catch some simple design mistakes and
prompt you to perform a different action.

Some design elements are not available when you use the wizard. However, when you finish using
the wizard, you can modify the query in Design view.

Before you begin
If you use fields from data sources that are not related to each other, the Query Wizard asks you if
you want to create relationships. The wizard opens the Relationships window for you, but you must
restart the wizard if you edit any relationships. Therefore, before you run the wizard, consider
creating any relationships that your query needs.

Use the Query Wizard
    1. On the Create tab, in the Other group, click Query Wizard.

    2. In the New Query dialog box, click Simple Query Wizard, and then click OK.

    3. Next, you add fields. You can add up to 255 fields from as many as 32 tables or queries.

For each field, perform these two steps:

            1. Under Tables/Queries, click the table or query that contains the field.

            2. Under Available Fields, double-click the field to add it to the Selected Fields list.

When you have added all the fields that you want, click Next.

    4. If you did not add any number fields (fields that contain numeric data), skip ahead to step 9.
       If you added any number fields, the wizard asks whether you want the query to return
       details or summary data.

Do one of the following:

                If you want to see individual records, click Details, and then click Next. Skip ahead to
                 step 9.

                If you want to see summarized numeric data, such as averages, click Summary, and
                 then click Summary Options.

    5. In the Summary Options dialog box, specify which fields you want to summarize, and how
       you want to summarize the data. Only number fields are listed.

For each number field, choose one of the following functions:

                                                    22
University of Sunderland                                                       Garry Storer
                                    Microsoft Access 2007

                Sum The query returns the sum of all the values of the field.

                Avg The query returns the average of the values of the field.

                Min The query returns the smallest value of the field.

                Max The query returns the largest value of the field.

    6. If you want the query results to include a count of the records in a data source, select the
       appropriate Count records in data source name check box.

    7. Click OK to close the Summary Options dialog box.

    8. If you did not add a date/time field to the query, skip ahead to step 9. If you added a date-
       time field to the query, the Query Wizard asks you how you would like to group the date
       values. For example, suppose you added a number field ("Price") and a date/time field
       ("Transaction_Time") to your query, and then specified in the Summary Options dialog box
       that you want to see the average value of the number field "Price". Because you included a
       date/time field, you could calculate summary values for each unique date/time value, for
       each day, for each month, for each quarter, or for each year.

Note In Design view, you can use an expression to group by any time period you want, but the
wizard only offers these choices.

Select the time period that you want to use to group the date/time values, and then click Next.

    9. Give the query a title, specify whether you want to open or modify the query, and then click
       Finish.

If you choose to open the query, the query displays the selected data in Datasheet view. If you
choose to modify the query, the query opens in Design view.

Create a query by working in Design view
You can use Design view to manually create a select query. When you use Design view, you have
more control over the details of the query design, but it is easier to make design mistakes, and it
can take longer than using the wizard.

Step 1: Add data sources
When you use Design view, because you use the Show Table dialog box to add data sources, you
add the data sources and fields in separate steps. However, you can always add more data sources
later if you want.

    1. On the Create tab, in the Other group, click Query Design.

    2. In the Show Table dialog box, on the Both tab, double-click each data source that you want
       to use.

    3. Close the Show Table dialog box.



                                                   23
University of Sunderland                                                     Garry Storer
                                   Microsoft Access 2007

Automatic joins
When you add the data sources, if the sources already have relationships defined between them,
those relationships are automatically added to the query as joins. Joins specify how data from
related sources should be combined. Access also automatically creates a join between two tables if
they have fields have compatible data types and one field is a primary key.

You might want to adjust the joins that Access creates. Access determines what type of join to
create based on the relationship the join represents. If Access creates a join but there is no defined
relationship, Access creates an inner join.

Use the same data source several times
In some cases, you want to join two copies of the same table or query, called a self-join, that
combines records from the same table when there are matching values in the joined fields. For
example, say you have an Employees table in which the ReportsTo field for each employee's record
displays his or her manager's ID instead of name. You could use a self-join to display the manager's
name in each employee's record instead.

When you add a data source a second time, Access appends _1 to the name of the second instance.
For example, if you added the Employees table twice, the second instance would be named
Employees_1.

Step 2: Join related data sources
If the data sources that you add to a query already have relationships, Access automatically creates
an inner join for each relationship. If referential integrity is enforced, Access also displays a "1"
above the join line to show which table is on the "one" side of a one-to-many relationship and an
infinity symbol (∞) to show which table is on the "many" side.

If you add queries to your query, and have not created relationships between those queries, Access
does not automatically create joins between those queries, or between queries and tables that are
not related. If Access does not create joins when you add data sources, you should usually add
them yourself. Data sources that are not joined to any other data source can cause problems with
the query results.

You might also want to change the type of a join from an inner join to an outer join, so that your
query includes more records.

Add a join
    To add a join, drag a field from one data source to a corresponding field on another data
       source.

Access displays a line between the two fields to show that a join has been created.




                                                  24
University of Sunderland                                                     Garry Storer
                                   Microsoft Access 2007




Change a join
   1. Double-click the join you want to change.

The Join Properties dialog box appears.




Join Properties dialog box. Note that the names of tables in the dialog box change to reflect the
actual tables involved.

    2. In the Join Properties dialog box, review the three options.

    3. Click the option that you want to use, and then click OK.

After the joins are ready, you add output fields — fields that have data that you want in the query
results.

Step 3: Add output fields
You can easily add a field from any of the data sources that you added in step 1.
    To add a field, drag the field from a data source in the upper pane of the query design
       window down to the Field row of the design grid, in the bottom pane of the query design
       window.

When you add a field this way, Access automatically fills in the Table row of the design grid to
reflect the data source of the field.

Use an expression as an output field
If you want to perform calculations or use a function to produce query output, you can use an
expression as an output field. An expression can use data from any of the query data sources, as
well as functions, such as Format or InStr, and can also contains constants and arithmetic operators.

                                                  25
University of Sunderland                                                    Garry Storer
                                    Microsoft Access 2007

    1. In an empty column of the query design grid, right-click the Field row, and then click Zoom
       on the shortcut menu.

    2. In the Zoom box, type or paste your expression. Preface your expression with the name you
       would like to use for the expression output, followed by a colon. For example, if you wanted
       the expression to be labeled "Last updated", you would start your expression with Last
       updated:.

Note You can do a wide variety of things by using expressions. A thorough explanation of
expressions is beyond the scope of this article.

Step 4: Specify criteria
This step is optional.

You use criteria to limit the records that your query returns, on the basis of whether field values
meet the criteria that you specify.

                                                         Specify criteria for an output field
                                                            1. In the query design grid, in the Criteria
                                                                row of the field that has values that you
                                                                want to limit, type an expression that
                                                                field values must satisfy to be included
                                                                in your results. For example, if you
                                                                wanted to limit a query so that only
                                                                records where the value of the field City
                                                                is Las Vegas, type Las Vegas in the
                                                                Criteria row under that field.
                                                            2. Specify any alternate criteria in the Or
                                                                row, below the Criteria row.

If you specify alternate criteria, a field value can meet any of the listed criteria and be included in
the query result.

Multiple field criteria
You can use criteria with multiple fields. When you do, all the criteria in a given Criteria or Or row
must be true for the record to be included.

Specify criteria by using a field that you don't want to output
You can add a field to your query design and not include the field's data in the query output. You do
this if you want to use the field's values to limit the query results, but don't want to see the field
values.
    1. Add the field to the design grid.

    2. Clear the check box in the Show row for the field.

    3. Specify criteria as you would for an output field.

Step 5: Summarize data
This step is optional.

                                                    26
University of Sunderland                                                        Garry Storer
                                  Microsoft Access 2007

You might want to summarize data, especially if your data is numeric. For example, you might want
to see the average price, or total sales.

To summarize data in a query, you use the Total row. By default, the Total row is not displayed in
Design view.

    1. With the query open in Design view, on the Design tab, in the Show/Hide group, click Totals.

Access displays the Total row in the query design grid.

    2. For each field that you want to summarize, choose the function to use from the list in the
       Total row.




                                                 27
University of Sunderland                                                   Garry Storer
                                     Microsoft Access 2007

Create a form by using the Form Wizard
Access provides several quick-create form tools on the Create tab, each of which lets you create a
form with a single click. However, if you want to be more selective about what fields appear on the
form, you can use the Form Wizard instead. The wizard also lets you define how the data is grouped
and sorted, and you can use fields from more than one table or query (provided that you specified
the relationships between the tables and queries beforehand).


Start the Form Wizard
 1.     On the Create tab, in the Forms group, click More Forms, and then click Form Wizard
         .
 2.     Follow the directions on the pages of the Form Wizard.

       NOTE If you want to include fields from multiple tables and queries on the form, do not
      click Next or Finish after you select the fields from the first table or query on the first page
      of the Form Wizard. Instead, repeat the steps to select a table or query, and then click any
      additional fields that you want to include on the form. Then click Next or Finish to
      continue.

 3.     On the last page of the wizard, click Finish.

The Form Wizard can create a variety of results depending on the options that you select. As a
result, we recommend that you run the wizard several times, experimenting with different options
each time, until you get the results that you want.

Create a form by using the Blank Form tool
Access provides several form-creation tools on the Create tab, which you can use to quickly create
a form. If these tools do not fit your needs, you can use the Blank Form tool instead. As the name
implies, this tool opens a new, blank form, which you can then customize and format the way you
want. This can be a very quick way to build a form, especially if you plan to put only a few fields on
the form.




                                                     28
University of Sunderland                                                        Garry Storer
                                    Microsoft Access 2007

Create a blank form
                                             1.     On the Create tab, in the Forms group, click
                                                  Blank Form.

                                             Access opens a blank form in Layout view, and
                                             displays the Field List task pane.

                                             2.      In the Field List task pane, expand the table
                                                  or tables that contain the fields that you want
                                                  to see on the form.
                                             3.      To add a field to the form, double-click it or
                                                  drag it onto the form. To add several fields at
                                                  the same time, hold down the CTRL key, click
                                                  the fields that you want to add, and then drag
                                                  them onto the form.




                                              NOTE The order of the tables in the Field List pane
                                             can change, depending on which part of the form is
                                             currently selected. If you are not able to add a field
                                             to the form, try selecting a different part of the form
                                             and then try adding the field again. On the Format
                                             tab, use the tools in the Controls group to add a
                                             logo, title, page numbers, or the date and time to
      the form.

 4.     If you want to add a wider variety of controls to the form, switch to Design view by
      right-clicking the form and then clicking Design View     . You can then use the tools in the
      Controls group on the Design tab.




      To add a control, click the tool for the control you want, and then click on the form where
      you want to position it.




                                                   29
University of Sunderland                                                      Garry Storer
                                        Microsoft Access 2007

Create a form by using the Form tool
You can use the Form tool to quickly create a single item form. This type of form displays
information about one record at a time, as shown in the following illustration:



                                                                        The form displays information for
                                                                     a single record.

                                                                        In some cases, Access adds a
                                                                     subdatasheet to display related
                                                                     information. For more information,
                                                                     see the section Automatic
                                                                     subdatasheet creation.

                                                                     When you use the Form tool, all the
                                                                     fields from the underlying data
                                                                     source are added to the form. You
                                                                     can start to use the new form
                                                                     immediately, or you can modify it in
                                                                     Layout view or Design view to better
suit your needs.
Create a single item form
 1.          In the Navigation Pane, click the table or query that contains the data that you want to
          see on the form.

 2.         On the Create tab, in the Forms group, click Form.

          Access creates the form and displays it in Layout view. In Layout view, you can make
          design changes to the form while it is displaying data. For example, you can adjust the size
          of the text boxes to fit the data.

 3.         To begin working with the form, switch to Form view:
                   On the Home tab, in the Views group, click View, and then click Form View

Automatic subdatasheet creation
If Access finds a table that has a one-to-many relationship with the table or query that you used to
create the form, Access adds a subdatasheet to the form that is based on the related table or
query. For example, if you create a simple form that is based on the Customer table, and there is a
one-to-many relationship defined between the Customer table and Orders table, the subdatasheet
displays all the records in the Orders table that pertain to the current Customer record. If you
decide that you do not want the subdatasheet on the form, you can delete the subdatasheet by
switching to Layout view, selecting the datasheet, and then pressing DELETE.

If there is more than one table with a one-to-many relationship to the table that you used to create
the form, Access does not add any datasheets to the form.

                                                       30
University of Sunderland                                                         Garry Storer
                                    Microsoft Access 2007

Create a simple report
You can create a variety of different reports in Microsoft Office Access 2007, ranging from the
simple to the complex. Begin by thinking about your report's record source. Whether your report is
a simple listing of records or a grouped summary of sales by region, you must first determine which
fields contain the data you want to see in your report, and in which tables or queries they reside.

After you choose your record source, you will usually find it is easiest to create your report by using
the Report Wizard. The Report Wizard is a feature in Access that guides you through a series of
questions and then generates a report based on your answers.


Choose a record source
A report consists of information that is pulled from tables or queries, as well as information that is
stored with the report design, such as labels, headings, and graphics. The tables or queries that
provide the underlying data are also known as the report's record source. If the fields that you want
to include all exist in a single table, use that table as the record source. If the fields are contained in
more than one table, you need to use one or more queries as the record source. Those queries may
already exist in your database, or you may need to create new queries specifically to fit the needs
of your report. You can find links to more information about queries in the See Also section.


Create a report by using the Report tool
The Report tool provides the fastest way for you to create a report, because it generates a report
immediately without prompting you for information. The report displays all the fields from the
underlying table or query. The Report tool may not create the final, polished product that you
ultimately want, but it is quite useful as a means to quickly look at the underlying data. You can
then save the report and modify it in Layout view or Design view so that it better serves your
purposes.
 1.     In the Navigation Pane, click the table or query on which you want to base the report.

 2.     On the Create tab, in the Reports group, click Report.

      Access builds the report and displays it in Layout view.

After viewing the report, you can save it and then close both the report and the underlying table or
query that you used as a record source. The next time that you open the report, Access will display
the most recent data from your record source.


Create a report by using the Report Wizard
You can use the Report Wizard to be more selective about what fields appear on your report. You
can also specify how the data is grouped and sorted, and you can use fields from more than one
table or query, provided you have specified the relationships between the tables and queries
beforehand. Find links to more information about creating relationships in the See Also section.
 1.     On the Create tab, in the Reports group, click Report Wizard.
 2.     Follow the directions on the Report Wizard pages. On the last page, click Finish.
                                                    31
University of Sunderland                                                        Garry Storer
                                     Microsoft Access 2007
      When you preview the report, you see the report as it will appear in print. You can also
      increase the magnification to zoom in on details.

NOTE If you want to include fields from multiple tables and queries in your report, do not click
Next or Finish after you select the fields from the first table or query on the first page of the Report
Wizard. Instead, repeat the steps to select a table or query, and click any additional fields that you
want to include in the report. Then, click Next or Finish to continue.


Create labels by using the Label Wizard
Use the Label Wizard to easily create labels for a wide variety of standard label sizes.

 1.      In the Navigation Pane, open the table or query that will be the record source for your
      labels by double-clicking it.

 2.      On the Create tab, in the Reports group, click Labels.
 3.      Follow the directions on the pages of the Label Wizard. On the last page, click Finish.

Access displays your labels in Print Preview so that you can see them as they will appear when they
are printed. You can use the slider control on the Access status bar to zoom in on details.

 NOTE Print Preview is the only view you can use to see multiple columns — the other views show
the data in a single column.


Create a report by using the Blank Report tool
If you aren't interested in using the Report tool or the Report Wizard, you can use the Blank Report
tool to build a report from scratch. This can be a very quick way to build a report, especially if you
plan to put only a few fields on your report. The following procedure explains how to use the Blank
Report tool:
 1.     On the Create tab, in the Reports group, click Blank Report.

      A blank report is displayed in Layout view, and the Field List pane is displayed on the right
      side of the Access window.

 2.      In the Field List pane, click the plus sign next to the table or tables containing the fields
      that you want to see on the report.
 3.      Drag each field onto the report one at a time, or hold down CTRL and select several
      fields, and then drag them onto the report at the same time.
 4.     Use the tools in the Controls group on the Format tab to add a logo, title, page
      numbers, or the date and time to the report.




                                                     32
University of Sunderland                                                         Garry Storer
                                   Microsoft Access 2007


Understand the report sections
In Access, the design of a report is divided into sections. You can view your report in Design view to
see its sections. To create useful reports, you need to understand how each section works. For
example, the section in which you choose to place a calculated control determines how Access
calculates the results. The following list is a summary of the section types and their uses:

        Report Header This section is printed just once, at the beginning of the report. Use the
     report header for information that might normally appear on a cover page, such as a logo,
     a title, or a date. When you place a calculated control that uses the Sum aggregate
     function in the report header, the sum calculated is for the entire report. The report
     header is printed before the page header.
      Page Header This section is printed at the top of every page. For example, use a page
     header to repeat the report title on every page.
       Group Header This section is printed at the beginning of each new group of records.
     Use the group header to print the group name. For example, in a report that is grouped by
     product, use the group header to print the product name. When you place a calculated
     control that uses the Sum aggregate function in the group header, the sum is for the
     current group.
       Detail This section is printed once for every row in the record source. This is where you
     place the controls that make up the main body of the report.
       Group Footer This section is printed at the end of each group of records. Use a group
     footer to print summary information for a group.
      Page Footer This section is printed at the end of every page. Use a page footer to print
     page numbers or per-page information.
       Report Footer This section is printed just once, at the end of the report. Use the report
     footer to print report totals or other summary information for the entire report.

      NOTE In Design view, the report footer appears below the page footer. However, when
     the report is printed or previewed, the report footer appears above the page footer, just
     after the last group footer or detail line on the final page.


Understand controls
Controls are objects that display data, perform actions, and let you view and work with information
that enhances the user interface, such as labels and images. Access supports three types of
controls: bound, unbound, and calculated:

       Bound control A control whose source of data is a field in a table or query is a bound
     control. You use bound controls to display values from fields in your database. The values
     can be text, dates, numbers, Yes/No values, pictures, or graphs. A text box is the most
     common type of bound control. For example, a text box on a form that displays an
     employee's last name might get this information from the Last Name field in the
     Employees table.
                                                  33
University of Sunderland                                                     Garry Storer
                                    Microsoft Access 2007

       Unbound control A control that doesn't have a source of data (a field or expression) is
     an unbound control. You use unbound controls to display information, lines, rectangles,
     and pictures. For example, a label that displays the title of a report is an unbound control.
        Calculated control A control whose source of data is an expression rather than a field
     is a calculated control. You specify the value that you want in the control by defining an
     expression as the source of data for the control. An expression is a combination of
     operators (such as = and + ), control names, field names, functions that return a single
     value, and constant values. For example, the following expression calculates the price of an
     item with a 25 percent discount by multiplying the value in the Unit Price field by a
     constant value (0.75).
     = [Unit Price] * 0.75

     An expression can use data from a field in the report's underlying table or query, or from a
     control in the report.

When you create a report, it is probably most efficient to add and arrange all the bound controls
first, especially if they make up the majority of the controls on the report. You can then add the
unbound and calculated controls that complete the design by using the tools in the Controls group
on the Design tab.

You bind a control to a field by identifying the field from which the control gets its data. You can
create a control that is bound to the selected field by dragging the field from the Field List pane to
the report. The Field List pane displays the fields of the report's underlying table or query. To
display the Field List pane, on the Design tab, in the Controls group, click Add Existing Field.

Alternatively, you can bind a field to a control by typing the field name in the control itself or in the
box for the ControlSource value in the control's property sheet. The property sheet defines the
characteristics of the control, such as its name, the source of its data, and its format.

Using the Field List pane is the best way to create a control for two reasons:

       A bound control has an attached label, and the label takes the name of the field (or the
     caption defined for that field in the underlying table or query) as its caption by default, so
     you don't have to type the caption yourself.
       A bound control inherits many of the same settings as the field in the underlying table
     or query (such as for the Format, DecimalPlaces, and InputMask properties). Therefore,
     you can be sure that these properties for the field remain the same whenever you create a
     control that is bound to that field.

If you already created an unbound control and want to bind it to a field, set the control's
ControlSource property to the name of the field. For details about the ControlSource property,
search Help for "ControlSource."




                                                   34
University of Sunderland                                                       Garry Storer
                                   Microsoft Access 2007

Fine-tune your report in Layout view
After you create a report, you can easily fine-tune its design by working in Layout view. Using the
actual report data as your guide, you can adjust the column widths, rearrange the columns, and add
grouping levels and totals. You can place new fields on the report design and set the properties for
the report and its controls.
To switch to Layout view, right-click the report name in the Navigation Pane and then click Layout
View      .

Access shows the report in Layout view.

You can use the property sheet to modify the properties for the report and its controls and
sections. To display the property sheet, press F4.

You can use the Field List pane to add fields from the underlying table or query to your report
design. To display the Field List pane, do one of the following:

       On the Format tab, in the Controls group, click Add Existing Fields.
       Press ALT+F8.
You can then add fields by dragging them from the Field List pane to the report.


Fine-tune your report in Design view
You can also fine-tune your report's design by working in Design view. You can add new controls
and fields to the report by adding them to the design grid. The property sheet gives you access to a
large number of properties that you can set to customize your report.

To switch to Design view, right-click the report name in the Navigation Pane and then click Design
View      .

Access shows the report in Design view.

You can use the property sheet to modify the properties for the report itself and the controls and
sections it contains. To display the property sheet, press F4.

You can use the Field List pane to add fields from the underlying table or query to your report
design. To display the Field List pane, do one of the following:

       On the Format tab, in the Controls group, click Add Existing Fields.
       Press ALT+F8.
You can then add fields by dragging them from the Field List pane to the report.




                                                  35
University of Sunderland                                                       Garry Storer
                                       Microsoft Access 2007


Add fields from the Field List pane
       To add a single field, drag the field from the Field List pane to the section where you
      want it displayed on the report.
        To add several fields at once, hold down CTRL and click the fields that you want. Then,
      drag the selected fields onto the report.

When you drop the fields onto a report section, Access creates a bound text box control for each
field and automatically places a label control beside each field.


Add controls to the report
Some controls are created automatically, such as the bound text box control that is created when
you add a field from the Field List pane to your report. Many other controls can be created in
Design view by using the tools in the Controls group on the Design tab.




Determine the name of a tool
          Place the mouse pointer over the tool.

      Access displays the name of the tool.

Create a control by using the tools in the Controls group
 1.        Click the tool for the type of control that you want to add. For example, to create a
       check box, click the Check Box       tool.
 2.       Click in the report design grid where you want to position the upper-left corner of the
       control. Click once to create a default-sized control, or click the tool and then drag in the
       report design grid to create a control of the size that you want.
 3.       If you don't position the control perfectly on the first try, you can move it by using the
       following procedure:
      1.          Click the control to select it.
      2.           Position the mouse pointer over the edge of the control until the pointer
           turns into a four-headed arrow .
      3.          Drag the control to the location that you want.

This procedure creates an "unbound" control. If the control is the type that can display data (a text
box or check box, for example), you need to enter a field name or expression in the ControlSource
property for the control before it will display any data.


                                                     36
University of Sunderland                                                        Garry Storer
                                     Microsoft Access 2007

Display the property sheet
To display the property sheet in Design view, do one of the following:

        On the Design tab, in the Show/Hide group, click Property Sheet.

        Press F4.

Save your report design
 1.      Click the Microsoft Office Button      and then click Save, or press CTRL+S.
      Alternatively, click Save   on the Quick Access Toolbar.

 2.      If the report is untitled, type a name in the Report Name box, and then click OK.

Save your report design under a new name
 1.      Click the Microsoft Office Button      , and then click Save As.

 2.      In the Save As dialog box, type a name in the Save Report to box, select Report in the
      As box, and then click OK.

View, print, or send your report as an e-mail message
After you save your report design, you can use it over and over again. The report's design stays the
same, but you get current data every time you view or print the report. If your reporting needs
change, you can modify the report design or create a new, similar report based on the original.

View your report
There are several ways to view your report. Which method you choose depends on what you want
to do with the report and its data:

        If you want to make temporary changes to which data appears on the report before you
      print it, or if you want to copy data from the report to the clipboard, use Report view.
        If you want to be able to change the design of the report while looking at the data, use
      Layout view.
        If you simply want to see what the report will look like when it is printed, use Print
      Preview.

 NOTE If your report is formatted with multiple columns, you can only see the column layout in
Print Preview. Layout view and Report view display the report as a single column.

View your report in Report view
Report view is the default view that is used when you double-click a report in the Navigation Pane.
If the report is not open, double-click the report in the Navigation Pane to see it in Report view.

If the report is already open, right-click the report name in the Navigation Pane and then click
Report View.
                                                    37
University of Sunderland                                                       Garry Storer
                                     Microsoft Access 2007

Work with your data in Report view
In Report view, you can select text and copy it to the clipboard. To select entire rows, click and drag
in the margin next to the rows that you want to select. You can then copy these rows to the
clipboard by doing one of the following:

       On the Home tab, in the Clipboard group, click Copy.
       Right-click the selected rows and then click Copy.

       Keyboard shortcut Press CTRL+C.

Show only the rows you want by using filters
You can apply filters directly to your report without leaving Report view. For example, if you have a
"Country/region" column and you want to see only those rows where the country/region is
"Canada", do the following:

 1.     Find the word "Canada" in the report and right-click it.

 2.     Click Equals "Canada".

Access will create and apply the filter.

Create a more detailed filter
 1.     Right-click the field that you want to filter.

 2.     Click Common Filters .

 3.     Click the filter condition that you want.
 4.     Enter your criteria.

Toggle a filter on and off
You can switch between a filtered and non-filtered display by clicking Toggle Filter in the Sort &
Filter group of the Home tab. This does not remove the filter — it just turns it on and off.

Remove a filter
 1.     Right-click the field from which you want to remove the filter.
 2.     Click Remove Filter.
Once a filter has been removed, you cannot switch it back on by using the Toggle Filter command.
You must first re-create the filter.

 NOTE If you apply a filter to a report and then save and close the report, the filter will be saved.
However, the next time you open the report, Access will not apply the filter. To reapply the filter,
on the Home tab, in the Sort & Filter group, click Toggle Filter.

Preview your report by using Print Preview

Right-click the report in the Navigation Pane, and then click Print Preview on the shortcut menu.
                                                     38
University of Sunderland                                                     Garry Storer
                                    Microsoft Access 2007

You can use the navigation buttons to view the pages of a report sequentially or to jump to any
page in the report.




    Click to display the first page.
    Click to display the previous page.
    Click to display the next page.
    Click to display the last page.
    Type a page number in this box and then press ENTER to jump to a specific page.

In Print Preview, you can zoom in to see details or zoom out to see how well the data is positioned
on the page. With the cursor positioned over the report, click once. To reverse the effect of the
zoom, click again. You can also use the zoom control on the Access status bar to zoom further in or
out.

To close Print Preview, do one of the following:

        On the Print Preview tab, click Close Print Preview.

        Right-click the report in the Navigation Pane and then click Layout View       or Design
      View     on the shortcut menu.

 TIP After previewing your report, you can export the results to Microsoft Office Word 2007,
Microsoft Office Excel 2007, or several other Office programs. On the External Data tab, in the
Export Data group, click the button for the format that you want and follow the instructions.


Print your report
You can print a report while it is open in any view, or even while it is closed. Before you print, be
sure to double-check the page settings, such as the margins or page orientation. Access saves the
page settings with the report, so you need to set them only once. You can set them again later, if
your printing needs change.


Change the page settings
 1.      Open the report in Print Preview. You can change page settings in any view, but Print
      Preview is best because you can see the effects of any changes immediately.

 2.      On the Print Preview tab, in the Page Layout group, click Portrait     or Landscape
      to set the page orientation, Size   to set the paper size, Margins      to adjust the
      margins, and so on.



                                                   39
University of Sunderland                                                       Garry Storer
                                    Microsoft Access 2007

 3.      After you make a change, use the navigation buttons to view several pages to ensure
      that you haven't created any formatting problems on later pages.

Send your report to a printer
 1.     Open the report in any view, or select the report in the Navigation Pane.

 2.     Click the Microsoft Office Button       , and then click Print.

      Access displays the Print dialog box.

 3.     Enter your choices for options such as printer, print range, and number of copies.
 4.     Click OK.


Send your report as an e-mail message
You can send your report to recipients as an e-mail message instead of printing a paper copy:
 1.    In the Navigation Pane, click the report to select it, click the Microsoft Office Button
          , and then click E-mail   .
 2.      In the Send Object As dialog box, in the Select Output Format list, click the file format
      that you want to use.




 3.     Complete any remaining dialog boxes.
 4.     In your e-mail application, type the message details and send the message.




                                                    40
University of Sunderland                                                      Garry Storer

								
To top