Docstoc

MS ACCESS 2003

Document Sample
MS ACCESS 2003 Powered By Docstoc
					ACCESS 2003 – LEVEL 1
LESSON 1 - EXPLORING ACCESS ......................................................................1
   Starting Access .........................................................................................................2
   Opening an Existing Database..................................................................................4
   Using Menu Commands ...........................................................................................6
   Displaying and Hiding Toolbars ..............................................................................8
   Displaying Hidden Toolbar Buttons .......................................................................10
   Changing Menu and Toolbar Options ....................................................................11
   Using Database Objects ..........................................................................................13
   Using the Database Window ..................................................................................14
   Opening a Database Object ....................................................................................16
   Using the Task Pane ...............................................................................................18
   Exiting Access ........................................................................................................20
   Exercise ..................................................................................................................22
      Exploring Access................................................................................................22

LESSON 2 - USING ONLINE HELP ....................................................................24
   Use Access Help .....................................................................................................25
   Work with Help Task Pane.....................................................................................26
   Use Ask a Question ................................................................................................28
   Modify Properties of Online Content .....................................................................29
   Use Microsoft Office Online Help .........................................................................31
   Display and Hide the Office Assistant ...................................................................32
   Finding an Answer .................................................................................................34
   Changing Office Assistant Options ........................................................................35
   Exercise ..................................................................................................................38
      using online help and office assistant .................................................................38

LESSON 3 - WORKING WITH TABLES ............................................................40
   Using Datasheet View ............................................................................................41
   Navigating Fields in Tables ....................................................................................41
   Adding Records ......................................................................................................42
   Moving through Records ........................................................................................44
   Selecting Records ...................................................................................................46
   Editing Records ......................................................................................................47


Canadian Beaver (CBIT)                                                                                                             Page i
   Saving Records .......................................................................................................49
   Deleting Records ....................................................................................................50
   Exercise ..................................................................................................................52
      Working with Tables ..........................................................................................52

LESSON 4 - EDITING TABLES ...........................................................................53
   Changing the Row Height ......................................................................................54
   Changing the Column Width ..................................................................................55
   Changing a Font Attribute ......................................................................................56
   Changing a Cell Effect ...........................................................................................58
   Selecting a Column.................................................................................................59
   Moving a Column ...................................................................................................60
   Hiding a Column ....................................................................................................62
   Unhiding a Column ................................................................................................63
   Freezing a Column .................................................................................................64
   Exercise ..................................................................................................................66
      Editing Tables ....................................................................................................66

LESSON 5 - CREATING TABLES .......................................................................68
   Using the Database Wizard ....................................................................................69
   Creating a New Database .......................................................................................74
   Designing Tables ....................................................................................................75
   Creating a Table in Design View ...........................................................................76
   Using Design View.................................................................................................78
   Adding Field Names ...............................................................................................79
   Assigning Data Types.............................................................................................80
   Adding a Field Description ....................................................................................83
   Setting a Primary Key ............................................................................................84
   Saving a New Table................................................................................................85
   Using the Table Wizard ..........................................................................................86
   Exercise ..................................................................................................................91
      Creating Tables ..................................................................................................91

LESSON 6 - SETTING FIELD PROPERTIES ....................................................93
   Using Field Properties ............................................................................................94
   Limiting Field Size .................................................................................................95
   Setting Number Formats.........................................................................................97

Page ii                                                                                                    Canadian Beaver (CBIT)
   Setting Date/Time Formats...................................................................................100
   Setting Yes/No Formats .......................................................................................102
   Setting Default Values ..........................................................................................104
   Setting Validation Rules .......................................................................................106
   Creating an Input Mask - Wizard .........................................................................108
   Creating an Input Mask Manually ........................................................................111
   Creating a Custom Input Mask .............................................................................114
   Typing a Lookup List ...........................................................................................117
   Modifying Lookup Properties ..............................................................................120
   Exercise ................................................................................................................123
      Setting Field Properties ....................................................................................123

LESSON 7 - CREATING RELATIONSHIPS ....................................................126
   Using Related Tables ............................................................................................127
   Creating a Relationship between Tables ..............................................................128
   Setting Referential Integrity .................................................................................131
   Viewing Subdatasheets.........................................................................................134
   Deleting a Join Line..............................................................................................136
   Exercise ................................................................................................................138
      Creating Relationships .....................................................................................138

LESSON 8 - PRINTING DATA ...........................................................................140
   Printing Table Data...............................................................................................141
   Changing the Page Setup ......................................................................................142
   Using Print Preview..............................................................................................144
   Printing Selected Records.....................................................................................146
   Exercise ................................................................................................................149
      Printing Data ....................................................................................................149

LESSON 9 - FINDING AND FILTERING DATA .............................................150
   Sorting Records ....................................................................................................151
   Finding Specific Records .....................................................................................152
   Finding Records using Wildcards ........................................................................154
   Using Replace.......................................................................................................157
   Using Filter By Selection .....................................................................................159
   Applying/Removing a Filter .................................................................................161
   Using Filter Excluding Selection..........................................................................162

Canadian Beaver (CBIT)                                                                                                            Page iii
   Using Filter For ....................................................................................................163
   Using Filter By Form............................................................................................165
   Exercise ................................................................................................................168
      Finding and Filtering Data ...............................................................................168

LESSON 10 - USING SIMPLE QUERIES..........................................................169
   Using Queries and Recordsets ..............................................................................170
   Using the Simple Query Wizard...........................................................................170
   Creating a Query in Design View.........................................................................173
   Opening a Query...................................................................................................176
   Adding a Table to a Query ...................................................................................177
   Joining Tables in a Query .....................................................................................179
   Running a Query...................................................................................................181
   Exercise ................................................................................................................183
      Using Simple Queries.......................................................................................183

LESSON 11 - MODIFYING QUERY RESULTS ...............................................185
   Sorting a Query.....................................................................................................186
   Adding Criteria to a Query ...................................................................................188
   Hiding a Field in a Query .....................................................................................189
   Adding a Record using a Query ...........................................................................190
   Printing a Query ...................................................................................................192
   Exercise ................................................................................................................194
      Modifying Query Results .................................................................................194

LESSON 12 - USING OPERATORS IN QUERIES...........................................196
   Using Comparison Operators ...............................................................................197
   Using an And Condition .......................................................................................199
   Using an Or Condition..........................................................................................201
   Using the Between And Operator .........................................................................203
   Using the Expression Builder ...............................................................................205
   Using a Wildcard Character .................................................................................208
   Exercise ................................................................................................................210
      Using Operators in Queries ..............................................................................210

LESSON 13 - ANALYZING TABLES ................................................................212
   Analyzing a Table.................................................................................................213


Page iv                                                                                                   Canadian Beaver (CBIT)
    Using Relationships in Splits................................................................................213
    Using the Table Analyzer Wizard ........................................................................213
    Using the Performance Analyzer ..........................................................................218
    Exercise ................................................................................................................221
       Analyzing Tables..............................................................................................221

LESSON 14 - CREATING BASIC FORMS .......................................................222
    Using Forms .........................................................................................................223
    Using the Form Wizard ........................................................................................223
    Viewing Records in a Form ..................................................................................226
    Printing Records in a Form...................................................................................228
    Basing a Form on a Query ....................................................................................229
    Using AutoForm ...................................................................................................232
    Adding a Record using a Form .............................................................................233
    Exercise ................................................................................................................236
       Creating Basic Forms .......................................................................................236

LESSON 15 - CREATING BASIC REPORTS ...................................................238
    Using Reports .......................................................................................................239
    Using the Report Wizard ......................................................................................239
    Using Print Preview - Reports ..............................................................................244
    Printing Pages of a Report ....................................................................................247
    Grouping and Summarizing Report Data .............................................................249
    Basing a Report on a Query..................................................................................253
    Using AutoReport.................................................................................................257
    Exercise ................................................................................................................259
       Creating Basic Reports .....................................................................................259

LESSON 16 - EXPORTING DATA TO EXCEL AND WORD ........................261
    Exporting Data to an Excel Workbook.................................................................262
    Dragging and Dropping Data into Excel ..............................................................264
    Creating Excel Workbooks - Office Links ...........................................................265
    Creating Word Documents - Office Links ...........................................................266
    Exercise ................................................................................................................269
       Exporting Data to Excel and Word ..................................................................269

INDEX......................................................................................................................270


Canadian Beaver (CBIT)                                                                                                             Page v
LESSON 1 -
EXPLORING ACCESS

In this lesson, you will learn how to:
         Start Access

         Open an existing database

         Use menu commands

         Display and hide toolbars

         Displaying Hidden Toolbar Buttons

         Change menu and toolbar options

         Use database objects

         Use the Database window

         Open a database object

         Use the task pane

         Exit Access
Lesson 1 - Exploring Access                                                    Access 2003 - Lvl 1




STARTING ACCESS

    Discussion
          A database is any collection of information organized into a group. The information
          should be organized in a way that allows for easy retrieval. For example, a telephone
          book is a non-computerized database of information. It is organized in alphabetical
          order and includes information such as names, addresses, and telephone numbers.
          Other examples of non-computerized databases include address books and inventory
          lists.

          Electronic databases can be maintained on a computer. Computerized databases allow
          you to manipulate large amounts of data quickly and easily. They simplify tasks such
          as searching for specific data, organizing and sorting data, and making corrections or
          changes to data.

          In Microsoft Access 2003, the database information is stored in data tables. Every data
          table has a structure that provides for the collection, organization, storage, and
          retrieval of data. These tables of information are contained in a database file. Each
          database file can have numerous data tables.

          A data table consists of fields and records. Fields are categories of information. For
          example, in an address table, you may maintain names, addresses, cities, states, and
          zip codes. Each of these categories is a field in the address database.

          The set of fields containing the data for a single entry is called a record. For example,
          Charles Hardy, 1234 Main St., Bridgeville, NY, 11012 is a record in the address data
          table. Each piece of information in the record is stored in a field and is referred to as a
          field value. NY is the field value for the State field in this particular record. An
          address table would most likely consist of a number of records, and the data values for
          each record would be stored in the appropriate fields.

          Access is a relational database application. A relational database contains a large
          amount of data that is split into numerous tables; each table should include only the
          information pertinent to one subject, such as Customer, Orders, or Items. As a
          result, each table is smaller; more manageable; and, in turn, more efficient. These
          tables can then be joined together to make them related. When tables are related, you
          can access information from any field in any related table, eliminating the need to add
          the same information to more than one table. In addition, you can create reports,
          forms, and queries from data stored in any related table.

          In addition to extensive built-in help, the Microsoft Office Assistant is available to
          offer tips and hints for using Access more efficiently. The Office Assistant, a context-
          sensitive Help feature, may appear when you start Access. You can adjust the
          capabilities of the Office Assistant, as well as choose not to have the Office Assistant
          appear at all.



Page 2                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                  Lesson 1 - Exploring Access


          Another method of obtaining help is the Ask a Question feature. You can type a
          question into the Ask a Question box on the menu bar to get help with a specific
          feature.

          Additionally, Office on the Web provides an online link to resources you can use to
          create better databases. For example, you can use Office on the Web to link to Internet
          sites, where you can access technical resources and free product enhancements.

          Microsoft Access 2003 is designed to run under the Windows operating system. By
          default, Windows creates a startup command for Access on the Programs submenu,
          which is accessed from the Start menu. Your system may be different, depending on
          how Access was installed or customized.

          When you start Access, the New File task pane appears on the right side of the
          application window. The New File task pane provides links for opening existing
          databases, as well as for creating new ones.



                     You can create a shortcut on the Windows desktop to start
                      Access.


                     The first time you open the Microsoft Access dialog box after
                      installation, the list of recently used files and the More Files
                      option will not be available in the New File task pane. As you
                      begin working with databases in Access, you will be able to
                      open recently used files under More Files in the Open a File
                      section of the task pane.


                     The first time you open Access after installation, the User
                      Name dialog box may appear. You can enter your name and
                      initials as necessary, and then select OK.




   Procedures
          1. Click the Start button on the taskbar.
          2. Point to Programs.
          3. Select Microsoft Access.




    Step-by-Step
          Start Microsoft Access 2003.


Canadian Beaver (CBIT)                                                                   Page 3
Lesson 1 - Exploring Access                                                    Access 2003 - Lvl 1



           Steps                                           Practice Data
           1. Click the Start button on the taskbar.       Click      Start
              The Start menu appears.
           2. Point to Programs.
              The Programs submenu appears.                Point to       Programs

           3. Select Microsoft Access.
                                                           Click    Microsoft
              Access starts and the New File task
                                                           Access 2003
              pane appears.



OPENING AN EXISTING DATABASE

    Discussion
          An Access database is a collection of information organized into a number of objects
          including tables, queries, forms, reports, pages, macros, and modules.

          When you want to work with an Access database, you must first load the database file
          into memory. This process enables you to open all the tables or other objects within
          that database.

          You can view or edit an existing database by opening it from disk. You do not need to
          remember the file name because the Open dialog box displays a list of folders and
          files in the current drive and folder. You can select the desired database file from the
          list, or you can type the name of the database you want to open.

          If the database resides in a different drive or folder, you can use the Look in list to
          select the correct location. The folders and files residing in the selected location
          appear in the box below the Look in box.

          In addition to using the Look in list to open databases, the Open dialog box contains a
          Places Bar on the left side of the dialog box. The Places Bar contains shortcuts to
          various folders containing commonly used files, as well as desktop options and
          recently opened files.

          If you want to protect the original version of a database from modifications, you can
          use the Open list to open a copy of the database or the database as read-only. Only
          one database can be open at any time.




Page 4                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                  Lesson 1 - Exploring Access




                                             The Database window




                     You can also access the Open dialog box by selecting the
                      More files link in the New File task pane, or by selecting the
                      File menu and then the Open command.


                     Access displays the names of the four most recently opened
                      databases at the bottom of the File menu and in the Open a
                      file list at the top of the New File task pane. When you click a
                      file name in the list, the corresponding database opens.


                     You can use the Tools menu in the Open dialog box to add the
                      currently selected folder to the Places Bar.




   Procedures
          1. Click the Open button            on the Standard toolbar.
          2. Select the Look in list.
          3. Select the drive where the database you want to open is located.
          4. Open the folder where the database you want to open is located.
          5. Select the name of the database you want to open.
          6. Select Open.



Canadian Beaver (CBIT)                                                                   Page 5
Lesson 1 - Exploring Access                                                 Access 2003 - Lvl 1




    Step-by-Step
          Open an existing database from a specific drive and folder location.

           Steps                                         Practice Data
           1. Click the Open button on the
              Standard toolbar.                          Click
              The Open dialog box opens.
           2. Select the Look in list.                   Click Look in
              A list of available drives appears.
           3. Select the drive where the database        Click the student data
              you want to open is located.               drive
              A list of available folders appears.
           4. Open the folder where the database         Double-click to open the
              you want to open is located.               student data folder
              The contents of the folder appear.
           5. Select the name of the database you        Scroll as necessary and
              want to open.                              click Wsgoods
              The file name is selected.
           6. Select Open.                               Click Open
              The Open dialog box and New File
              task pane close, and the database
              opens in the Database window.



USING MENU COMMANDS

    Discussion
          The menu bar, located below the application title bar, provides access to all the
          features of Access. Each menu contains commands grouped by function. When you
          select an item on the menu bar, a menu of corresponding commands appears, from
          which you can select the desired command. Some menu commands are dimmed,
          which indicates that the command is not available for the current task.

          Each menu appears in two stages, a short menu and a full menu. When you first select
          a menu, the short menu appears and displays the most frequently used commands.

          If a command does not appear on the short menu, you can click the double arrows that
          appear at the bottom of the menu. This action expands the short menu to display the
          full menu with all available commands. Once you have expanded a menu, all menus
          are expanded until you choose a command or perform an action. Other ways to

Page 6                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                  Lesson 1 - Exploring Access


          expand a menu include double-clicking the menu name on the menu bar or hovering
          over the double arrows or menu name with the mouse pointer.

          As soon as you select a command from the full menu, it is added to the short menu. In
          this way, your short menus are updated with the most frequently used commands.
          Commands stay on the short menu until you stop using them for a while or reset your
          data usage.

          When a menu command is followed by an ellipsis (...), selecting it opens a dialog box,
          in which additional information is entered. A menu command that displays a right-
          pointing triangle indicates a submenu. When you point to this command, the
          associated submenu cascades to the right.

          In addition to the standard menus on the menu bar, Access provides shortcut menus
          that are accessed by right-clicking text or an object such as a toolbar. Shortcut menus
          provide commonly used commands and are context-sensitive. Therefore, the options
          available on a shortcut menu vary, depending on the object clicked.



                     You can also use key combinations to execute menu
                      commands. To display a menu, press the [Alt] key and the
                      underlined letter in the menu name. You can then press the
                      underlined letter in the desired command to perform the
                      action.


                     Some Office 2003 components are available on an Install on
                      First Use basis. This term means that, although the component
                      appears on the menu, it is not actually installed until the first
                      time you use it. Office 2003 prompts you to install the
                      component and then installs it from the original installation
                      source.




   Procedures
          1. Click the desired menu.
          2. To display the full menu, click the double arrow at the bottom of the
             short menu.
          3. Click the desired command.




    Step-by-Step
          Use menu commands to perform an action.

Canadian Beaver (CBIT)                                                                     Page 7
Lesson 1 - Exploring Access                                                  Access 2003 - Lvl 1




           Steps                                          Practice Data
           1. Click the desired menu.                     Click View
              The selected short menu appears.
           2. To display the full menu, click the         Click at the bottom of
              double arrow at the bottom of the short     the Edit menu
              menu.
              The selected short menu expands to
              display all available commands.
           3. Click the desired command.                  Click Large Icons
              The appropriate action occurs.

          Practice the Concept: Using the keyboard, press [Alt+V] to select the View menu
          and then press the letter I to select the List command.


DISPLAYING AND HIDING TOOLBARS

    Discussion
          Toolbars provide shortcuts to menu commands. Each Access toolbar is composed of
          buttons and pull-down lists. Each button executes a specific menu command.

          When Access starts, the Database toolbar appears by default below the menu bar. The
          Database toolbar provides buttons for many general Access functions. You can also
          display the Web toolbar, which provides buttons for creating web pages.

          In addition to the default toolbars you can select in Access, task-specific toolbars
          appear when you are working in the various views. For example, when you open a
          Table object type, the Formatting (Datasheet), Table Datasheet, and Web toolbars
          are available.

          You can choose to display one, several, or all the toolbars at any given time, or you
          can hide all the toolbars to create a larger working area.

          Toolbars can be displayed as either docked or floating. A docked toolbar appears at
          one edge of the window, whereas a floating toolbar can be moved to any location on
          the screen. You can also change the size and shape of a floating toolbar. These options
          provide flexibility when you need to display several toolbars at once, or when you
          need to see all the available buttons on a docked toolbar.

          When you point to a button on a toolbar, the name of the function associated with it
          appears. This description is referred to as a ScreenTip. Even if a toolbar button is
          dimmed (because it is not available for the current task), the ScreenTip still appears
          when you point to the button.


Page 8                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                 Lesson 1 - Exploring Access


          The Toolbar Options button at the right end of a toolbar can be used to see any
          buttons that do not fit on the docked toolbar. In addition, you can customize any
          toolbar by adding or removing buttons.



                     You can also display and hide toolbars by right-clicking any
                      toolbar and selecting or deselecting toolbars as desired. A
                      checkmark next to the name of a toolbar (selected) indicates
                      that it is displayed.


                     If you have hidden all the toolbars, you can redisplay one
                      toolbar by selecting the View menu, pointing to the Toolbars
                      command, and selecting the toolbar you want to display, or
                      you can right-click the menu bar and select the desired toolbar.




   Procedures
          1. Select the View menu.
          2. Point to the Toolbars command.
          3. Select the toolbar you want to display or hide.




    Step-by-Step
          Display and hide a toolbar.

           Steps                                            Practice Data
           1. Select the View menu.                         Click View
              The View menu appears.
           2. Point to the Toolbars command.                Point to Toolbars
              The Toolbars submenu appears.
           3. Select the toolbar you want to display        Click Web
              or hide.
              The selected toolbar is displayed or
              hidden accordingly.

          Practice the Concept: Hide the Web toolbar by right-clicking any toolbar and
          selecting the Web command.



Canadian Beaver (CBIT)                                                                    Page 9
Lesson 1 - Exploring Access                                                   Access 2003 - Lvl 1



DISPLAYING HIDDEN TOOLBAR BUTTONS

    Discussion
          When MS Access 2003 starts, depending on your screen resolution, many toolbar
          buttons may not be visible. To view hidden toolbar buttons, a Toolbar Options
          button is provided at the far right of any toolbar. When a Toolbar Options button is
          clicked, a palette containing the hidden toolbar buttons appears.

          By default, the Database toolbars display the most frequently used buttons. As soon
          as you select any hidden toolbar button, that button appears on the toolbar. If there is
          not enough room on the toolbar, one of the less frequently used buttons is hidden.


                   You can add and permanently remove a toolbar button by
                    clicking the Toolbar Options button, pointing to the Add or
                    Remove Buttons command, pointing to the desired toolbar,
                    and selecting or deselecting the button from the list of buttons.
                    The Reset Toolbar command at the bottom of the list returns
                    the toolbar to its default settings.




   Procedures
          1. Click the Toolbar Options button         on any toolbar.
          2. Click the hidden toolbar button you want to use.




    Step-by-Step
          Display a hidden toolbar button.

           Steps                                           Practice Data
           1. Click the Toolbar Options button on          Click the Formatting
              any toolbar.
              A palette containing hidden toolbar          toolbar
              buttons appears.




Page 10                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                               Lesson 1 - Exploring Access



           Steps                                          Practice Data
           2. Click the hidden toolbar button you         Click
              want to use.
              The palette closes, and the selected
              toolbar button appears on the
              associated toolbar.



CHANGING MENU AND TOOLBAR OPTIONS

    Discussion
          You can change menu and toolbar options in order to adjust the behavior of these
          objects. The Options page in the Customize dialog box allows you to select menu and
          toolbar preferences. If you prefer to see full menus, you can disable the option to show
          recently used commands first. You can also reset any data usage changes that have
          occurred as a result of using the menus and toolbars.




                                         The Customize dialog box




                     The Reset my usage data option only affects Access. All
                      other options affect all Office 2003 applications.


Canadian Beaver (CBIT)                                                                   Page 11
Lesson 1 - Exploring Access                                               Access 2003 - Lvl 1




   Procedures
          1. Select the Tools menu.
          2. Select the Customize command.
          3. Select the Options tab.
          4. To restore toolbars and menus to the default, select Reset my usage
             data.
          5. Select Yes.
          6. To change the menu display, select or deselect the Always show full
             menus option.
          7. Change additional options as desired.
          8. Select Close.




    Step-by-Step
          Change menu and toolbar options.

           Steps                                        Practice Data
           1. Select the Tools menu.                    Click Tools
              The Tools menu appears.
           2. Select the Customize command.             Click Customize...
              The Customize dialog box opens.
           3. Select the Options tab.                   Click the Options tab, if
              The Options page appears.                 necessary
           4. To restore toolbars and menus to the      Click Reset my usage
              default, select Reset my usage data.      data
              A Microsoft Access warning box
              opens, prompting you to confirm the
              selected option.
           5. Select Yes.                               Click Yes
              The Microsoft Access warning box
              closes.




Page 12                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                Lesson 1 - Exploring Access



           Steps                                          Practice Data
           6. To change the menu display, select or       Click Always show
              deselect the Always show full menus         full menus
              option.
              The Always show full menus option is
              selected or deselected accordingly.
           7. Select Close.                               Click Close
              The Customize dialog box closes, and
              the menu and toolbar settings are
              saved.

          Display the View menu and notice that all of its commands appear. Then, click in the
          application window to hide the View menu.


USING DATABASE OBJECTS

    Discussion
          An Access database file can contain objects such as tables, queries, forms, reports,
          pages, macros, and modules. Database objects are created to input, edit, retrieve,
          display, and print data. You can include up to seven different object types in an
          Access database. A description of each of these object types is listed in the following
          table:

           Object Type        Object Purpose
           Table              This object type defines the structure of an Access
                              database. Tables display multiple records in rows and
                              columns. Information in these records can be entered,
                              edited, stored, and retrieved.
           Query              A way of requesting selected information from a
                              table. When you run a query, a selected set of records,
                              called the recordset, appears. You can then edit or
                              print the recordset.
           Form               A screen display you can create to show selected
                              fields in a record. Forms allow you to enter, view, and
                              edit data. You can use a form as an alternative to
                              displaying data in rows and columns.
           Report             A design for printed data. Reports can include lists
                              and mailing labels, as well as database reports.
                              Reports can also perform mathematical operations
                              and calculate summaries.



Canadian Beaver (CBIT)                                                                   Page 13
Lesson 1 - Exploring Access                                                  Access 2003 - Lvl 1



           Object Type        Object Purpose
           Page               A data access web page used for viewing and working
                              with Microsoft Access databases on the Internet or an
                              intranet.
           Macro              A stored set of Access commands that can be repeated
                              as a unit to automate database functions.
           Module             This object type stores Visual Basic for Applications
                              Edition programming code that can be used to further
                              customize and enhance database functions.

          When you open a database, all objects in the database are grouped under one of the
          object types listed in the preceding table. These object types, along with their
          corresponding objects, appear in the Database window.



USING THE DATABASE WINDOW

    Discussion
          When you open a database, it appears in a Database window. The Database window
          provides a toolbar below the title bar, the Objects and Groups bar below the toolbar
          to the left and an object list to the right. The Objects bar displays the various object
          types that are available in Access: Tables, Queries, Forms, Reports, Pages, Macros,
          and Modules. The Groups bar displays the default Favorites group; you can add use
          this group to organize related objects in your Access database. You can also create
          and add new groups.

          When an object type is selected, all objects (if any) associated with that object type
          appear in the object list. For example, selecting the Queries object type displays all
          query objects in the database and selecting the Forms object type displays all the form
          objects.

          Shortcuts also appear in the object list for some object types. These shortcuts enable
          you to create new objects, such as tables, queries, forms, and reports, in Design view
          or using a wizard.




Page 14                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                  Lesson 1 - Exploring Access




                                           The Queries object list




                     You can also use the [Ctrl+Tab] key combination to select
                      the desired object type in the Objects bar.


                     The toolbar buttons available in the Database window vary
                      according to the object type selected.




   Procedures
          1. Open the desired database.
          2. Click the desired object type in the Objects bar.




    Step-by-Step
          Use the Database window to select an object type.

          If necessary, open an existing database.




Canadian Beaver (CBIT)                                                                  Page 15
Lesson 1 - Exploring Access                                                     Access 2003 - Lvl 1



           Steps                                            Practice Data
           1. Click the desired object type in the
                                                        Click           Queries
              Objects bar.
              The object type is selected, and the
              objects stored in it appear in the object
              list.

          Practice the Concept: Click the Forms object type and the Reports object type to
          display their object lists. Then, click the Tables object type to redisplay its object list.


OPENING A DATABASE OBJECT

    Discussion
          You can open a database object to view the data stored in it. The view in which the
          data appears depends on the type of object you open. Tables and queries appear in
          Datasheet view. Forms appear in Form view. Reports display the data in print
          preview. Macros and modules run programs attached to the object.




                                   The Customers table in Datasheet view




                   You can also open an object by double-clicking its name in the
                    object list.




Page 16                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                               Lesson 1 - Exploring Access



                     You can use the Close button on the applicable window title
                      bar to close a database object (such as a table or form), a
                      database, or Access itself.




   Procedures
          1. Open the desired database.
          2. Display the desired object list.
          3. Click the name of the object you want to open.

          4. Select the        Open button on the Database window toolbar.




    Step-by-Step
          Open a database object.

          If necessary, display the Tables object list.

           Steps                                          Practice Data
           1. Click the name of the object you want       Click Customers, if
              to open.                                    necessary
              The object is selected.
           2. Select the Open button on the
              Database window toolbar.                    Click      Open
              The object appears in the
              corresponding view, or the
              corresponding program runs.

          Click the Close button on the Customers: Table window title bar to close the
          Customers table.

          Practice the Concept: Display the Forms object list. Select the Orders Entry Form
          object, if necessary, and then select the Open button to display the form in Form
          view. Close the Orders Entry Form window.

          Display the Reports object list and use the Preview button in the Database window to
          display the Orders Report object in print preview. Close the Orders Report window.




Canadian Beaver (CBIT)                                                                   Page 17
Lesson 1 - Exploring Access                                                  Access 2003 - Lvl 1


          Close the Wsgoods database by clicking the Close button on the Database window
          title bar.


USING THE TASK PANE

    Discussion
          The task pane provides links to many common tasks in Access. For example, you can
          use the New File task pane to create a new, blank presentation or to open a recently
          modified one.

          The task pane is actually comprised of several panes, which can be viewed using the
          Other Task Panes list. The name of the current task pane appears on the task pane
          title bar. You can use the other task panes to perform other tasks in Access, such as
          performing searches and working with the Office Clipboard.

          The Back and Forward buttons located on the task pane title bar navigate to
          previously viewed task panes. Blue text in the task pane indicates a link to an action or
          to a dialog box.

          You can hide or display the task pane according to your needs. When you open a
          database, the task pane automatically closes. You can display or hide the task pane at
          any time while working with database objects. Although the features in the task pane
          are useful, you may want to hide it to display a larger working area. By default, the
          task pane appears each time you start Access. You can disable this feature by
          deselecting the Show at startup option at the bottom of the New File task pane.




                                          The New File task pane




Page 18                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                 Lesson 1 - Exploring Access




                     The contents of the task pane vary, depending upon the
                      currently selected text or object.


                     Each task pane can be opened with a separate menu command.
                      The Task Pane command on the View menu opens the most
                      recently used task pane.


                     You can change the size of the task pane by dragging the
                      splitter bar (the left border of the task pane) as needed.




   Procedures
          1. Select the View menu.
          2. Point to the Toolbars command.
          3. Select the Task Pane command.
          4. To view a different task pane, select the Other Task Panes list on
             the task pane title bar.
          5. Select the desired task pane.
          6. Click the Back or Forward button in the upper left corner of the task
             pane to return to a previous task pane.
          7. Click the desired task pane link.
          8. To close the task pane, click the Close button in the upper right
             corner of the task pane.




    Step-by-Step
          Use the task pane.

          If necessary, close the task pane.

           Steps                                            Practice Data
           1. Select the View menu.                         Click View
              The View menu appears.
           2. Point to the Toolbars command.                Point to Toolbars
              The Toolbars submenu appears.


Canadian Beaver (CBIT)                                                                 Page 19
Lesson 1 - Exploring Access                                                    Access 2003 - Lvl 1



           Steps                                           Practice Data
           3. Select the Task Pane command.                Click Task Pane
              The task pane opens with the most
              recently viewed task pane displayed.
           4. To view a different task pane, select        Click     on the task pane
              the Other Task Panes list on the task        title bar
              pane title bar.
              A list of available task panes appears.
           5. Select the desired task pane.                Click Search
              The selected task pane appears.
           6. Click the Back or Forward button in          Click    in the upper left
              the upper left corner of the task pane       corner of the task pane
              to return to a previous task pane.
              The previously viewed task pane or the
              next task pane in the list appears.
           7. Click the desired task pane link.            Follow the instructions
              The corresponding action occurs.             shown below the table
                                                           before continuing on to
                                                           the next step
           8. To close the task pane, click the Close      Click    in the upper right
              button in the upper right corner of the      corner of the task pane
              task pane.
              The task pane closes.

          In the New File task pane, click the Wsgoods link under Open a file.

          Return to the table and continue on to the next step (step 8).


EXITING ACCESS

    Discussion
          In order to be efficient, you should close any objects you are not using. You can have
          several objects open at once, but multiple objects use more system resources. Changes
          to databases are saved automatically when you close the Database window.

          In addition, when you have finished using Access, you should exit the application
          properly, since Access performs necessary housekeeping before it closes. If a database
          object has been modified, but not saved, an Access dialog box or the Office Assistant
          prompts you to save the changes before exiting.




Page 20                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                Lesson 1 - Exploring Access



                     You can also close an object or database by clicking the Close
                      button on the applicable window title bar.




   Procedures
          1. Select the File menu.
          2. Select the Exit command.




    Step-by-Step
          Exit Access.

           Steps                                           Practice Data
           1. Select the File menu.                        Click File
              The File menu appears.
           2. Select the Exit command.                     Click Exit
              Access closes.




Canadian Beaver (CBIT)                                                                 Page 21
Lesson 1 - Exploring Access                                               Access 2003 - Lvl 1




EXERCISE
EXPLORING ACCESS

      Task
          Explore Access.

          1. Start Access, if necessary.
          2. Open Train01x.
          3. Select the Queries, Forms, and Reports object types to view the
             objects in each; then, reselect the Tables object type.
          4. Open the Client table.
          5. Display the Formatting (Datasheet) toolbar.
          6. Hide the Formatting (Datasheet) toolbar.




          7. Close the Client table.
          8. Change the menu and toolbar options to display short menus and
             large toolbar icons. (Hint: Try using the Customize dialog box.)
          9. Reset your usage data, show full menus only, and return the toolbar
             buttons to their normal size.



Page 22                                                              Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                               Lesson 1 - Exploring Access


          10. Display the task pane. View the Clipboard task pane. Use the Back
              button to return to the New File task pane. Then, close the task pane.
          11. Close the database.




Canadian Beaver (CBIT)                                                                 Page 23
LESSON 2 -
USING ONLINE HELP

In this lesson, you will learn how to:
         Use Access Help

         Work with Help Task Pane

         Use Ask a Question

         Modify Properties of Online Content

         Use Microsoft Office Online Help

         Display and Hide the Office Assistant

         Use the Help Answer Wizard

         Change Office Assistant Options
Access 2003 - Lvl 1                                              Lesson 13 – Using Online Help




USE ACCESS HELP

    Discussion
          If you need assistance on any Access topic or task, you can use Access’s extensive
          Help facility. There are several ways in which you can get help: the Office Assistant,
          Ask a Question, and the Microsoft Access Help dialog box.

          Access provides assistance through the Microsoft Access Help command, which
          launches the Office Assistant, if it is enabled. If the Office Assistant is disabled, you
          can directly access the Help window, which includes the Contents, Index, and Answer
          Wizard components. These components allow you to scroll through a table of
          contents, search for a specific word or phrase based on a keyword, or search based on
          a question you type, respectively.

          If you have access to the Internet, you can use the Office on the Web command to
          connect to the Microsoft Office web site, from where you can download free
          programs, access on-line support, and get the latest Microsoft product information.

          The Activate Product command provides an on-line method of purchasing and
          downloading Microsoft software and updates.The Detect and Repair command
          reviews the previous install process and finds and fixes problems that may have
          developed during or since the initial software installation. However, this feature
          cannot repair corrupted data files.

          You can use the About Microsoft Access command to view copyright and licensing
          information about the program. The About Microsoft Access window contains a
          System Info button, which displays information about your computer, and a Disabled
          Items button, which displays any items that prevent Access from functioning
          properly. A Tech Support button provides help on getting product support.




Canadian Beaver (CBIT)                                                                    Page 25
Lesson 13 – Using Online Help                                                Access 2003 - Lvl 1




                                          The Access Help menu




WORK WITH HELP TASK PANE

    Discussion
          The Help Task Pane appears when you select Microsoft Office Access Help under
          the Help menu or when you click on the        button on the Standard Tool Bar.

          This Task Pane is divided into 3 sections : Assistance, Microsoft Office Online and
          See also.

          Under Assistance, a text box Search for is displayed where you can type a question
          as well as a link to the Table of Contents which displays the content of help, in
          outline format. Several links appear in the section Microsoft Office Online
          permitting you to access quickly different Microsoft help sites whether it is to obtain
          help on the latest updates or to download fixes or templates. Several additional links
          appear under See also which facilitate modifications to the content of the online help.




                  Access to the Internet is required to log on to Microsoft Online
                   Help




Page 26                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                             Lesson 13 – Using Online Help




    rocedures
    P


           1. Click on the button    on the Standard Tool Bar or on
              Microsoft Office Access Help under the Help menu.

           2. Click on the desired link in the Access Help Task Pane or type a
              question in the Search text box in the Assistance zone.


           3. Continue to click on links until the required help is displayed.


           4. Close the Help Window after reading the content.




    Step-by-Step
          Working with Microsoft Office Access Help

           Steps                                          Practice Data

           1. Click on the button     on the              Click on
              Standard Tool Bar or on Microsoft
              Office Access Help under the Help
              menu.
              Displays the Help Task Pane
           2. Click on the desired link in the Access     Click on Table of
              Help Task Pane or type a question in        Contents under
              the Search text box in the Assistance       Assistance
              zone. Click on Table of Contents
              The Table of Content is displayed.
           3. Click on the desired link in the Table      Click on Startup and
              of Contents.                                Settings
              Launches the topic and displays the
              sub-topics.
           4. Continue to click on links until the        Click on Getting Help
              required help is displayed.
              Launches the desired topic and
              displays the sub-topics..



Canadian Beaver (CBIT)                                                               Page 27
Lesson 13 – Using Online Help                                                 Access 2003 - Lvl 1



           Steps                                          Practice Data
           5. Continue to click on links until the        Click on the desired Icon
              required help is displayed.
              Launches the desired topic and
              displays the appropriate windows.
           6. After reading the content of the topic,     Click on     of the help
              Close the Help Window. After reading        Window
              the content, close the window.
              Closes the help window..



USE ASK A QUESTION

    Discussion
          Ask a question is located at the far right of the Standard Tool Bar under the Access
          Buttons. It is always a very quick way to get assistance on a topic. For best results, it
          is preferable to enter a complete question or sentence rather than a unique word or
          group of words. Once a question has been typed, click on [Enter], and Access will
          display possible help topics. By clicking on the topic, a Microsoft Office Access help
          window is displayed. From this document, other relevant help topics can be accessed.

          When first launching a document the Ask a question text box displays the text Type a
          question for help. The question or sentence replaces the default text. Previous
          questions are displayed in the text box, permitting a quick reference to previously
          asked questions.



                   Only the questions asked during a Access session will appear
                    in the Ask a Question text box. When closing Access, the list
                    will be deleted.




   Procedure
          1. Click in Ask a Question text box located to the far right of the
             Standard Tool bar.
          2. Type your question.
          3. Press on [Enter].
          4. Select the help topic required
          5. Click on the Close button of the help window.

Page 28                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                Lesson 13 – Using Online Help




    Step-by-Step
          Getting help by asking a question in the Ask as Question text box feature on the upper
          most corner of the Access Window.

           Steps                                             Practice Data
           1. Click in the Ask a question text box     Click in the Ask a
              located to the far right of the Standard question text box.
              tool bar.
              Displays the insertion point in the Ask
              a question text box or selects the text.
           2. Type the question you want to ask.       Type How do I save a
              Displays the text in Ask a question text document?
              box.
           3. Press [Enter].                                 Presss [Enter]
              Displays the results of the search in
              the Task Pane Search Results..
           4. Select the appropriate topic.                  Scroll displayed topics
              Opens the Help Window displaying the           and click on Save a
              answers to the question.                       document
           5. Click on Close on the title bar of the         Click the  button of the
              Help window.                                   help window.
              Closes the Help Window.



MODIFY PROPERTIES OF ONLINE CONTENT

    Discussion
          One can modify the properties of the content of online help if one does not want to use
          the online help on Microsoft Office Online or if one wants to change its behaviour.



                     It is preferable to have access to high speed Internet to access
                      Microsoft Office Online.




Canadian Beaver (CBIT)                                                                   Page 29
Lesson 13 – Using Online Help                                              Access 2003 - Lvl 1




   Procedure
          1. Select       .
          2. Click on the link Online Content Settings
          3. Select Online Content, if necessary.
          5. In the right-hand side of the dialogue box, select the option Show
             content and links from Microsoft Office Online, if necessary.
          6. Click on OK.




    Step-by-Step
          Modify the properties of Microsoft Office Online help.

           Steps                                         Practice Data

           1. Click on    .                              Click on
              The Access Help Task Pane is
              displayed.
           2. Click on Online Content Settings           Click on Online Content
              under See also.                            Settings
              Displays the Service Options dialogue
              box..
           3. Select Online Content, if necessary.       Cliquez sur Contenu en
              Selects the category and displays the      ligne
              corresponding available properties on
              the right hand side of the dialogue
              box.
           4. On the right-hand side of the Service      Click on    Show content
              Options dialogue box, select Show          and links from Microsoft
              content and links from Microsoft           Office Online, if
              Office Online, if necessary.               necessary.
              Selects the option
           5. Select or deselect other options           Click on      Search
              responding to your preferences             online content when
              Selects or deselects options               connected to select the
                                                         option, if required.




Page 30                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                              Lesson 13 – Using Online Help



           Steps                                          Practice Data
           6. Click on OK.                                Click on OK.
              Closes the Service Options dialogue
              box et applies the selected options.



USE MICROSOFT OFFICE ONLINE HELP

    Discussion
          If the computer has access to the Internet, one can use Microsoft Office Online for:

                  ·   assistance
                  ·   templates
                  ·   training
                  ·   downloads

          If one does not have access to the Internet, the help topics located on the hard drive on
          local are displayed in the Microsoft Office Access Help task pane but the additional
          online features are not available. The content is displayed by order of relevance.




                                     The Microsoft Office Online Site




Canadian Beaver (CBIT)                                                                    Page 31
Lesson 13 – Using Online Help                                              Access 2003 - Lvl 1




   Procedure
          1. Select View from the menu.
          2. Select Task Pane.
          3. Select Other Task Panes.
          4. Select Help.
          5. Select Connect to Microsoft Office Online.




    Step-by-Step
          Using Microsoft Office Online.

          The computer must be connected to the Internet.

           Steps                                         Practice Data
           1. Select View.                               Click on View
              The View menu is displayed.
           2. Select Task Pane.                          Click on Task Pane
              The Task Pane is displayed.
           3. Select Connect to Microsoft Office         Click on Connect to
              Online.                                    Microsoft Office Online.
              Displays the Microsoft Office Online
              site in a search engine window.

          Close the Search Engine Window and the Office Task Pane.


DISPLAY AND HIDE THE OFFICE ASSISTANT

    Discussion
          To assist you in quickly and efficiently getting comprehensive help on any Access
          feature, you can use the Office Assistant. The Office Assistant appears in the program
          window as an animated graphic. When you click the Office Assistant, a balloon
          appears, allowing you to enter questions or phrases for which you need help. In
          addition, the Office Assistant automatically provides tips and help on tasks as you
          work.



Page 32                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                Lesson 13 – Using Online Help


          Although the Office Assistant moves if it is in the way, you can hide the Office
          Assistant if you are not using it, find it distracting, or require a larger working area.



                     You can also hide the Office Assistant by right-clicking it and
                      selecting the Hide command. You can permanently disable the
                      Office Assistant by deselecting the Use the Office Assistant
                      option on the Options page in the Office Assistant dialog box.




   Procedures
          1. Select the Help menu.
          2. Select the Show the Office Assistant or Hide the Office Assistant
             command, as appropriate.




    Step-by-Step
          Hide and display the Office Assistant.

           Steps                                            Practice Data
           1. Select the Help menu.                         Click Help
              The Help menu appears.
           2. Select the Show the Office Assistant          Click Show the Office
              or Hide the Office Assistant                  Assistant
              command, as appropriate.
              The Office Assistant appears or closes
              accordingly.

          Practice the Concept: Hide the Office Assistant by right-clicking it and selecting the
          Hide command. If the Office Assistant displays a balloon asking if you want to
          permanently turn it off or just hide it again, select No, just hide me. Use the Help
          menu to redisplay the Office Assistant.




Canadian Beaver (CBIT)                                                                       Page 33
Lesson 13 – Using Online Help                                                 Access 2003 - Lvl 1



FINDING AN ANSWER

    Discussion
          You can query the Office Assistant directly with any questions you may have
          regarding Access. When you query the Office Assistant, help topics related to your
          question appear. You can select any available help topic to view additional
          information. If there are more topics than can be displayed at one time, you can use
          the See more and See previous commands to scroll through the available topics.

          The Office Assistant provides the best answers when you enter a complete sentence or
          question in a query, rather than just a word or phrase. For example, it is better to enter
          How do I print a document? rather than just printing. The last query remains in the
          Office Assistant until you create a new query or exit Access.



                   You do not have to open the Office Assistant to ask a
                    question. You can type your question into the Ask a Question
                    box on the menu bar.




   Procedures
          1. Open the Office Assistant.
          2. Click the Office Assistant to display the balloon.
          3. Type the desired question or sentence.
          4. Select Search.
          5. Select the desired help topic.
          6. Click the Close button on the help window title bar.




    Step-by-Step
          Find an answer to a question using the Office Assistant.

          If necessary, open the Office Assistant.




Page 34                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 13 – Using Online Help



           Steps                                         Practice Data
           1. Click the Office Assistant to display      Click the Office Assistant
              the balloon.
              The Office Assistant balloon appears.
           2. Type the desired question or sentence.     Type How do I print a
              The query appears in the box.              document?
           3. Select Search.                             Click Search
              A list of pertinent help topics appears.
           4. Select the desired help topic.             Click Print a document
              The corresponding help window
              opens.
           5. Click the Close button on the help         Click    on the help
              window title bar.                          window title bar
              The help window closes.

          Close the Office Help Task Pane.


CHANGING OFFICE ASSISTANT OPTIONS

    Discussion
          You can use the options available on the Options page in the Office Assistant dialog
          box to make the Office Assistant work best for you. Some of these options are
          activated by default.

          The Office Assistant can be permanently enabled or disabled by the Use the Office
          Assistant option on the Options page in the Office Assistant dialog box. Under this
          heading, other options that can be selected include: Respond to F1 key, which opens
          the Office Assistant whenever you press the [F1] key; Move when in the way, which
          moves the Office Assistant as needed when screen elements (such as a dialog box) are
          open; and Guess Help topics, in which the Office Assistant automatically displays
          help on the current task.

          Under Show tips about, you can choose exactly which type of tip you want to display
          or hide, depending upon your individual needs. For example, the Show the Tip of the
          Day at startup option opens an Office Assistant tip each time Access starts.

          You can also reset your tips. This option allows you to review previous tips from the
          current Access session. This option does not affect other Office 2003 programs.

          Other Office 2003 programs (such as PowerPoint and Excel) share the Office
          Assistant. Therefore, most changes you make to the Office Assistant in Access affect
          all other Office 2003 programs.


Canadian Beaver (CBIT)                                                                  Page 35
Lesson 13 – Using Online Help                                                  Access 2003 - Lvl 1


          The Gallery page in the Office Assistant dialog box allows you to preview and select
          other Office Assistants. Additional Office Assistants can be downloaded from
          Microsoft’s web page on the World Wide Web.




                                   The Office Assistant Dialogue Box




                  You can right-click the Office Assistant to display the Office
                   Assistant dialog box. The Options command displays the
                   Options page and the Choose Assistant command displays
                   the Gallery page.


                  If you have Internet access to the World Wide Web, you can
                   select additional Office Assistants from Microsoft’s web page.
                   These Office Assistants are found by selecting the Help menu
                   and then the Office on the Web command.


                  If you disable the Office Assistant, you can enable it by
                   selecting the Help menu and then the Show the Office
                   Assistant command.




Page 36                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 13 – Using Online Help




   Procedures
          1. Display the Office Assistant.
          2. Click the Office Assistant.
          3. Select Options.
          4. Select or deselect the desired options.
          5. Select OK.




    Step-by-Step
          Change Office Assistant options.

          If necessary, display the Office Assistant.

           Steps                                        Practice Data
           1. Click the Office Assistant.               Click the Office Assistant
              The Office Assistant balloon appears.
           2. Select Options.                           Click Options
              The Office Assistant dialog box opens
              with the Options page displayed.
           3. Select or deselect the desired options.   Click    Use the Office
              The options are selected or deselected    Assistant to deselect it
              accordingly.
           4. Select OK.                                Click OK
              The Office Assistant dialog box closes,
              and the options are enabled or
              disabled accordingly.




Canadian Beaver (CBIT)                                                               Page 37
Lesson 13 – Using Online Help                                             Access 2003 - Lvl 1




EXERCISE
USING ONLINE HELP AND OFFICE ASSISTANT

      Task
          Using Help Features.

          1. Display the Office Assistant
          2. Ask the Assistant « How to create a template? ».
          3. Display the document Modify a template.
          4. Review and close the help window..
          5. Use the Help Menu to Hide the Office Assistant.
          6. Close the Search Results Task Pane.

          7. Use        to display Microsoft Office Access Help.
          8. Use Search for: under Assistance to learn “how to select a different
             Office Assistant?”
          9. Select the appropriate help document and close the help window.
          10. Return to the Task Pane Microsoft Office Access Help..
          11. Click on the link Online Content Settings to activate Microsoft
              Office Online.
          12. Close the Task Pane.




Page 38                                                              Canadian Beaver (CBIT)
LESSON 3 -
WORKING WITH TABLES

In this lesson, you will learn how to:
         Use Datasheet view

         Navigate fields in tables

         Add records

         Move through records

         Select records

         Edit records

         Save records

         Delete records
Access 2003 - Lvl 1                                             Lesson 3 - Working with Tables




USING DATASHEET VIEW

    Discussion
          When you open a table, it appears in Datasheet view by default. Datasheet view is
          useful when you want to enter, edit, or delete records.

          In Datasheet view, the table data is arranged in rows and columns, with the columns
          representing the table fields and the rows the individual records. Multiple records
          appear in the table. The small box at the far left of each row is the record selector; a
          black triangle in the record selector indicates the current record.



NAVIGATING FIELDS IN TABLES

    Discussion
          In Datasheet view, you can use the mouse or the keyboard to move through the fields
          and records. If there are too many fields to display on screen at the same time, the
          window scrolls automatically as you move to the right or left.

          As you enter or edit data, you can use the [Tab] or [Enter] keys to move from field to
          field. You can also use the [Right] and [Left] arrow keys to navigate across fields.
          These keys are helpful because they allow you to keep your hands on the keyboard at
          all times, rather than having to switch back and forth from the keyboard to the mouse.
          However, the mouse is useful if you want to skip certain fields. You simply click in
          the field where you want to enter or edit data.

          The [Up] and [Down] arrow keys allow you to move from record to record. In
          addition, the navigation toolbar at the bottom of the datasheet allows you to select the
          first, last, next, or previous record, as well as specify a particular record you want to
          view.



                     The [Page Up] and [Page Down] keys scroll to the previous
                      or next page of records, respectively.




Canadian Beaver (CBIT)                                                                     Page 41
Lesson 3 - Working with Tables                                                Access 2003 - Lvl 1



ADDING RECORDS

    Discussion
          A blank row follows the last record in an existing table. This blank row, the new
          record row, displays an asterisk (*) in the record selector. As soon as you begin
          entering data into the new record row, the asterisk in the record selector changes to a
          pencil to indicate that the record is being entered or edited, and Access creates another
          new record row below it.

          If there is no data in the table, only the new record row appears. It does not display an
          asterisk, however; rather, it displays the black triangle that indicates the current
          record.

          When you enter data into a row, the amount of characters you can enter is limited by
          the size of the field. The field size, however, can actually be larger than the column
          width in Datasheet view; if the field size is larger than the column width, the text
          scrolls as you type.

          After you have typed the desired information into a field, you can press the [Enter] or
          [Tab] key to save the record and move the insertion point to the next field.




                                     Adding records in Datasheet view




Page 42                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                              Lesson 3 - Working with Tables



                     You can use the Clipboard task pane to enter repeated values.
                      You can copy as many as 24 separate entries to the Clipboard
                      task pane. To display the Clipboard task pane, select the Edit
                      menu and then the Office Clipboard command. Copying two
                      items in succession also displays the Clipboard task pane. For
                      each copied item, the Clipboard task pane displays an icon
                      and a portion of the copied or cut text. Clicking an item in the
                      Clipboard task pane inserts it into the current field.


                     You can press the [Ctrl+‟] key combination to insert the data
                      from the corresponding field of the previous record into the
                      current field.


                     You can also use the New Record button on the Table
                      Datasheet toolbar or at the bottom of the Datasheet view
                      window to add a new record.




   Procedures
          1. Open the desired table in Datasheet view.
          2. Select the first field in the new record row, if necessary.
          3. Type the desired data.
          4. Press [Enter].
          5. Enter the desired record data into the remaining fields, pressing
             [Enter] after each entry.




    Step-by-Step
          From the Student Data directory, open WORLD04.MDB.
          Add a record to a table.

          Open the Items table in Datasheet view.

           Steps                                            Practice Data
           1. Select the first field in the new record      Click in the Product ID
              row.                                          field of the first row, if
              The insertion point appears in the            necessary
              field.



Canadian Beaver (CBIT)                                                                   Page 43
Lesson 3 - Working with Tables                                                 Access 2003 - Lvl 1



           Steps                                            Practice Data
           2. Type the desired data.                        Type 10-1437
              The data appears in the field.
           3. Press [Enter].                                Press [Enter]
              The insertion point moves to the next
              field.
           4. Enter the desired record data into the        Follow the instructions
              remaining fields, pressing [Enter]            shown below the table to
              after each entry.                             complete this step
              The data appears in the fields.

          Add the following records to the table, pressing [Enter] after each field:

                       ProductID      Product Description       Unit Price
                       10-1437        shoes, soccer             65.75
                       10-2562        skates, hockey            98.50
                       10-3827        shoes, baseball           97.81

          Close the Datasheet view window.


MOVING THROUGH RECORDS

    Discussion
          If there are more records in a table than can be displayed in Datasheet view, a vertical
          scroll bar appears. You can use this scroll bar to view additional records.

          Access also provides a navigation toolbar at the bottom of the datasheet that can be
          used to select the first, last, next, or previous record in the datasheet, as well as to
          specify a particular record you want to view.



                   You can also use the [Up] and [Down] arrow keys to move
                    from record to record and the [Page Up] and [Page Down]
                    keys to display the previous or next page of records,
                    respectively.




Page 44                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 3 - Working with Tables




   Procedures
          1. Open a table in Datasheet view.
          2. To move to the last record in the table, click the Last Record button
                      on the navigation toolbar.

          3. To move to the first record, click the First Record button      on
             the navigation toolbar.

          4. To move to the next record, click the Next Record button        on
             the navigation toolbar.
          5. To move to the previous record, click the Previous Record button
                      on the navigation toolbar.




    Step-by-Step
          Move through records in a table.

          Open the Items 2 table in Datasheet view.

           Steps                                         Practice Data
           1. To move to the last record in the table,   Click
              click the Last Record button on the
              navigation toolbar.
              The first field of the last record is
              selected.
           2. To move to the first record, click the     Click
              First Record button on the navigation
              toolbar.
              The first field of the first record is
              selected.
           3. To move to the next record, click the      Click
              Next Record button on the navigation
              toolbar.
              The first field of the next record is
              selected.




Canadian Beaver (CBIT)                                                               Page 45
Lesson 3 - Working with Tables                                                   Access 2003 - Lvl 1



           Steps                                             Practice Data
           4. To move to the previous record, click          Click
              the Previous Record button on the
              navigation toolbar.
              The first field of the previous record is
              selected.



SELECTING RECORDS

    Discussion
          When you select any field in a record, that record becomes the active record. A black
          triangle appears in the record selector to the left of the record to indicate that it is the
          active record. You can then edit any field in the record as desired.

          Selecting a record is different than making a record active. To select a record, you
          must select the entire row. Selecting a record is useful when you want to delete or
          copy it. For example, if you want to add a record containing almost identical
          information as the current record, you can select the current record, copy it, paste it
          into a new record, and then edit it as needed.



                   You can also use the [Up] and [Down] arrow keys to activate
                    the next or previous record, respectively, and the [Page Up]
                    and [Page Down] keys to activate the first record on the
                    corresponding datasheet page.




   Procedures
          1. Open a table in Datasheet view.
          2. Click anywhere in the record you want to make active.
          3. Point to the record selector of a record you want to select.
          4. Click in the record selector of the record you want to select.




Page 46                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                 Lesson 3 - Working with Tables




    Step-by-Step
          Select a record in a table.

          If necessary, open the Items 2 table in Datasheet view.

           Steps                                              Practice Data
           1. Click anywhere in the record you want           Click in the 10-8137 field
              to make active.
              The field is selected and the record
              selector displays a black triangle.
           2. Point to the record selector of a record        Point to the left of the
              you want to select.                             11-8771 field
              A solid, black, right-pointing arrow
              appears.
           3. Click in the record selector of the             Click to the left of the
              record you want to select.                      11-8771 field
              The entire record is selected.



EDITING RECORDS

    Discussion
          You can edit records at any time. You cannot edit an AutoNumber field, however,
          because Access automatically generates the data in it.

          When you use the keyboard to access a field, the entire field is selected, and you can
          replace all data simply by typing new data. This is select mode. Access also allows
          you to edit individual characters within a field. To do this, you must switch to edit
          mode. In edit mode, the insertion point appears in the field. You can click in a field to
          access edit mode.

          While you are editing a record, a pencil appears in its record selector. The following
          table describes various keystrokes you can use to edit records:

           Keystroke                    Description
           [Up]                         Selects the same field in the previous record.
           [Down]                       Selects the same field in the next record.
           [Right]                      In select mode, selects the next field; in edit
                                        mode, moves one character to the right.


Canadian Beaver (CBIT)                                                                     Page 47
Lesson 3 - Working with Tables                                                   Access 2003 - Lvl 1



           Keystroke                  Description
           [Left]                     In select mode, selects the previous field; in
                                      edit mode, moves one character to the left.
           [F2]                       Toggles between select and edit modes.
           [Ctrl+Enter]               Inserts a new line character in a field.
           [Ctrl+‟]                   Inserts the data from the corresponding field in
                                      the previous record.
           [Ctrl+Alt+Spacebar]        Replaces the data with the default data, if
                                      available.
           [Ctrl+Shift+:]             In select mode, replaces the field data with the
                                      current time.
           [Ctrl+;]                   In select mode, replaces the field data with the
                                      current date.




   Procedures
          1. Open a table in Datasheet view.
          2. Click in the field you want to edit.
          3. Select the text you want to edit.
          4. Type the desired text.
          5. Press [Enter].




    Step-by-Step
          Edit a table record in edit mode.

          If necessary, open the Items 2 table in Datasheet view.

           Steps                                            Practice Data
           1. Click in the field you want to edit.          Click in the 65.75 field in
              The insertion point appears in the            the first row
              field.




Page 48                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 3 - Working with Tables



           Steps                                          Practice Data
           2. Select the text you want to edit.           Drag the number 6, the
              The characters are selected.                first character in the field
           3. Type the desired text.                      Type 5
              The characters are replaced.
           4. Press [Enter].                              Press [Enter]
              The changes to the record are saved.



SAVING RECORDS

    Discussion
          Access automatically saves a new record or changes made to an existing record as
          soon as you move to the next field. You can also save a record manually. This option
          is useful if a record has numerous fields and you want to save the changes made to a
          field without leaving the field. You may also want to save a record manually to save
          changes made to automatically generated data.



                     Access also saves records automatically when you close a
                      table.


                     You can also press the [Shift+Enter] key combination to save
                      a record.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the Records menu.
          3. Select the Save Record command.




    Step-by-Step
          Save a record in a table.



Canadian Beaver (CBIT)                                                                   Page 49
Lesson 3 - Working with Tables                                             Access 2003 - Lvl 1


          If necessary, open the Items 2 table in Datasheet view.

           Steps                                         Practice Data
           1. Select the Records menu.                   Click Records
              The Records menu appears.
           2. Select the Save Record command.            Click Save Record
              The record is saved.



DELETING RECORDS

    Discussion
          When you no longer need a record, you can delete it. Deleting records saves disk
          space and keeps your tables smaller and more manageable.




                                            Deleting a record




                  After you have deleted a record, you cannot undo the deletion.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the record you want to delete.


          3. Click the Delete Record button          on the Table Datasheet
             toolbar.
          4. Select Yes to delete the record.




Page 50                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 3 - Working with Tables




    Step-by-Step
          Delete a record from a table.

          If necessary, open the Items 2 table in Datasheet view.

           Steps                                        Practice Data
           1. Select the record you want to delete.     Scroll as necessary and
              The record is selected.                   click the record selector of
                                                        the last record
           2. Click the Delete Record button on the
              Table Datasheet toolbar.                  Click
              The record is removed from the table,
              and a Microsoft Access warning box
              opens, prompting you to confirm the
              deletion.
           3. Select Yes to delete the record.          Click Yes
              The Microsoft Access warning box
              closes.

          Close the Items 2 table.
          Close WORLD04.MDB.




Canadian Beaver (CBIT)                                                                 Page 51
Lesson 3 - Working with Tables                                                Access 2003 - Lvl 1




EXERCISE
WORKING WITH TABLES

      Task
          Work with tables.

          1. Open Train04x.
          2. Open the Project table in Datasheet view.
          3. Add the following records to the table:


             Project     Client ID        Course       Start     End       Trainer    Cost
               ID                         Name         Date      Date      Initials
              1001       ABBOTT          PCBASICS      1/5/01   1/5/01       JA       75
              1002        AQUA          WINDOWS        1/9/01   1/9/01       EA       75
              1003      CONCORD           WORD       1/16/01    1/17/01      DF       200

          4. Close the Project table.
          5. Open the Project2 table in Datasheet view.
          6. Move to the second record.
          7. In the second record, change the start date to 1/10/01 and the end
             date to 1/11/01.
          8. Save the record.
          9. Select the fourth record.
          10. Delete the fourth record.
          11. Close the table and then close the database.




Page 52                                                                   Canadian Beaver (CBIT)
LESSON 4 -
EDITING TABLES

In this lesson, you will learn how to:
         Change the row height

         Change the column width

         Change a font attribute

         Change a cell effect

         Select a column

         Move a column

         Hide a column

         Unhide a column

         Freeze a column
Lesson 4 - Editing Tables                                                   Access 2003 - Lvl 1




CHANGING THE ROW HEIGHT

    Discussion
          In Database view, the default row height is 12.75 points. You can change the row
          height to allow long field entries to wrap. This option allows you to display more of
          the data in a long field entry.

          Changing the height of a single row changes the height of every row in the table.



                  You can also select the Format menu and the Row Height
                   command to change the row height in a table. In the Row
                   Height dialog box, you can enter the desired row height or
                   select the Standard Height option to reset the row height to
                   the default 12.75 points.


                  You cannot use the Undo feature to undo a change in the row
                   height. To undo a row height change, you must close the table
                   without saving the changes.




   Procedures
          1. Open a table in Datasheet view.
          2. Point to any horizontal line between records in the record selector.
          3. Drag the line to the desired row height.




    Step-by-Step
          From the Student Data directory, open WORLD05.MDB.
          Change the height of the rows in a table.

          Open the Items table in Datasheet view.




Page 54                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                     Lesson 4 - Editing Tables



           Steps                                            Practice Data
           1. Point to any horizontal line between          Point to the horizontal line
              records in the record selector.               below the shoes, soccer
              The mouse pointer changes into a              record selector
              black cross with vertical arrowheads.
           2. Drag the line to the desired row height.      Drag the line down to the
              The height of all the rows changes            top of the next row
              accordingly.

          Notice that the longer text entries in the Product Description field now wrap to the
          next line.

          Practice the Concept: Select the Format menu and the Row Height command.
          Select the Standard Height option and OK.


CHANGING THE COLUMN WIDTH

    Discussion
          In Database view, the standard column width is 15.6667 characters. If a field contains
          a long entry, you can change the column width to display more of the field entry.



                     You can select the Format menu and the Column Width
                      command to change the column width in a table. In the
                      Column Width dialog box, you can enter the desired column
                      width or select the Standard Width option to reset the
                      column width to its default 15.6667 characters.


                     You cannot use the Undo feature to undo changes in column
                      width. To undo a column width change, you must close the
                      table without saving the changes.


                     If you double-click the column separator line to the right of
                      any column header, the width of that column automatically
                      adjusts to display the longest entry in the column.




Canadian Beaver (CBIT)                                                                     Page 55
Lesson 4 - Editing Tables                                                      Access 2003 - Lvl 1




   Procedures
          1. Open a table in Datasheet view.
          2. Point to the vertical line to the right of the header of the column you
             want to adjust.
          3. Drag the line to the desired width.




    Step-by-Step
          Change the width of a column in a table.

          If necessary, open the Items table in Datasheet view.

           Steps                                           Practice Data
           1. Point to the vertical line to the right of   Point to the vertical line to
              the header of the column you want to         the right of the Product
              adjust.                                      Description header
              The mouse pointer changes into a
              black cross with horizontal
              arrowheads.
           2. Drag the line to the desired width.          Drag the line to the right
              The column width changes                     of the Unit Price header
              accordingly.



CHANGING A FONT ATTRIBUTE

    Discussion
          You can change datasheet font attributes. The font attributes affect the appearance of
          the data and column headings. You can change the font type, style, and size, as well as
          add or remove font effects.




Page 56                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                 Lesson 4 - Editing Tables




                                           The Font dialog box




   Procedures
          1. Open a table in Datasheet view.
          2. Select the Format menu.
          3. Select the Font command.
          4. Select the desired options.
          5. Select OK.




    Step-by-Step
          Change a font attribute.

          If necessary, open the Items table in Datasheet view.

           Steps                                        Practice Data
           1. Select the Format menu.                   Click Format
              The Format menu appears.
           2. Select the Font command.                  Click Font...
              The Font dialog box opens.
           3. Select the desired options.               Click 11 in the Size list
              The changes are previewed in the
              Sample box.




Canadian Beaver (CBIT)                                                               Page 57
Lesson 4 - Editing Tables                                                 Access 2003 - Lvl 1



           Steps                                          Practice Data
           4. Select OK.                                  Click OK
              The Font dialog box closes, and the
              font attributes are applied to the table.

          Notice that the row height automatically adjusts to accommodate the font change.


CHANGING A CELL EFFECT

    Discussion
          You can change the appearance of cells in Datasheet view. For example, you can
          make cells raised or sunken. You can also change the look of the horizontal and
          vertical gridlines, and the cell background.




                                  The Datasheet Formatting dialog box




   Procedures
          1. Open a table in Datasheet view.
          2. Select the Format menu.
          3. Select the Datasheet command.

Page 58                                                              Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                     Lesson 4 - Editing Tables


          4. Select the desired option.
          5. Select OK.




    Step-by-Step
          Change a cell effect.

          If necessary, open the Items table in Datasheet view.

           Steps                                            Practice Data
           1. Select the Format menu.                       Click Format
              The Format menu appears.
           2. Select the Datasheet command.                 Click Datasheet...
              The Datasheet Formatting dialog box
              opens.
           3. Select the desired option.                    Click Raised under
              A preview of the selected option              Cell Effect
              appears in the Sample box.
           4. Select OK.                                    Click OK
              The Datasheet Formatting dialog box
              closes, and the cell effect is applied to
              the table.



SELECTING A COLUMN

    Discussion
          Before you can manipulate table columns, you must select them. You can perform a
          number of actions on selected columns, as well as select a single column or multiple
          adjacent columns.



                     Column headers are also called field selectors.



                     To select multiple adjacent columns, drag across the headers
                      of the columns you want to select.




Canadian Beaver (CBIT)                                                                   Page 59
Lesson 4 - Editing Tables                                                   Access 2003 - Lvl 1



                   Right-clicking a column header selects the column and
                    displays the column shortcut menu at the same time. The
                    shortcut menu provides access to common column commands,
                    such as changing column width or hiding columns.




   Procedures
          1. Open a table in Datasheet view.
          2. Click the header of the column you want to select.




    Step-by-Step
          Select a column in a table.

          If necessary, open the Items table in Datasheet view.

           Steps                                         Practice Data
           1. Click the header of the column you         Click the Product
              want to select.                            Description header
              The column is selected.

          Click in any field to deselect the column.


MOVING A COLUMN

    Discussion
          You can move one or more columns in Datasheet view. For example, you can
          reposition columns to view data more easily or organize the datasheet logically.

          You can move a single column or multiple, adjacent columns. Before you can move a
          column or multiple columns, however, you must first select them.



                   Moving columns does not change the order of the fields in the
                    table design.




Page 60                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                    Lesson 4 - Editing Tables



                     To undo a column move, you must close the table without
                      saving the changes. You cannot use the Undo feature to undo
                      a column move.


                     When you are dragging a column to move it, a vertical divider
                      bar appears in the destination location when the mouse pointer
                      is correctly positioned.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the column you want to move.
          3. Drag the column to the new location.




    Step-by-Step
          Move a column in a table.

          If necessary, open the Items table in Datasheet view.

           Steps                                           Practice Data
           1. Select the column you want to move.          Click the Product ID
              The column is selected.                      header
           2. Drag the column to the desired               Drag the Product ID
              location.                                    column to the right of the
              The column appears in the new                Product Description
              location.                                    column, until a vertical
                                                           divider bar appears
                                                           between the columns




Canadian Beaver (CBIT)                                                                  Page 61
Lesson 4 - Editing Tables                                                  Access 2003 - Lvl 1



HIDING A COLUMN

    Discussion
          If you do not regularly view or edit one or more table fields, you can hide their
          corresponding table columns. Hiding columns can make your datasheet narrower and
          easier to view.

          You can hide a single column or multiple adjacent columns.



                  You can also use the Hide Columns command on the column
                   header shortcut menu to hide the current column.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the column you want to hide.
          3. Select the Format menu.
          4. Select the Hide Columns command.




    Step-by-Step
          Hide a column in a table.

          If necessary, open the Items table in Datasheet view.

           Steps                                        Practice Data
           1. Select the column you want to hide.       Click the Unit Price
              The column is selected.                   header
           2. Select the Format menu.                   Click Format
              The Format menu appears.
           3. Select the Hide Columns command.          Click Hide Columns
              The column disappears from the table.




Page 62                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                 Lesson 4 - Editing Tables



UNHIDING A COLUMN

    Discussion
          You can redisplay hidden columns. The Unhide Columns dialog box lists all the
          columns in the table, with a check box to the left of each column name. The check box
          is deselected for hidden columns. You can select the check box to redisplay a hidden
          column.




                                      The Unhide Columns dialog box




                     You can also use the Unhide Columns dialog box to hide
                      multiple, non-adjacent columns.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the Format menu.
          3. Select the Unhide Columns command.
          4. Select the column you want to unhide.
          5. Select Close.


Canadian Beaver (CBIT)                                                                Page 63
Lesson 4 - Editing Tables                                                  Access 2003 - Lvl 1




    Step-by-Step
          Unhide a column in a table.

          If necessary, open the Items table in Datasheet view and hide the Unit Price column.

           Steps                                         Practice Data
           1. Select the Format menu.                    Click Format
              The Format menu appears.
           2. Select the Unhide Columns                  Click Unhide Columns...
              command.
              The Unhide Columns dialog box
              opens.
           3. Select the column you want to unhide.      Click    Unit Price
              The column check box is selected, and
              the column appears in the table.
           4. Select Close.                              Click Close
              The Unhide Columns dialog box
              closes.

          Close and save the Items table.


FREEZING A COLUMN

    Discussion
          If you have a table with multiple fields, all of the columns may not display on screen
          at the same time. You can scroll to the right to view additional columns; however, you
          may not be able to see the columns at the far left, which may display necessary
          information, such as the record identification.

          You can freeze columns in Datasheet view. Freezing one or more columns allows
          you to see the information in the frozen columns no matter how many columns you
          scroll to the right.



                  You can also use the Freeze Columns command on the
                   column header shortcut menu to freeze the current column. If
                   you freeze a column other than the first column, however, the
                   frozen column moves to the far left position in the datasheet.


Page 64                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                       Lesson 4 - Editing Tables



                     A solid, dark line appears to the right of the last frozen
                      column.


                     To unfreeze columns, select the Format menu and the
                      Unfreeze All Columns command.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the column you want to freeze.
          3. Select the Format menu.
          4. Select the Freeze Columns command.




    Step-by-Step
          Freeze a column in a table.

          Open the Orders table in Datasheet view. Scroll to the right to view the Catalog
          column. Notice that the left columns scroll out of view. Scroll back to view the Order
          Number column.

           Steps                                             Practice Data
           1. Select the column you want to freeze.          Click the Order Number
              The column is selected.                        header
           2. Select the Format menu.                        Click Format
              The Format menu appears.
           3. Select the Freeze Columns command.             Click Freeze Columns
              The selected column is frozen.

          Click anywhere in the table to deselect the column. Scroll to the right. Notice that the
          Order Number column remains on screen. Select the Format menu and then select
          the Unfreeze All Columns command to unfreeze the column.

          Close the Orders table without saving the changes.
          Close WORLD05.MDB.




Canadian Beaver (CBIT)                                                                     Page 65
Lesson 4 - Editing Tables                                                  Access 2003 - Lvl 1




EXERCISE
EDITING TABLES

      Task
          Edit a table.

          1. Open Train05x.
          2. Open the Client table in Datasheet view.
          3. Increase the row height by approximately 25%.
          4. Change the font to Arial Narrow and the font size to 12.
          5. Widen the Name and Address columns so that all data in them
             appears.
          6. Change the cell effect to Sunken.
          7. Move the Client ID column to the right of the Name column.




          8. Hide the Client ID column.
          9. Unhide the Client ID column.
          10. If necessary, resize the Datasheet window so that the columns at the
              far right do not appear.


Page 66                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                              Lesson 4 - Editing Tables


          11. Freeze the Name column, then scroll to view the Phone No column.
          12. Unfreeze all columns.
          13. Close the table without saving the changes.
          14. Close the database.




Canadian Beaver (CBIT)                                                            Page 67
LESSON 5 -
CREATING TABLES

In this lesson, you will learn how to:
         Use the Database Wizard

         Create a new database

         Design tables

         Create a table in Design view

         Use Design view

         Add field names

         Assign data types

         Add a field description

         Set a primary key

         Save a new table

         Use the Table Wizard
Access 2003 - Lvl 1                                          Lesson 7 - Creating Relationships




USING THE DATABASE WIZARD

    Discussion
          When you create a database in Access, you are creating a container for related tables,
          forms, queries, reports, and other database objects. You can create a new database
          manually or with the help of the Database Wizard. If you create a new database
          manually, you must then create your own tables, queries, and other objects. To save
          time, you can use the Database Wizard.

          Access 2003 offers several Database Wizard templates you can use to quickly create a
          database. The Database Wizard templates include preformatted database objects, such
          as tables, forms, and reports. A variety of business and personal Database Wizard
          templates are available on the Databases page in the Templates dialog box. If you use
          any Database Wizard template, Access creates not only the database, but also the
          tables, queries, and other objects in it.

          When you use the Database Wizard, you select the fields you want to use in the
          database tables, the desired screen style, and the desired report style. Lastly, you can
          add a database title, include a picture on reports, and choose whether to open the
          database in the Database window or start the database from its Switchboard. The first
          time you start the database, a message box prompts you to enter your name and
          address information. After you have provided this information, the Main Switchboard
          (a form containing command buttons and other objects to help you navigate and use
          the database) opens.

          After you have used a Database Wizard template, a link to it appears under New from
          template in the New File task pane. You can click this link to quickly create another
          database using the same template and settings.




                           Using the Database Wizard to create a new database


Canadian Beaver (CBIT)                                                                    Page 69
Lesson 7 - Creating Relationships                                              Access 2003 - Lvl 1




                   When you create a new database, it is saved in the default file
                    format for Access 2000 so that it can be opened in Access
                    2000 as well as Access 2002 and 2003. To create databases to
                    be used strictly in Access 2003, you can change the default
                    file format by selecting the Tools menu, the Options
                    command, and the Advanced tab in the Options dialog box.
                    Then, select the Default File Format list and Access 2003.




   Procedures
          1. Click the New button           on the Database toolbar.
          2. Select the General Templates link under New from template in the
             New File task pane.
          3. Select the Databases tab.
          4. Select the desired Database Wizard template.
          5. Select OK.
          6. Type the desired file name.
          7. Select the Save in list.
          8. Select the drive where you want to store the file.
          9. Open the folder where you want to store the file.
          10. Select Create.
          11. Select Next >.
          12. Select the table in the Tables in the database list box for which you
              want to view the fields.
          13. Select or deselect fields in the Fields in the table list box to include
              them in or to exclude them from the database, respectively.
          14. Select Next >.
          15. Select the desired screen style.
          16. Select Next >.
          17. Select the desired report style.
          18. Select Next >.
          19. Type the desired database title.


Page 70                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 7 - Creating Relationships


          20. Select Next >.
          21. Indicate whether or not you want to start the database after the
              Database Wizard has built it.
          22. Select Finish.
          23. Select OK.




    Step-by-Step
          Use the Database Wizard to create a new database.

           Steps                                          Practice Data
           1. Click the New button on the Database
              toolbar.                                    Click
              The New File task pane opens.
           2. Select the General Templates link           Click General
              under New from template in the New          Templates...
              File task pane.
              The Templates dialog box opens.
           3. Select the Databases page.                  Click the Databases tab
              The Databases page appears.
           4. Select the desired Database Wizard          Click Inventory Control
              template.
              The Database Wizard template is
              selected.
           5. Select OK.                                  Click OK
              The File New Database dialog box
              opens with the text in the File name
              box selected.
           6. Type the desired file name.                 Type Supply
              The text appears in the File name box.
           7. Select the Save in list.                    Click Save in
              A list of available drives appears.
           8. Select the drive where you want to          Click the student data
              store the file.                             drive, if necessary
              A list of available folders appears.




Canadian Beaver (CBIT)                                                                Page 71
Lesson 7 - Creating Relationships                                            Access 2003 - Lvl 1



           Steps                                          Practice Data
           9. Open the folder where you want to           Double-click to open the
              store the file.                             student data folder, if
              A list of available folders and files       necessary
              appears.
           10. Select Create.                             Click Create
               The File New Database dialog box
               and the New File task pane close, and
               the Database window and the
               Database Wizard open.
           11. Select Next >.                             Click Next >
               The next page of the Database Wizard
               appears.
           12. Select the table in the Tables in the      Click Information about
               database list box for which you want       employees
               to view the fields.
               The table is selected and the
               corresponding fields appear in the
               Fields in table list box.
           13. Select or deselect fields in the Fields    Click Email Name to
               in the table list box to include them in   select it
               or to exclude them from the database
               respectively.
               The fields are selected or deselected
               accordingly.
           14. Select Next >.                             Click Next >
               The next page of the Database Wizard
               appears.
           15. Select the desired screen style.           Click Stone
               A preview of the screen style appears
               in the preview box.
           16. Select Next >.                             Click Next >
               The next page of the Database Wizard
               appears.
           17. Select the desired report style.           Click Casual
               A preview of the report style appears
               in the preview box
           18. Select Next >.                             Click Next >
               The next page of the Database Wizard
               appears with the text in the What
               would you like the title of the
               database to be? box selected.



Page 72                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 7 - Creating Relationships



           Steps                                          Practice Data
           19. Type the desired database title.           Type Inventory
               The text appears in the What would
               you like the title of the database to
               be? box.
           20. Select Next >.                             Click Next >
               The final page of the Database Wizard
               appears.
           21. Indicate whether or not you want to        Click Yes, start the
               start the database after the Database      database. to deselect it
               Wizard has built it.
               The Yes, start the database. option is
               selected or deselected accordingly.
           22. Select Finish.                             Click Finish
               The Database Wizard closes, and the
               Database Wizard message box notifies
               you when the database has been
               created.
           23. Select OK.                                 Click OK
               The Database Wizard message box
               closes.

          Notice the tables that were automatically created by the Database Wizard in the
          Tables object list. Select the Forms object type to display the Forms object list.
          Then, close the Supply database.

          Select the New button on the Database toolbar to display the New File task pane.
          Then, select the Supply link under Open a file to open the database. Select OK to
          close the Microsoft Access message box; then, enter the desired information into the
          My Company Information window. When you have finished entering the information,
          close the My Company Information window.

          Notice that the Main Switchboard window opens and the Supply: Database window is
          minimized. This is how the database will open in the future.

          Click Enter/View Products in the Main Switchboard window to view the formatted
          entry form. Then, close the Products form. Click the Restore Up button on the
          minimized Database window. Then, close the Database window to close the Supply
          database.
          Close SUPPLY.MDB.




Canadian Beaver (CBIT)                                                                   Page 73
Lesson 7 - Creating Relationships                                           Access 2003 - Lvl 1



CREATING A NEW DATABASE

    Discussion
          You can create a new database in Access without using the Database Wizard;
          however, you must then create the tables, queries, and other objects in the database.

          When you create a new database, you must specify the desired file name, as well as
          where you want to store the database file.

          After you have created the database, the Database window opens. Its toolbar buttons
          allow you to open objects, modify the design of existing objects, and create new
          objects. The Objects bar in the Database window allows you to access the following
          object types: Tables, Queries, Forms, Reports, Pages, Macros, and Modules.
          Created objects can belong to any of these categories.



                  The New File task pane closes as soon as you create a new,
                   blank database.


                  You can also create a new, blank database by selecting the
                   General Templates link on the New File task pane and the
                   Blank Database icon on the General page in the Templates
                   dialog box.


                  To open the New File task pane, select the File menu and the
                   New command.




   Procedures
          1. Click the New button          on the Database toolbar.
          2. Select the Blank Database link under New in the New File task
             pane.
          3. Type the desired file name.
          4. Select the Save in list.
          5. Select the drive where you want to store the file.
          6. Open the folder where you want to store the file.
          7. Select Create.


Page 74                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 7 - Creating Relationships




    Step-by-Step
          Create a new, blank database.

           Steps                                            Practice Data
           1. Click the New button on the Database
              toolbar.                                      Click
              The New File task pane opens.
           2. Select the Blank Database link under          Click Blank Database
              New in the New File task pane.
              The File New Database dialog box
              opens with the text in the File name
              box selected.
           3. Type the desired file name.                   Type Company
              The text appears in the File name box.
           4. Select the Save in list.                      Click Save in
              A list of available drives appears.
           5. Select the drive where you want to            Click the student data
              store the file.                               drive, if necessary
              A list of folders appears.
           6. Open the folder where you want to             Double-click to open the
              store the file.                               student data folder, if
              A list of available folders and files         necessary
              appears.
           7. Select Create.                                Click Create
              The File New Database dialog box
              and the New File task pane close, and
              the new database opens in the
              Database window.



DESIGNING TABLES

    Discussion
          In Access, there are five steps to designing a table. The first step is to develop a
          thorough understanding of the system, including all the data to be entered and all the
          reports, statistics, labels, and other output you want to generate. It is helpful to gather
          all the forms you use for input and all the reports generated from the data. This

Canadian Beaver (CBIT)                                                                       Page 75
Lesson 7 - Creating Relationships                                             Access 2003 - Lvl 1


          process serves two purposes. First, these forms and reports are invaluable when you
          begin detailing exactly what information you want stored in each table. Second, once
          you have collected a set of these forms, you can make sure that every item of
          information on your reports is either included in your database or can be derived from
          data in your database.

          The second step is to determine how many tables you need and what information to
          store in each table. Once you have gained an overview of the system, you are ready to
          begin designing your database. The most important decision you must make before
          you begin creating tables is how many tables you want the database to include.

          The third step is to design the tables by selecting the fields you want to include, the
          type of data you want to store in each field, and the size of the fields. Once you have
          developed an overview of your application and a list of tables, you can make a
          preliminary list of fields, based on the categories of information that each table will
          include. You can refine the list by considering the following: if the data is to be sorted
          or selected, it should be in a separate field; if the data is to be calculated from other
          fields, you do not need to store it in its own field; and, if the table is to be linked to
          another table, both tables should contain common fields.

          The fourth step is to create the table structures by defining the name, data type, and
          size of the fields.

          The fifth and final step is to enter sample data. This step is important because it helps
          you determine whether or not the table fields are the correct size and if all the
          necessary fields have been included. You can then modify the tables as necessary.



CREATING A TABLE IN DESIGN VIEW

    Discussion
          A table is the basic building block of a database. All queries, reports, and forms use
          the fields and records in the database tables as the basis for their output. You must
          create at least one table in the database before you can create any other object.

          When you create a new table, you can use either Datasheet or Design view, work in
          the Table Wizard, import data from another data source, or link to data in another data
          source. Usually, a table is created in Design view, which gives you the most control
          over the database design.




Page 76                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 7 - Creating Relationships




                                       The blank table in Design view




                     You can also create a new table in Design view by double-
                      clicking the Create table in Design view object in the Tables
                      object list.


                     Datasheet view displays a grid of rows and columns. Field
                      names are entered as column headings.


                     The Table Wizard uses a series of steps to help you create a
                      table. You can select a sample table on which to base your
                      table and choose field names from commonly used fields, such
                      as LastName and CompanyName.




   Procedures
          1. Display the Tables objects.

          2. Select the         New button on the Database window toolbar.
          3. Select Design View.
          4. Select OK.




Canadian Beaver (CBIT)                                                                Page 77
Lesson 7 - Creating Relationships                                              Access 2003 - Lvl 1




    Step-by-Step
          Create a new table in Design view.

          If necessary, display the Tables object list in the Database window.

           Steps                                            Practice Data
           1. Select the New button on the Database
              window toolbar.                               Click       New
              The New Table dialog box opens.
           2. Select Design View.                           Click Design View
              Design View is selected and its
              description appears the New Table
              dialog box.
           3. Select OK.                                    Click OK
              The New Table dialog box closes, and
              the new table appears in Design view.



USING DESIGN VIEW

    Discussion
          The Design view window is split into two panes. The upper pane displays a design
          grid. You can use the rows and columns in this design grid to enter the field names,
          data types, and field descriptions for each field in the table. The small box to the left
          of each field name is the row selector. A black triangle in the row selector indicates
          the current field.

          The lower pane displays the properties of the selected field. A property is a
          characteristic of a field, such as the number of characters a field can contain. Access
          automatically assigns default field properties, which you can modify as desired.



                   Once you have entered at least one field, you can press the
                    [F6] key to move between the upper and lower panes of the
                    Design view window.




Page 78                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships



ADDING FIELD NAMES

    Discussion
          Field names are entered into the Field Name column in the Design view window.
          Field names are labels that identify the data stored in a field. For example, the
          OrderNumber field will most likely contain order numbers.

          Field names can be up to 64 characters long and can include letters, numbers, and
          spaces. (Field names, however, cannot begin with a space.) Field names cannot
          contain a period (.), an exclamation point (!), a back quote (`), brackets ([ ]), or ASCII
          control characters. In addition, field names within a table must be unique.

          Making field names descriptive and meaningful can help with data entry and data
          retrieval. However, you should avoid long and complicated field names because they
          can be cumbersome to remember and difficult to reference when performing database
          functions.



                     The order in which the field names appear in Design view
                      determines the order in which the columns appear in Database
                      view.


                     Do not include spaces in field names if you work with other
                      database formats or plan to use macros with VBA (Visual
                      Basic for Applications) code.




   Procedures
          1. Open or create a table in Design view.
          2. Type the desired field name.
          3. Press [Down].




    Step-by-Step
          Add field names in Design view.

          If necessary, create a new table in Design view and select the first blank cell in the
          Field Name column.


Canadian Beaver (CBIT)                                                                     Page 79
Lesson 7 - Creating Relationships                                            Access 2003 - Lvl 1




           Steps                                          Practice Data
           1. Type the desired field name.                Type OrderNumber
              The text appears in the Field name
              column of the current row.
           2. Press [Down].                               Press [Down]
              The insertion point moves down one
              row, and a default data type appears
              in the Data Type column of the
              previous row.

          Practice the Concept: Type ProductID and Quantity as the next two field names;
          pressing [Down] after each.


ASSIGNING DATA TYPES

    Discussion
          The field data type tells Access what kind of values you plan to store in a field and
          how much storage space to set aside for the field. Although you can change a data
          type after a field contains data, doing so may erase some or all of the information in
          the field.

          The following table describes the available data types:

           Data Type        Description
           Text             Text is the default data type; a text field can contain
                            any combination of letters, numbers, punctuation
                            marks, and spaces. The default width is 50 characters
                            and the maximum length is 255 characters.
           Memo             A memo field is similar to a text field, except that a
                            memo field can contain up to 65,535 characters. You
                            can use memo fields for notes or long descriptions in a
                            database.
           Number           A number field can contain only numeric characters, a
                            comma (used as a thousands separator), a period (used
                            as a decimal point), and a hyphen (used as a negative
                            number sign). You should use a number field only
                            when you want to perform calculations using the field
                            values. For example, even though zip codes and
                            telephone numbers consist of numeric characters, you
                            would not use them in calculations; therefore, they
                            should be entered in text rather than number fields.


Page 80                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                       Lesson 7 - Creating Relationships



           Data Type     Description
           Date/Time     The Date/Time data type is used for dates and/or times.
                         Access automatically validates all entries to ensure that
                         they are valid dates and/or times. For example, Access
                         will not allow you to enter 2/31/99 because February
                         does not have 31 days. Date/Time fields are useful in
                         performing calculations on dates and times.
           Currency      The Currency data type is similar to the Number data
                         type and can be used in calculations. However, the
                         values in a currency field can have a maximum of four
                         decimal places and automatically appear with dollar
                         signs and thousands separators (commas). You should
                         use currency fields whenever possible because they use
                         fixed point calculation, which is faster than the floating
                         point calculation used in number fields.
           AutoNumber    The AutoNumber data type allows Access to
                         automatically assign a unique number (consecutively
                         from 1) to each record in the database. You cannot
                         manually enter a value into an AutoNumbered field or
                         change a number Access has assigned to a record. You
                         use this data type when you want a unique
                         identification number for each record.
           Yes/No        A Yes/No data type is used to signify one of two
                         conditions, Yes or No. You use a yes/no field when
                         only two possibilities (i.e., True or False) exist for a
                         field value.
           OLE object    The OLE object data type allows you to either embed
                         an object created in another Windows application or
                         create a link to such an object. You use an OLE object
                         data type for graphics, spreadsheets, or sound files.
           Hyperlink     A Hyperlink data type is used to store a link to
                         anywhere you choose. The link could go to an Internet
                         page, a Word document on an intranet, or even a form
                         in the current database. A hyperlink field can contain a
                         description, an address, and a sub-address [separated by
                         number signs (#)], as well as up to 2,048 characters;
                         only the address, however, is mandatory.
           Lookup        The Lookup Wizard data type allows you to create a
           Wizard        lookup field. A lookup field displays a list of values
                         that are either stored in another table or created by you.
                         A lookup field allows you to choose values from a list
                         during data entry, thereby reducing repetitive typing
                         and eliminating typing errors.




Canadian Beaver (CBIT)                                                                Page 81
Lesson 7 - Creating Relationships                                          Access 2003 - Lvl 1




                                             Assigning a data type




   Procedures
          1. Open or create a table in Design view.
          2. Enter field names as desired.
          3. Select the Data Type column in the field for which you want to
             change the data type.
          4. Select the Data Type list.
          5. Select the desired data type.




    Step-by-Step
          Assign data types to database fields.

           Steps                                           Practice Data
           1. Select the Data Type column in the           Click in the Data Type
              field for which you want to change the       column for the Quantity
              data type.                                   field
              The insertion point and a drop-down
              arrow appear in the corresponding
              Data Type column.

Page 82                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 7 - Creating Relationships



           Steps                                         Practice Data
           2. Select the Data Type list.                 Click Data Type
              A list of available data types appears.
           3. Select the desired data type.              Click Number
              The selected data type appears.



ADDING A FIELD DESCRIPTION

    Discussion
          You can use the Description column in the design grid to enter a description for each
          field. The description appears on the status bar when the field is accessed on a form.
          You can enter brief comments as to the purpose of the field or the data that should be
          stored in it.




   Procedures
          1. Open or create a table in Design view.
          2. Enter field names as desired.
          3. Select the Description column in the field to which you want to add
             a description.
          4. Type the desired description.




    Step-by-Step
          Add a description to a field.

           Steps                                         Practice Data
           1. Select the Description column in the       Click in the Description
              field to which you want to add a           column for the
              description.                               OrderNumber field
              The insertion point appears in the
              corresponding Description column.




Canadian Beaver (CBIT)                                                                  Page 83
Lesson 7 - Creating Relationships                                            Access 2003 - Lvl 1



           Steps                                          Practice Data
           2. Type the desired description.               Type Enter the order
              The text appears in the Description         number
              column.

          Practice the Concept: Type the following description for the ProductID field: Enter
          the catalog number of the item.


SETTING A PRIMARY KEY

    Discussion
          Access works most efficiently if you specify a primary key for each table. The
          primary key is a field or group of fields that uniquely identifies each record in the
          table. Therefore, the value of the key field, or the combined values of a group of key
          fields, must not be found in more than one record. Consequently, a LastName field
          would be a poor primary field, because several records may have the same last name;
          it would not, therefore, be unique.

          There are several advantages to setting a primary key. First, the primary key is
          automatically indexed, which makes information retrieval faster. Second, when you
          open a table, the records are automatically sorted in order by the primary key. Finally,
          a primary key prevents the entry of duplicate data because Access does not allow
          duplicates in the primary key field.



                   If you have trouble identifying the primary key field, you can
                    create an AutoNumber field and designate it as the primary
                    key.


                   You cannot choose a memo, OLE, or hyperlink field as the
                    primary key.


                   To create a multi-field primary key, hold the [Ctrl] key, click
                    the desired fields, and then click the Primary Key button on
                    the Table Design toolbar.




   Procedures
          1. Open or create a table in Design view.
          2. Enter field names as desired.


Page 84                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships


          3. Select the field you want to designate as the primary key.


          4. Click the Primary Key button             on the Table Design toolbar.




    Step-by-Step
          Set a field as the primary key.

           Steps                                           Practice Data
           1. Select the field you want to designate       Click OrderNumber in
              as the primary key.                          the Field Name column
              The insertion point appears in the
              field.
           2. Click the Primary Key button on the
              Table Design toolbar.                        Click
              A key appears in the row selector for
              the designated field and the field is set
              as the primary key.



SAVING A NEW TABLE

    Discussion
          After you have designed the fields for a table, you must save the table before you can
          add any data. When you save a new table, you should give it a name that describes the
          records it stores. You can use up to 64 characters, including spaces. These characters
          can include letters, numbers, and spaces. They cannot contain a period (.), an
          exclamation point (!), a back quote (`), brackets ([]), or ASCII control characters.

          After you have saved a table, an icon for it appears in the Tables object list in the
          Database window.



                     When you save a table for the first time, Access opens the
                      Save As dialog box, in which you enter the desired table
                      name.


                     When you save a table, you are not creating a file. You are
                      adding an object to the existing database.



Canadian Beaver (CBIT)                                                                     Page 85
Lesson 7 - Creating Relationships                                            Access 2003 - Lvl 1




   Procedures
          1. Create a table in Design view.
          2. Enter the desired table data.


          3. Click the Save button           on the Table Design toolbar.
          4. Type the desired table name.
          5. Select OK.




    Step-by-Step
          Save a new table.

           Steps                                           Practice Data
           1. Click the Save button on the Table
              Design toolbar.                              Click
              The Save As dialog box opens with the
              text in the Table Name box selected.
           2. Type the desired table name.                 Type Line Items
              The text appears in the Table Name
              box.
           3. Select OK.                                   Click OK
              The Save As dialog closes.

          Close the Design view window. Notice that the table appears in the Tables object list
          in the Database window.


USING THE TABLE WIZARD

    Discussion
          The Table Wizard leads you through the steps needed to create a new table. The Table
          Wizard provides many table templates for typical business and personal use. You
          select the type of table you want to create, and then the fields you want to add to the
          table.


Page 86                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships



          The Table Wizard allows you to relate your new table to an existing table in your
          database. However, if your database does not contain any tables when you begin the
          Table Wizard, you will not be prompted to relate tables.




                                               The Table Wizard




                     You can use the right-pointing, double arrow button on the
                      first page of the Table Wizard to add all the fields in the
                      Sample Fields list to your new table. The left-pointing arrow
                      buttons allow you to remove either the selected field or all
                      fields from the Fields in my new table list box.


                     You can rename any field in the Fields in my new table list
                      box by selecting it and then selecting the Rename Field
                      button.


                     You can also activate the Table Wizard by double-clicking the
                      Create table by using wizard object in the Tables object list.




   Procedures
          1. Display the Tables object list.

          2. Select the        New button on the Database window toolbar.
          3. Select Table Wizard.
          4. Select OK.


Canadian Beaver (CBIT)                                                                 Page 87
Lesson 7 - Creating Relationships                                             Access 2003 - Lvl 1


          5. Select the Business or Personal option, as desired.
          6. Select the desired table from the Sample Tables list box.
          7. Select a field you want to add to your table from the Sample Fields
             list box.

          8. Select        to the right of the Sample Fields list box.
          9. Add other fields as desired.
          10. Select Next >.
          11. Type the desired table name in the What do you want to name your
              table? box.
          12. Select the desired primary key option.
          13. Select Next >.
          14. If you chose to set your own primary key, select the What field will
              hold data that is unique for each record? list.
          15. Select the desired field.
          16. Select the desired data option for the primary key field.
          17. Select Next >.
          18. Review or create table relationships, as desired.
          19. Select Next >.
          20. Select the desired view option.
          21. Select Finish.




    Step-by-Step
          Use the Table Wizard to create a table.

          If necessary, display the Tables object list in the Database window.

           Steps                                          Practice Data
           1. Select the New button on the Database       Click      New
              window toolbar.
              The New Table dialog box opens.
           2. Select Table Wizard.                        Click Table Wizard
              Table Wizard is selected.



Page 88                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 7 - Creating Relationships



           Steps                                           Practice Data
           3. Select OK.                                   Click OK
              The New Table dialog box closes, and
              the Table Wizard opens.
           4. Select the Business or Personal              Click Business, if
              option, as desired.                          necessary
              The option is selected.
           5. Select the desired table from the            Click Products
              Sample Tables list box.
              The fields in the selected table appear
              in the Sample Fields list box.
           6. Select a field you want to add to your       Click ProductID, if
              table from the Sample Fields list box.       necessary
              The field is selected.

           7. Select       to the right of the Sample      Click
              Fields list box.
              The field name appears in the Fields
              in my new table list box.
           8. Add other fields as desired.                 Follow the instructions
              The field names appear in the Fields         below the table before
              in my new table list box.                    continuing on to the next
                                                           step
           9. Select Next >.                               Click Next >
              The next page of the Table Wizard
              appears with the text in the What do
              you want to name your table? box
              selected.
           10. Type the desired table name in the          Type Items
               What do you want to name your
               table? box.
               The text replaces the default text in the
               What do you want to name your
               table? box.
           11. Select the desired primary key option.      Click No, I‟ll set the
               The option is selected.                     primary key.
           12. Select Next >.                              Click Next >
               The next page of the Table Wizard
               appears.




Canadian Beaver (CBIT)                                                                 Page 89
Lesson 7 - Creating Relationships                                              Access 2003 - Lvl 1



           Steps                                           Practice Data
           13. If you chose to set your own primary        Click What field will
               key, select the What field will hold        hold data that is unique
               data that is unique for each record?        for each record?
               list.
               A list of available fields appears.
           14. Select the desired field.                   Click ProductID, if
               The field is selected.                      necessary
           15. Select the desired data option for the      Click Numbers and/or
               primary key field.                          letters I enter when I
               The option is selected.                     add new records.
           16. Select Next >.                              Click Next >
               The next page of the Table Wizard
               appears.
           17. Select Next >.                              Click Next >
               The next page of the Table Wizard
               appears.
           18. Select the desired view option.             Click Enter data
               The option is selected.                     directly into the table., if
                                                           necessary
           19. Select Finish.                              Click Finish
               The Table Wizard closes, and the new
               table appears in Datasheet view.

          Add the ProductDescription and UnitPrice fields to the table.

          Return to the table and continue on to the next step (step 9).

          Close the Datasheet view window. Notice the new Items object in the Tables object
          list.
          Close COMPANY.MDB.




Page 90                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 7 - Creating Relationships




EXERCISE
CREATING TABLES

      Task
          Create a new database and table.

          1. Use the Order Entry Database Wizard to create a database. Name
             the database Orders and save it to the student data folder. (Hint: Use
             the General Templates... link.)
          2. Accept the defaults for the fields, as well as for the screen and report
             styles. Enter Orders as the database title and deselect the Yes, start
             the database. option to display the database in the Database
             window.
          3. Open the Order Details table in Design view.
          4. After you have viewed the table, close it. Then, close the Orders
             database as well.
          5. Create a blank database named Training and save it to the student
             data folder.
          6. From the Tables object list, create a new table in Design view.
          7. Add the following fields and their corresponding data types to the
             table:

                          Field Name               Data Type
                          ProjectID                Text
                          ClientID                 Text
                          CourseName               Text
                          StartDate                Date/Time
                          EndDate                  Date/Time
                          TrainerInitials          Text
                          Cost                     Number

          8. Enter the following description for the ProjectID field: Enter the
             ProjectID assigned by Marketing..
          9. Set the ProjectID field as the primary key.
          10. Save the table as Project.


Canadian Beaver (CBIT)                                                                  Page 91
Lesson 7 - Creating Relationships                                               Access 2003 - Lvl 1




          11. Close the Design view window.
          12. Use the Table Wizard to create a new table.
          13. Select the Business category and the Payments sample table. Then,
              add the following fields: ProjectID, PaymentDate,
              PaymentAmount, and PaymentMethod.
          14. Accept the default name for the table and choose to set the primary
              key manually.
          15. Set the ProjectID field as the primary key and select the Numbers I
              enter when I add new records. option.
          16. Do not relate the new table to any other table in the database.
          17. Choose to enter data directly into the table and finish creating the
              table.
          18. Close both the table and the database.




Page 92                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                              Lesson 7 - Creating Relationships



     LESSON 6 -
     SETTING FIELD PROPERTIES

     In this lesson, you will learn how to:
                Use field properties

                Limit field size

                Set number formats

                Set date/time formats

                Set yes/no formats

                Set default values

                Set validation rules

                Create an input mask - wizard

                Create an input mask manually

                Create a custom input mask

                Type a lookup list

                Modify Lookup properties




Canadian Beaver (CBIT)                                                    Page 93
Lesson 7 - Creating Relationships                                             Access 2003 - Lvl 1




USING FIELD PROPERTIES

    Discussion
          Each field has a set of properties that control the way it stores, handles, and displays
          data. Since forms and reports you create use the fields in your tables, setting field
          properties in the early stages of building a database can save you time later on; you
          will have less design work to do in later stages if you set the desired field properties
          before you create any forms and reports.

          You normally set field properties when you create a table in Design view. If you have
          created and saved a table using default field properties, you can open the table in
          Design view to change its property settings.

          The properties available in the Field Properties pane depend on the data type
          assigned to the selected field. Some of the property types you can set are listed in the
          following table:

           Property type           Description
           Field Size              Limits a Text field to a specific number of
                                   characters; limits a Number field to a specific
                                   type of number
           Format                  Controls the way values appear in Datasheet
                                   view
           Decimal places          Available for Number and Currency fields only,
                                   determines how many decimal places will appear
                                   in the field; this property type has no effect on
                                   Number fields using the General format
           Input Mask              Creates a pattern for data entered into the field
                                   (such as adding hyphens within a telephone
                                   number)
           Caption                 Creates a label other than the field name; the
                                   caption will appear in the table and on forms and
                                   reports
           Default Value           Specifies the value you want to appear in the
                                   selected field in all new records
           Validation Rule         Forces data entered into the selected field to meet
                                   a specified requirement; for example, you can
                                   specify that the Credit Limit field not be over
                                   $10,000



Page 94                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                             Lesson 7 - Creating Relationships



           Property type             Description
           Validation Text           Creates an error message to appear when the data
                                     entered violates a validation rule
           Required                  Specifies that the field cannot be left empty
           Allow Zero Length         Determines whether or not you can enter
                                     quotation marks (“ ”) in a Text, Memo or
                                     Hyperlink field to indicate that there is no data
                                     for that field
           Indexed                   Speeds up retrieval of data in a field; all primary
                                     key fields are automatically indexed




                     A brief description of the selected property appears in the
                      Field Properties pane.


                     You can press the [F1] key for help with the selected property.




LIMITING FIELD SIZE

    Discussion
          Setting a field size limits the number of characters or the type of characters you can
          enter into a field. After typing the maximum number of characters allowed, further
          keystrokes are not permitted. The Field Size property is available only for Text,
          Number, and AutoNumber data types; all other data types have default sizes that are
          set automatically.

          For a Text field, the field size is the maximum number of characters you want to
          allow in the field, up to the maximum of 255 characters allowed by Access. For a
          Number field, you can select one of the following Field Size options:

            Field Size           Size Range                         Decimal Places
            Byte                 0 to 255 (no fractions)            None; data is
                                                                    rounded
            Integer              -32768 to 32767 (no fractions)     None; data is
                                                                    rounded
            Long Integer         -2,147,483,648 to                  None; data is
                                 2,147,483,647 (no fractions)       rounded


Canadian Beaver (CBIT)                                                                     Page 95
Lesson 7 - Creating Relationships                                                  Access 2003 - Lvl 1


            Field Size          Size Range                              Decimal Places
                                         38              38
            Single              -3.4x10 to 3.4x10                       Up to 7
                                              308                 308
            Double              -1.797x10           to 1.797x10         Up to 15
            Replication ID      Globally unique identifier.             Not available
                                    38        38
            Decimal             -10 to 10                               Up to 28

          For an AutoNumber field, only the Long Integer and Replication ID options are
          available.



                    The default field size for Number fields is Long Integer,
                     which is also the largest field size. You should use the
                     smallest possible field size whenever possible, however, so
                     that the database uses less storage space and can be processed
                     more quickly.




                    You may lose existing data if you decrease the size of a field.
                     In addition, you cannot undo design changes after you have
                     saved the table.




   Procedures
          1. Open the desired table in Design view.
          2. Select the field for which you want to set the field size property.
          3. Select the General page in the Field Properties pane, if necessary.
          4. Select the Field Size property.
          5. Select the Field Size list, or type the desired value.
          6. Select the desired option, if applicable.




    Step-by-Step
          From the Student Data directory, open WORLD46.MDB.
          Limit the size of a field.

          Open the Customers table in Design view.



Page 96                                                                      Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships



           Steps                                           Practice Data
           1. Select the field for which you want to       Scroll as necessary and
              set the field size property.                 click in the Credit Limit
              The field is selected.                       field
           2. Select the General page in the Field         Click the General tab, if
              Properties pane.                             necessary
              The General page appears.
           3. Select the Field Size property.              Click in the Field Size
              A drop-down arrow appears in the             box
              Field Size box.
           4. Select the Field Size list or type the       Click Field Size
              desired value.
              A list of available options appears.
           5. Select the desired option, if applicable.    Click Double
              The option appears in the Field Size
              box.

          Save the changes to the table.

          Practice the Concept: Change the size of the Postal Code field to 8.

          Save the table; a Microsoft Access warning box will warn you that some data may be
          lost. Since there are currently no entries in that field with more than seven characters,
          you can select Yes. If there had been more than eight characters in any existing
          record, the extra characters would have been deleted.


SETTING NUMBER FORMATS

    Discussion
          The Format property affects how data appears in Datasheet view, not how it is stored
          in the table nor how it is used in calculations. If a Number data type field is formatted
          with 0 decimal places, for example, a value of 1.5 would appear as 2 in the datasheet;
          if the value is multiplied by 2 in a calculation, however, the answer would be 3, not 4.

          The available formats for fields with Number data types are listed in the following
          table:




Canadian Beaver (CBIT)                                                                     Page 97
Lesson 7 - Creating Relationships                                          Access 2003 - Lvl 1



           Format              Description
           General Number      Displays a number exactly as it is entered; this is
                               the default format
           Currency            Displays a dollar sign, a thousands separator, and
                               two decimal places; the defaults for this format are
                               determined by the system settings
           Euro                Displays a euro sign, a thousands separator, and
                               two decimal places; the defaults for this format are
                               determined by the system settings
           Fixed               Displays at least one digit and is rounded to the
                               default number of decimal places; the defaults for
                               this format are determined by the system settings
           Standard            Displays a thousands separator and is rounded to
                               the default number of decimal places; the defaults
                               for this format are determined by the system
                               settings
           Percent             Multiplies the number by 100, displays a percent
                               sign (%), and is rounded to the default number of
                               decimal places; the defaults for this format are
                               determined by the system settings
           Scientific          Expresses numbers in standard scientific notation
                               (as multiples of exponents of 10)




                                     Selecting a number format



Page 98                                                              Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 7 - Creating Relationships




                     You can change number defaults on your system by opening
                      the Control Panel and selecting the Regional Options icon.


                     The Format property list displays how a number will be
                      formatted for format types other than Text and Memo.




   Procedures
          1. Open the desired table in Design view.
          2. Select the field for which you want to set a number format.
          3. Select the General page in the Field Properties pane, if necessary.
          4. Select the Format property.
          5. Select the Format list.
          6. Select the desired format.




    Step-by-Step
          Set a number format.

          If necessary, open the Customers table in Design view.

           Steps                                          Practice Data
           1. Select the field for which you want to      Scroll as necessary and
              set a number format.                        click in the Credit Limit
              The field is selected.                      field
           2. Select the General page in the Field        Click the General tab, if
              Properties pane.                            necessary
              The General page appears.
           3. Select the Format property.                 Click in the Format box
              A drop-down arrow appears in the
              Format box.
           4. Select the Format list.                     Click Format
              A list of available formats appears.
           5. Select the desired format.            Click Currency
              The format appears in the Format box.

Canadian Beaver (CBIT)                                                                Page 99
Lesson 7 - Creating Relationships                                              Access 2003 - Lvl 1



           Save your changes; then, switch to Datasheet view. Scroll to the Credit Limit
           column. Notice that the values are now formatted as currency.

           Switch back to Design view.


SETTING DATE/TIME FORMATS

    Discussion
           You can also change the format of a Date/Time field to change the way the date or
           time appears in the table. The available formats for Date/Time fields are listed in the
           following table:

            Format             Description
            General Date       The default format; if the value is only a date, no time
                               appears; if the value is only a time, no date appears
            Long Date          The day and month names are spelled out (e.g.,
                               Tuesday, July 4, 1995)
            Medium Date        The month name is abbreviated, and the name of the
                               day is omitted (e.g., 04-Jul-95)
            Short Date         The date appears as numbers separated by slashes
                               (e.g., 7/4/95)
            Long Time          The time is displayed as hours, minutes, and seconds,
                               separated by colons, and followed by an AM or PM
                               indicator (e.g., 6:30:15 PM)
            Medium Time        The time is displayed the same as in the Long Time
                               format, except that no seconds appear (e.g., 06:15
                               PM)
            Short Time         The time is displayed as hours and minutes, separated
                               by a colon, in 24-hour clock format (e.g., 18:30)




                    You can change the date and time defaults for your system by
                     selecting the Regional Options icon from the Control Panel.




Page 100                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 7 - Creating Relationships




   Procedures
          1. Open the desired table in Design view.
          2. Select the field for which you want to set a date/time format.
          3. Select the General page in the Field Properties pane, if necessary.
          4. Select the Format property.
          5. Select the Format list.
          6. Select the desired format.




    Step-by-Step
          Set a date/time format.

          If necessary, open the Customers table in Design view.

           Steps                                         Practice Data
           1. Select the field for which you want to     Scroll as necessary and
              set a date/time format.                    click in the Contract Date
              The field is selected.                     field
           2. Select the General page in the Field       Click the General tab, if
              Properties pane.                           necessary
              The General page appears.
           2. Select the Format property.                Click in the Format box
              A drop-down arrow appears in the
              Format box.
           3. Select the Format list.                    Click Format
              A list of available formats appears.
           4. Select the desired format.            Click Medium Date
              The format appears in the Format box.

          Save the table and switch to Datasheet view. Scroll as necessary to the Contract
          Date field; notice its format.

          Switch back to Design view.




Canadian Beaver (CBIT)                                                                Page 101
Lesson 7 - Creating Relationships                                            Access 2003 - Lvl 1



SETTING YES/NO FORMATS

    Discussion
           A Yes/No field is limited to either a positive or a negative response and can be
           displayed as a text box, a check box, or a combo box. You select the display of the
           field on the Lookup page in the Field Properties pane.

           If the Yes/No field displays its values in a text box, you can select one of three
           Yes/No data type formats; True/False, Yes/No, and On/Off. Regardless of the format
           selected, the positive responses of True, Yes, and On are equivalent, just as the
           negative responses of False, No and Off are equivalent. Consequently, if the Yes/No
           field is set to the True/False format and a user enters Yes, Access automatically
           converts it to True.

           When a Yes/No field displays a check box, a selected check box indicates a positive
           response and a deselected check box indicates a negative response. The check box is
           the default setting for a Yes/No field.




                                   Selecting a Display Control property




                   If no format is set, an entry of Yes, True or On displays a
                    field value of -1, and an entry of No, False, or Off displays a
                    field value of 0.




Page 102                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 7 - Creating Relationships




   Procedures
          1. Open the desired table in Design view.
          2. Select the field for which you want to set a yes/no format.
          3. Select the General page in the Field Properties pane, if necessary.
          4. Select the Format property.
          5. Select the Format list.
          6. Select the desired yes/no format.
          7. Select the Lookup tab.
          8. Select the Display Control list.
          9. Select the desired yes/no control.




    Step-by-Step
          Set a yes/no format.

          If necessary, open the Customers table in Design view.

           Steps                                         Practice Data
           1. Select the field for which you want to     Scroll as necessary and
              set a yes/no format.                       click in the Catalog Sent
              The field is selected.                     field
           2. Select the General page in the Field       Click the General tab, if
              Properties pane.                           necessary
              The General page appears.
           3. Select the Format property.                Click in the Format box
              A drop-down arrow appears in the
              Format box.
           4. Select the Format list.                    Click Format
              A list of available formats appears.
           5. Select the desired yes/no format.     Click Yes/No
              The format appears in the Format box.
           6. Select the Lookup tab.                     Click the Lookup tab
              The Lookup page appears, with the
              Display Control box selected.



Canadian Beaver (CBIT)                                                               Page 103
Lesson 7 - Creating Relationships                                              Access 2003 - Lvl 1



            Steps                                           Practice Data
            7. Select the Display Control list.             Click Display Control
               A list of available options appears.
            8. Select the desired option.                   Click Text Box
               The option appears in the Display
               Control box.

           Save the table, and switch to Datasheet view. Scroll to the Catalog Sent field; notice
           the text values. Click in any field with a Yes value; notice that the actual stored value
           is -1.

           Click in any field with a No value; notice that the actual stored value is 0. Then,
           double-click the 0 value, type true, and press the [Down] key. Notice that the true
           entry changes to a Yes.

           Practice the Concept: Switch to Design view and display the Lookup tab. Change
           the Display Control property of the Catalog Sent field back to Check Box. Then,
           save the table.


SETTING DEFAULT VALUES

    Discussion
           When you set a default value for a field, that value automatically appears in the field
           for all new records. You can, however, modify the default field value as needed when
           entering a new record.

           A default value can save you time when entering data. For example, if a table stores
           the names and addresses of clients and most of the clients have addresses in New
           York, you can set the default value of the State field to NY. If you then enter a new
           record for a client in Connecticut, or if a client moves out of New York, you can
           change the value in the State field just for that individual record. Setting a default
           value for an established table, however, does not modify existing records.

           You can set a default value by entering the desired value or expression in the Default
           Value box. An expression consists of operators (i.e. =, +, -, *, /) and/or values.

           If you create a default value for a Text field, the default text must be enclosed in
           quotation marks (" "); for example, "Net 30". Values for Date fields must be
           enclosed in number signs (#); for example, #1/15/95#. If you do not enter the number
           signs, however, Access will automatically enter them.



                    You cannot set a default value for fields with AutoNumber or
                     OLE object data types.


Page 104                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships



                     You can also use the Expression Builder to create a default
                      value.




   Procedures
          1. Open the desired table in Design view.
          2. Select the field for which you want to set a default value.
          3. Select the General page in the Field Properties pane.
          4. Select the Default Value box.
          5. Create the desired default value.
          6. Press [Enter].




    Step-by-Step
          Set a default value.

          If necessary, open the Customers table in Design view.

           Steps                                           Practice Data
           1. Select the field for which you want to       Scroll as necessary and
              set a default value.                         click in the Catalog Sent
              The field is selected.                       field
           2. Select the General page in the Field         Click the General tab
              Properties pane.
              The General page appears.
           3. Select the Default Value property.           Click in the Default
              The insertion point appears in the           Value box
              Default Value box, and the Build
              button appears.
           4. Type the desired default value.              Type yes
              The text appears in the Default Value
              box.
           5. Press [Enter].                               Press [Enter]
              The default value is saved.




Canadian Beaver (CBIT)                                                                 Page 105
Lesson 7 - Creating Relationships                                                Access 2003 - Lvl 1


           Save the table and switch to Datasheet view. Scroll to display the Catalog Sent
           column in the new record row; notice that the Catalog Sent field for the new record is
           checked.

           Then, switch back to Design view.


SETTING VALIDATION RULES

    Discussion
           Databases can contain incorrect information due to data entry errors. One method of
           controlling the accuracy of data is to impose restrictions on the values entered into a
           field.

           You can impose restrictions on data entered into your table by creating validation
           rules in the Validation Rule box of one or more fields. When you enter data into a
           new record or modify data in an existing record, Access checks each field for existing
           validation rules. If an entry does not meet the conditions of the corresponding
           validation rule, a warning box notifies you of the error. When you set a validation
           rule, you use the Validation Text box to specify the text you want to appear in the
           warning box.

           Setting a validation rule is different from setting the data type or field size properties.
           Access uses data type and field size properties to determine that the correct type of
           data is being entered. Validation rules are more specific in their restrictions. For
           example, you can create a validation rule to ensure that numbers entered in a
           particular field are between 500 and 1000.




                                Creating a validation rule with validation text




Page 106                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships



                     You can also use the Expression Builder to set a validation
                      rule.




   Procedures
          1. Open the desired table in Design view.
          2. Select the field for which you want to set a validation rule.
          3. Select the General page in the Field Properties pane, if necessary.
          4. Select the Validation Rule box.
          5. Enter the desired validation rule.
          6. Select the Validation Text box.
          7. Enter the desired validation text.




    Step-by-Step
          Set a validation rule.

          If necessary, open the Customers table in Design view.

           Steps                                           Practice Data
           1. Select the field for which you want to       Scroll as necessary and
              set a validation rule.                       click in the Credit Limit
              The field is selected.                       field
           2. Select the General page in the Field         Click the General tab, if
              Properties pane.                             necessary
              The General page appears.
           3. Select the Validation Rule property.         Click in the Validation
              The insertion point appears in the           Rule box
              Validation Rule box, and the Build
              button appears.
           4. Enter the desired validation rule.           Type <=10000
              The text appears in the Validation
              Rule box.




Canadian Beaver (CBIT)                                                                 Page 107
Lesson 7 - Creating Relationships                                            Access 2003 - Lvl 1



            Steps                                          Practice Data
            5. Select the Validation Text box.             Click in the Validation
               The insertion point appears in the          Text box
               Validation Text box.
            6. Enter the desired validation text.          Type The Credit Limit
               The text appears in the Validation          may not exceed $10,000.
               Text box.

           Save the table; a Microsoft Access warning box informs you that data integrity rules
           have been changed. Select Yes.

           Switch back to Datasheet view. Create a new record; scroll as necessary, enter 15000
           in the Credit Limit field, and press [Enter]. Select OK.

           Press [Esc] twice to delete the new record and switch back to Design view.


CREATING AN INPUT MASK - WIZARD

    Discussion
           Input masks control how data is entered into a table, as well as the format in which it
           is stored. You can use an input mask to control how many characters are entered into a
           field; define each individual character as numeric, text, or either; and specify each
           individual character as mandatory or optional, as well as add literal characters to
           format the entry. Since the input mask controls the values users can enter into a field,
           it often makes data entry easier.

           You can create an input mask by entering the criteria directly into the Input Mask
           box. However, it is often easier to use the Input Mask Wizard to set the property for
           you. The Input Mask Wizard offers several predefined input masks for items such as
           dates and times, ZIP codes, telephone numbers, etc. In addition, you can modify any
           predefined input mask to meet your needs.




Page 108                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships




                                           The Input Mask Wizard




                     You can get help on creating or modifying input masks by
                      selecting the Input Mask box and pressing the [F1] key.


                     An input mask only affects new entries; data that has already
                      been entered into a field is not affected if an input mask is
                      later.




   Procedures
          1. Open the desired table in Design view.
          2. Select the field to which you want to apply an input mask.
          3. Select the General page in the Field Properties pane, if necessary.
          4. Select the Input Mask property.
          5. Click the Build button.
          6. Select the desired option from the Input Mask list.
          7. Select Next.
          8. Select the Placeholder Character list.
          9. Select the desired placeholder.
          10. Select Next.


Canadian Beaver (CBIT)                                                                Page 109
Lesson 7 - Creating Relationships                                              Access 2003 - Lvl 1


           11. Select the desired option for storing the data.
           12. Select Next.
           13. Select Finish.




    Step-by-Step
           Create an input mask.

           If necessary, open the Customers table in Design view.

            Steps                                           Practice Data
            1. Select the field to which you want to        Scroll as necessary and
               apply an input mask.                         click in the Phone
               The field is selected.                       Number field
            2. Select the General page in the Field         Click the General tab, if
               Properties pane.                             necessary
               The General page appears.
            3. Select the Input Mask property.              Click in the Input Mask
               The Build button appears to the right        box
               of the Input Mask box.
            4. Click the Build button.                      Click
               The Input Mask Wizard opens.
            5. Select the desired option from the           Click Phone Number, if
               Input Mask list.                             necessary
               The input mask option is selected.
            6. Select Next.                                 Click Next >
               The next page of the Input Mask
               Wizard appears.
            7. Select the Placeholder character list.       Click Placeholder
               A list of available placeholders             character
               appears.
            8. Select the desired placeholder.              Click _ , if necessary
               The placeholder appears in the
               Placeholder character box.
            9. Select Next.                                 Click Next >
               The next page of the Input Mask
               Wizard appears.




Page 110                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships



           Steps                                            Practice Data
           10 Select the desired option for storing         Click Without the
              the data.                                     symbols in the mask, like
              The option is selected.                       this, if necessary
           11. Select Next.                                 Click Next >
               The next page of the Input Mask
               Wizard appears.
           12. Select Finish.                               Click Finish
               The Input Wizard closes, and the input
               mask appears in the Input Mask box.

          Save the table and switch to Datasheet view. Create a new record and tab to the
          Phone Number field; type 1234567890; notice that the input mask controls how the
          number is entered, as well as how it is formatted.

          Press [Esc] twice to delete the new record and close the Customers table.


CREATING AN INPUT MASK MANUALLY

    Discussion
          An input mask controls what values you can enter in a field, as well as how the data
          will appear. Although it is usually easier to use the Input Mask Wizard, you may need
          to create an input mask that is not included in the list of predefined masks in the Input
          Mask Wizard. Access allows you to create an input mask by entering criteria directly
          into the Input Mask box.

          When you create an input mask manually, you use special characters to define it.
          These special characters act as placeholders, controlling the type of character that can
          be entered into each position, as well as which characters are required. For example,
          the (999) 000-0000 input mask allows you to enter only digits, and the area code is not
          required; consequently, both ( ) 555-6545 and (804) 555-6545 are valid entries for this
          input mask.

          The following table defines some of the special characters that can be used in an input
          mask. To define a literal character, enter any character other than one of those shown
          in the table. If you want to define a character listed in the table as a literal character,
          you must precede that character with a backslash (\).




Canadian Beaver (CBIT)                                                                     Page 111
Lesson 7 - Creating Relationships                                            Access 2003 - Lvl 1



           Character Description
                0          A required numeric entry (0-9); plus and minus signs are
                           not allowed
                9          An optional numeric entry (0-9) or space; plus and minus
                           signs not allowed
                #          An optional numeric (0-9) entry or space; plus and minus
                           signs allowed
                L          A required alphabetic entry (A-Z)
                ?          An optional alphabetic entry (A-Z)
                A          A required alphabetic (A-Z) or numeric (0-9) entry
                a          An optional alphabetic (A-Z) or numeric (0-9) entry
                &          Any character or space; entry required
                C          Any character or space; entry optional
                <          Causes all characters that follow to be converted to
                           lowercase
                <          Causes all characters that follow to be converted to
                           uppercase
                !          Enters the input mask from right to left, instead of from
                           left to right
                 \         Causes the character that follows to be displayed as a
                           literal character




                    The Input Mask property can be set in Design view of any
                     table, query, or form. Most of the time, you will want to apply
                     an input mask to a field in table Design view, because it will
                     then be automatically applied to the field in queries, forms,
                     and reports.


                    The Input Mask Wizard can only be used for Text and
                     Date/Time fields. You must manually enter an input mask for
                     Number and Currency fields.




Page 112                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships




   Procedures
          1. Open the desired table in Design view.
          2. Select the field to which you want to apply an input mask.
          3. Select the General page in the Field Properties pane, if necessary.
          4. Select the Input Mask box.
          5. Type the desired input mask.




    Step-by-Step
          Create an input mask manually.

          If necessary, open the Reps table in Design view.

           Steps                                         Practice Data
           1. Select the field to which you want to      Click in the INITIALS
              apply an input mask.                       field, if necessary
              The field is selected.
           2. Select the General page in the Field       Click the General tab, if
              Properties pane.                           necessary
              The General page appears.
           3. Select the Input Mask box.                 Click in the Input Mask
              The insertion point appears in the         box
              Input Mask box.
           4. Type the desired input mask.               Type >LLL
              The text appears in the Input Mask
              box.

          Save the changes to the table and switch to Datasheet view. Create a new record,
          select the INITIALS field (if necessary), type abc, and press [Enter]; notice that the
          completed entry is formatted as all caps.

          Press [Esc] to delete the new record and close the Reps table.




Canadian Beaver (CBIT)                                                                 Page 113
Lesson 7 - Creating Relationships                                             Access 2003 - Lvl 1



CREATING A CUSTOM INPUT MASK

    Discussion
           The Input Mask Wizard provides a list of predefined input masks. If you frequently
           use an input mask that is not included with the predefined masks in the Input Mask
           Wizard, however, you may want to create a custom input mask. You may want to use
           an input mask, for example, to ensure that product numbers are always correctly
           entered.

           You can create and save a custom input mask in the Input Mask Wizard, where it is
           available to tables and forms at any time. In this way, you will only need to create a
           custom input mask once.




                                        Creating a custom input mask




                    Input masks are saved to your user profile on your system, not
                     to the individual database file. If you create a new mask, or
                     modify a default one using the Customize Input Mask Wizard
                     dialog box, the changes are permanently saved to your system
                     settings.




   Procedures
           1. Open the desired table in Design view.
           2. Select the field to which you want to apply a custom input mask.
           3. Select the General page in the Field Properties pane.



Page 114                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                       Lesson 7 - Creating Relationships


          4. Select the Input Mask box.

          5. Click the Build button     .
          6. Select Edit List.

          7. Select the New button      .
          8. Type the desired description for the custom input mask.
          9. Select the Input Mask box.
          10. Type the custom input mask.
          11. Select the Placeholder box.
          12. Type the desired placeholder.
          13. Select the Sample Data box.
          14. Type some sample data.
          15. Select Close.
          16. Select the custom input mask from the Input Mask list
          17. Select Finish.




    Step-by-Step
          Create a custom input mask.

          Open the Items table in Design view.

           Steps                                        Practice Data
           1. Select the field to which you want        Click in the Product ID
              apply a custom input mask.                field, if necessary
              The field is selected
           2. Select the General page in the Field      Click the General tab, if
              Properties pane.                          necessary
              The General page appears.
           3. Select the Input Mask box.                Click in the Input Mask
              The Build button appears to the right     box
              of the Input Mask box.
           4. Click the Build button.                   Click
              The Input Mask Wizard opens.




Canadian Beaver (CBIT)                                                              Page 115
Lesson 7 - Creating Relationships                                         Access 2003 - Lvl 1



           Steps                                       Practice Data
           5. Select Edit List.                        Click Edit List
              The Customize Input Mask Wizard
              dialog box appears.
           6. Select the New button.                   Click
              A blank form appears.
           7. Type the desired description for the     Type Product Number
              custom input mask.
              The text appears in the Description
              box.
           8. Select the Input Mask box.               Press [Tab]
              The insertion point appears in the
              Input Mask box.
           9. Type the custom input mask.              Type !00-0000
              The text appears in the Input Mask
              box.
           10. Select the Placeholder box.             Press [Tab]
               The insertion point appears in the
               Placeholder box.
           11. Type the desired placeholder.           Type _
               The character appears in the
               Placeholder box.
           12. Select the Sample Data box.             Press [Tab]
               The insertion point appears in the
               Sample Data box.
           13. Type some sample data.                  Type 346278
               The characters appear in the Sample
               Data box.
           14. Select Close.                           Click Close
               The Customize Input Mask Wizard
               dialog box closes, and the custom
               input mask appears in the Input Mask
               list.
           15. Select the custom input mask from the   Scroll as necessary and
               Input Mask list.                        click Product Number
               The custom input mask is selected.
           16. Select Finish.                          Click Finish
               The Input Mask Wizard dialog box
               closes, and the custom input mask
               appears in the Input Mask box.



Page 116                                                              Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                             Lesson 7 - Creating Relationships


          Save the changes to the table and switch to Datasheet view. Create a new record, type
          153434 into the Product ID field, and press [Enter]; notice the custom input mask.

          Press [Esc] to delete the new record and close the Items table.


TYPING A LOOKUP LIST

    Discussion
          You can increase data accuracy by adding a lookup field to a table. The user can then
          select the desired value from the lookup list, rather than having to type it. The values
          in a lookup list are usually stored in a field in a different table. If you want the initials
          of the sales rep to appear in the Sales Rep field of the Customer table, for example,
          you can create a lookup to the Initials field in the Reps table. If the desired data is not
          stored elsewhere, you can type the lookup list values.

          The Lookup Wizard creates a combo box field for the lookup list; a combo box field
          displays a drop-down list that displays all of the available data values. By default, a
          lookup field does not limit input to values on the lookup list; the user can type an
          entry not on the list if desired. However, you can limit a field to only those values on
          the lookup list.

          A lookup list can contain multiple columns. If you are typing a new list of values, you
          can designate the number of columns desired and then type the desired values into
          each column. In addition, you can add a second field to a lookup list. For example, if
          you are creating a lookup list in the Customer table, you can add both the Initials and
          the Last Name fields from the Reps table to the lookup list.




                                             Typing a lookup list




Canadian Beaver (CBIT)                                                                      Page 117
Lesson 7 - Creating Relationships                                                 Access 2003 - Lvl 1



                    If you are creating a lookup list for a field that is the related
                     field in a join, you must delete the join before using the
                     Lookup Wizard.




   Procedures
           1. Open the desired table in Design view.
           2. Click in the Data Type column of the field for which you want to
              create a lookup list.
           3. Select the Data Type arrow.
           4. Select Lookup Wizard....
           5. Select the desired lookup source.
           6. Select Next.
           7. Enter the desired number of lookup columns.
           8. Select the box below the Col1 heading.
           9. Type the first desired lookup value.
           10. Enter additional lookup values as desired.
           11. Select Next.
           12. Select the column that contains the values you want to store or use.
           13. Select Finish.




    Step-by-Step
           Create a lookup list.

           Open the Orders table in Design view.

            Steps                                             Practice Data
            1. Click in the Data Type column of the           Click in the Data Type
               field for which you want to create a           column of the Shipping
               lookup list.                                   Method field
               A drop-down arrow appears in the
               Data Type column.



Page 118                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 7 - Creating Relationships



           Steps                                         Practice Data
           2. Select the Data Type arrow.                Click Text
              A list of available data types appears.
           3. Select Lookup Wizard....                   Click Lookup Wizard...
              The Lookup Wizard opens.
           4. Select the desired lookup source.          Click I will type in the
              The option is selected.                    values that I want.
           5. Select Next.                               Click Next >
              The next page of the Lookup Wizard
              appears, with the Number of columns
              box selected.
           6. Enter the desired number of lookup         Type 2
              columns.
              The number appears in the Number of
              columns box.
           7. Select the box below the Col1              Press [Tab]
              heading.
              The insertion point appears in the first
              row of the Col1 column, and the
              specified number of columns appears.
           8. Type the first desired lookup value.       Type 1
              The text appears in the first Col1 box.
           9. Enter additional lookup values as          Follow the instructions
              desired.                                   shown below the table
              The text appears in the columns.           before continuing on to
                                                         the next step
           10. Select Next.                              Click Next >
               The next page of the Lookup Wizard
               appears.
           11. Select the column that contains the       Click Col1, if necessary
               values you want to store or use.
               The column is selected.
           12. Select Finish.                            Click Finish
               The Lookup Wizard warning box
               opens prompting you to save the table.

          Type the following values into the lookup list, pressing the [Tab] key to move to the
          next column or row as needed:

                            Col1               Col2
                             1                 UPS
                             2                 DHL

Canadian Beaver (CBIT)                                                                 Page 119
Lesson 7 - Creating Relationships                                             Access 2003 - Lvl 1


                                3               FedEx
                                4              US Mail
                                5               Emery

           Return to the table and continue on to the next step (step 10).

           Save the table and switch to Datasheet view. Select the Shipping Method field for
           the first record; then, select 1 UPS from the lookup list. Press [Down], type 1 into the
           Shipping Method field of the second record, and press [Down] again. For the third
           record, type 2 and press [Down]. For the fourth record, type 3 and press [Enter].

           Switch back to Design view.


MODIFYING LOOKUP PROPERTIES

    Discussion
           You can use the Lookup page in the Field Properties pane to modify the properties of
           a lookup list.

           If the lookup list was manually typed, you can use the Row Source property to edit it
           or add to it. Column values are separated by semi-colons (;) and text entries are
           enclosed in quotation marks (").

           The Bound Column property indicates which lookup column is used to store field
           values. For instance, if the lookup list consists of two columns, and column 1 is the
           bound column, the data in column 1 will be stored in the lookup field.

           Although you may want the values in the bound column to be stored in the field, they
           may be confusing and uninformative to the user; the descriptive text in an unbound
           field may have more meaning. You can suppress the display of the bound column and
           display only the descriptive text in the unbound column of a lookup list by changing
           the width of the bound column to zero (0”) in the Column Width box.

           By default, a lookup field allows the user to add data values to the lookup list simply
           by typing them into the field. You can limit data entry to just those values on the
           lookup list by selecting Yes in the Limit to List box; the default value of No allows
           free entry.




   Procedures
           1. Open the desired table in Design view.
           2. Select the lookup field you want to modify.



Page 120                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships


          3. Select the Lookup tab in the Field Properties pane.
          4. Select the property you want to modify.
          5. Modify the property as desired




    Step-by-Step
          Modify Lookup properties.

          If necessary, open the Orders table in Design view.

           Steps                                           Practice Data
           1. Select the lookup field you want to          Click in the Shipping
              modify.                                      Method field, if necessary
              The field is selected.
           2. Select the Lookup tab in the Field           Click the Lookup tab, if
              Properties pane.                             necessary
              The Lookup page appears.
           3. Select the property you want to              Click in the Column
              modify.                                      Widths box
              The insertion point appears in the
              corresponding property box.


           4. Modify the property as desired.              Follow the instructions
              The Lookup properties are modified           below to complete this
              accordingly.                                 step



          Change the text in the Column Widths property to 0”;1”. Then, change the Limit To
          List property to Yes.

          Save the table and switch to Datasheet view. Notice that all entries in the Shipping
          Method field now display the text values. Select the next record in which the
          Shipping Method field is blank and select Emery from the lookup list. Press
          [Down], type the letter u, and press [Down] again. Notice that Access completes the
          entry.

          Type Post Office, and press [Down]; a Microsoft Access message box informs you
          that the text entered is not on the list. Select OK and press [Esc] twice.

          Although text values appear in the field, the data actually stored in it is the numeric
          values in the bound column. Right-click any field in the Shipping Method column,
          click in the Filter For box, type UPS, and press [Enter]. Notice that no records are


Canadian Beaver (CBIT)                                                                   Page 121
Lesson 7 - Creating Relationships                                             Access 2003 - Lvl 1


           found; even though UPS appears in several fields in the datasheet, it is not the actual
           data stored in the field. Click the Remove Filter button to display all records.

           Now right-click any field in the Shipping Method column, click in the Filter For
           box, type 1, and press [Enter]. The filter now works, because you filtered for data
           actually stored in the field. Click the Remove Filter button to display all records.

           Save the Orders table and close it.
           Close WORLD46.MDB.




Page 122                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 7 - Creating Relationships




EXERCISE
SETTING FIELD PROPERTIES

      Task
          Set field properties.

          1. Open Train46x.
          2. Open the Payment table in Design view.
          3. Set the Format property for the Payment Date field to Short Date.
          4. Set the Format property for the Amount Paid field to Currency.
          5. Set a validation rule for the Amount Paid field, so that it can only
             contain values greater than ten dollars. (Hint: Type >10.) Then, enter
             the following validation text: The amount paid must be greater
             than ten dollars.
          6. Set the Format property for the Recorded field to Yes/No.
          7. Set a default value of “CHECK” for the Type of Payment field.
          8. Switch to Datasheet view, saving the changes. Select Yes to all
             Microsoft Access warning boxes. Notice the formats of the Payment
             Date and Amount Paid fields. Scroll to the new record row; notice
             that the default value of CHECK appears in the Type of Payment
             field. Create a new record with a Project ID of 11, a Payment Date
             of 4/1/01, and an Amount Paid of 5. Select OK when prompted
             with the validation text and replace the 5 with 25 in the Amount
             Paid field. Then, close the Payment table.
          9. Open the Reps table in Design view.
          10. Select the Initials field and create the following input mask: >LL.
              (This mask will require the data to be 2 letters and will automatically
              format them as upper-case.)
          11. Switch to Datasheet view, saving the changes. Then, add the
              following new record:

                           Initials
                           pj

              (Notice that Access automatically capitalizes the entry in the Initials
              field.)



Canadian Beaver (CBIT)                                                                  Page 123
Lesson 7 - Creating Relationships                                           Access 2003 - Lvl 1


           12. Switch to Design view and use the Input Mask Wizard to apply the
               Phone Number mask to the Home Phone field. Then, save the
               table.
           13. Select the Zip field and open the Input Mask Wizard. Add a new
               input mask named Short Zip. (Hint: Use the Edit List button and
               create a new mask using the New Record button.) Type the
               following input mask: !00000 with a sample data zip code of 11111.
               Then, apply the Short Zip input mask to the Zip field.
           14. Switch to Datasheet view, saving the changes. Tab to the Zip field
               for the first record row and type 12345. Notice the input mask.
           15. Tab to the Home Phone field, type 1234567890, and press [Enter];
               notice that the phone number is automatically formatted according to
               the input mask. Close the Reps table.
           16. Open the Project table in Design view. Create a new field below the
               Trainer Initials field; name it Sales Rep and make it a lookup field.
               (Hint: Select Lookup Wizard from the Data Type list.) Select the
               option to type in the values. Then, type the following values for the
               lookup column: PJ, RJ, KM, and SH. Finish the Lookup Wizard.
           17. Switch to Datasheet view, saving the changes. Add the following
               values to the Sales Rep column for the first four records:
                   Project ID              Sales Rep
                   1                       RJ
                   2                       SH
                   3                       PJ
                   4                       KM
           18. Switch back to Design view. Add a new sales rep to the Sales Rep
               value list by typing ;"JW" after the last entry in the Row Source
               box. Then, change the value in the List Rows box to 5. (Hint: Use
               the Lookup tab.)
           19. Switch to Datasheet view, saving the changes. Select the first empty
               Sales Rep field (in the Project ID 5 row) and select JW from the
               drop-down list.




Page 124                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                      Lesson 7 - Creating Relationships




          20. Close the Project table.
          21. Close the database file.




Canadian Beaver (CBIT)                                           Page 125
Lesson 7 - Creating Relationships                           Access 2003 - Lvl 1




     LESSON 7 -
     CREATING RELATIONSHIPS

     In this lesson, you will learn how to:
                Use related tables

                Create a relationship between tables

                Set referential integrity

                View subdatasheets

                Delete a join line




Page 126                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 7 - Creating Relationships




USING RELATED TABLES

    Discussion
          Tables can be joined, or related, in order to access and coordinate information in all
          the fields of the related tables. Joining tables is a useful way to avoid entering
          duplicate information in various, related tables. In addition, it allows you to create
          reports, forms, and queries from the related data tables and save them in the database
          file. Relating tables allows you to create smaller, more efficient tables that can be
          referenced when you need access to the data.

          When you relate tables, the table from which you select a field to join is the primary
          table, and the second table containing the field you want to associate is the related
          table. The tables must have at least one common field that contains the same type of
          data. This common field is called the join field. The join fields in both tables must
          have the same or equivalent data types and, if they are Number fields, they must have
          the same field size. In addition, the join field in the primary table must be the primary
          key in order to avoid duplicate entries.

          For example, the following table consists of customer names and address fields, along
          with a unique identification number for each customer, which serves as the primary
          key in the table. You can create this number or allow Access to create it for you.

           ID # Names                 Address                City         State      Zip

             1     Smith       11692 J St. NW            Washington      DC        20013
             2     Conrad      16 Allegheny Center       Pittsburgh      PA        16489
             3     Kane        1012 Broadway             Lexington       KY        40567
             4     Apple       516 Beacon Ave.           Seattle         WA        98051
             5     Billow      9249 Cavalcade St.        Houston         TX        77002

          You could then create a separate table consisting only of orders placed by customers.
          This table would also contain the field for the unique customer identification number,
          but not the customers’ names and addresses.




Canadian Beaver (CBIT)                                                                     Page 127
Lesson 7 - Creating Relationships                                           Access 2003 - Lvl 1



                         ID # Order
                           1     Office Work Center
                           2     All-In-One Corner Desk and Hutch
                           3     Open-Front Steel Bookcase
                           4     2-Drawer Letter File
                           5     Executive Leather Chair, Black

           By relating the two tables through the common customer identification number field,
           the customers’ name and address does not have to be entered for every order. All that
           has to be entered is the customer identification number, along with the orders.

           Access includes two basic types of relationships: one-to-many and one-to-one. A one-
           to-many relationship occurs when one record from the primary table matches many
           records from the related table (e.g., one customer record matches many order records).
           A one-to-one relationship occurs when one record from the primary table matches one
           record from the related table. Access determines the relationship type automatically
           when you create the relationship.



CREATING A RELATIONSHIP BETWEEN TABLES

    Discussion
           The Relationships window displays a graphic representation of database relationships
           and allows you to create relationships between tables. You drag field lists in the
           Relationships window to reposition them as needed. In addition, any field name that
           represents a primary key is bolded in the corresponding field list.

           You can add tables to the Relationships window for additional joins. For example, if
           the Relationships window displays only two related tables and you need to access
           information from a third table, you can easily add the required table to the
           Relationships window and then create the join.




Page 128                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships




                                Adding a field list to the Relationships window




                     The Show Table dialog box opens automatically if no tables
                      have been added to the Relationships window.


                     You can also open the Relationships window by selecting the
                      Tools menu and the Relationships command or by right-
                      clicking in the Database window and selecting the
                      Relationships command.


                     You can select multiple tables in the Show Table dialog box
                      by holding the [Ctrl] key as you click each table. Selecting
                      Add adds all the selected tables to the Relationships window.




   Procedures
          1. Click the Relationships button           on the Database toolbar.


          2. Click the Show Table button            on the Relationship toolbar, if
             necessary.
          3. Select the first table you want to relate.
          4. Select Add.



Canadian Beaver (CBIT)                                                                Page 129
Lesson 7 - Creating Relationships                                               Access 2003 - Lvl 1


           5. Select the second table you want to relate.
           6. Select Add.
           7. Select Close.
           8. Drag the desired field from the first field list to the matching field in
              the second field list.
           9. Select Create.
           10. Close the Relationships window.
           11. Select Yes.




    Step-by-Step
           From the Student Data directory, open WORLD08.MDB.
           Create a relationship between two tables.

            Steps                                           Practice Data
            1. Click the Relationships button on the
               Database toolbar.                            Click
               The Relationships window opens.
            2. Click the Show Table button on the
               Relationship toolbar, if necessary.          Click
               The Show Table dialog box opens.
            3. Select the first table you want to relate.   Click Customers, if
               The table name is selected.                  necessary
            4. Select Add.                                  Click Add
               The corresponding field list appears in
               the Relationships window.
            5. Select the second table you want to          Click Orders
               relate.
               The table name is selected.
            6. Select Add.                                  Click Add
               The corresponding field list appears in
               the Relationships window.
            7. Select Close.                                Click Close
               The Show Table dialog box closes.




Page 130                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 7 - Creating Relationships



           Steps                                            Practice Data
           8. Drag the desired field from the first         Drag the Customer
              field list to the matching field in the       Number field in the
              second field list.                            Customers field list to the
              The Edit Relationships dialog box             Customer ID field in the
              opens when you release the mouse              Orders field list
              button with the related fields as well as
              the type of relationship displayed.
           9. Select Create.                                Click Create
              The Edit Relationships dialog box
              closes, and a join line appears
              between the related fields.
           10. Close the Relationships window.              Click    on the
               A Microsoft Access warning box               Relationships window title
               opens, asking if you want to save the        bar
               changes to the layout.
           11. Select Yes.                                  Click Yes
               The Microsoft Access warning box and
               the Relationships window close, and
               the tables are related.

          Practice the Concept: Add the Line Items table to the Relationships window. Drag
          the Line Items field list to the right of the Orders field list, if desired. Then, join the
          Order Number field in the Orders field list to the Order Number field in the Line
          Items field list.

          Close the Relationships window and save the changes.


SETTING REFERENTIAL INTEGRITY

    Discussion
          When you create a relationship between two tables, you can set referential integrity.
          Referential integrity is a built-in set of rules Access uses to make sure that the
          relationship is valid. Referential integrity can also prevent accidental deletion or
          editing of data. In order to use referential integrity, the following conditions must be
          true: the related field in one table must be its primary key, the related fields in both
          tables must have the same data type, and both tables must belong to the same
          database.

          When you set referential integrity, you must observe the following three rules; First,
          you cannot enter data in the join field in the "many" table that does not have a match
          in the join field in the "one" table. Second, you cannot delete records from the "one"



Canadian Beaver (CBIT)                                                                      Page 131
Lesson 7 - Creating Relationships                                            Access 2003 - Lvl 1


           table if there are matching records in the "many" table. Third, you cannot edit primary
           key values if related records exist.

           If you want to perform any of the changes listed above, however, and still maintain
           referential integrity, you can select the Cascade Update Related Fields and Cascade
           Delete Related Records options in the Edit Relationships dialog box. If either or both
           of these options are selected, Access automatically makes the necessary changes to
           related tables to maintain referential integrity. It is recommended that these two
           options be selected only after careful consideration, since the changes cannot be
           undone.

           When referential integrity is enforced, Access displays symbols above the join line to
           indicate the type of relationship, one-to-one or one-to-many. The number 1 above a
           join line indicates “one”, and the mathematical symbol for infinity (which resembles a
           horizontal 8) indicates “many”.




                                         Setting referential integrity




                   Double-clicking the middle segment of any join line opens the
                    Edit Relationships window with the selected join displayed.
                    Double-clicking the beginning or ending segment of any join
                    line opens the Edit Relationships window, but with no join
                    selected. You can then use the Table/Query list to select the
                    desired join.


                   You can also open the Edit Relationships dialog box by
                    selecting the Relationships menu and the Edit Relationship
                    command or by right-clicking the join line and then selecting
                    the Edit Relationship command.




Page 132                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 7 - Creating Relationships




   Procedures
          1. Open the Relationships window.
          2. Double-click the middle segment of the desired join line.
          3. Select the Enforce Referential Integrity option.
          4. Select the Cascade Update Related Fields option, if desired.
          5. Select the Cascade Delete Related Records option, if desired.
          6. Select OK.




    Step-by-Step
          Set referential integrity for the relationship between two tables.

          Open the Relationships window.

           Steps                                           Practice Data
           1. Double-click the middle segment of           Double-click the middle
              the desired join line.                       segment of the join line
              The Edit Relationships dialog box            between the Customer
              opens with the selected join displayed.      Number and Customer
                                                           ID fields
           2. Select the Enforce Referential               Click Enforce
              Integrity option.                            Referential Integrity
              The Enforce Referential Integrity
              option is selected.
           3. Select OK.                                   Click OK
              The Edit Relationships dialog box
              closes, and referential integrity is
              applied to the join.

          Practice the Concept: Set referential integrity for the join between the Order
          Number fields. Then, close the Relationships window.




Canadian Beaver (CBIT)                                                                Page 133
Lesson 7 - Creating Relationships                                              Access 2003 - Lvl 1



VIEWING SUBDATASHEETS

    Discussion
           Subdatasheets are datasheets nested in the primary table and display the data in joined
           tables. When you join tables in a one-to-many relationship, Access creates
           subdatasheets in the table containing the primary key.

           When tables are joined, Access inserts a column containing plus signs to the left of the
           first field in the primary table. Clicking the plus sign expands a subdatasheet with the
           data in the related table displayed. If the table contains additional joins, you can
           expand subdatasheets to display each related table. Access can display up to eight
           levels of subdatasheets.

           In addition to viewing data, you can edit data right in the subdatasheet; the edited data
           is saved back to the table in which it is stored.




                                          Displaying subdatasheets




                    Access can also create subdatasheets for one-to-one
                     relationships.


                    If Access cannot determine which subdatasheet to display, the
                     Insert Subdatasheet dialog box opens so that you can select the
                     table you want to view. In addition, you can select a different
                     subdatasheet from the Insert Subdatasheet dialog box at any
                     time by selecting the Insert menu and the Subdatasheet
                     command.

Page 134                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 7 - Creating Relationships




   Procedures
          1. Open the primary table in Datasheet view.
          2. To display a subdatasheet, click the plus sign next to the record you
             want to expand.
          3. Display additional subdatasheets, if available.
          4. Click the minus sign next to any subdatasheet you want to collapse.




    Step-by-Step
          View subdatasheets.

          Open the Customers table in Datasheet view.

           Steps                                         Practice Data
           1. To display a subdatasheet, click the       Click      1014
              plus sign next to the record you want
              to expand.
              The subdatasheet appears.
           2. Display additional subdatasheets, if       Click 1711 in the
              available.                                 subdatasheet
              Additional subdatasheets appear.
           3. Click the minus sign next to any           Click 1711 in the
              subdatasheet you want to collapse.         subdatasheet
              The subdatasheet disappears.

          Practice the Concept: Change the sales representative for the 1711 order to NTB.
          Then, collapse the 1014 record in the Customers table to collapse the subdatasheet.

          Close the Customers table and open the Orders table in Datasheet view. Scroll
          down as necessary to view order number 1711. Notice that the sales representative for
          order number 1711 is now NTB. Close the Orders table.




Canadian Beaver (CBIT)                                                                 Page 135
Lesson 7 - Creating Relationships                                             Access 2003 - Lvl 1



DELETING A JOIN LINE

    Discussion
           Deleting a join line removes the relationship between two tables. You may want to
           delete a join line if you no longer need to relate the tables or you want to create a
           different relationship.

           You must select a join line before you can delete it.



                    You must click the middle segment of a join line in order to
                     select it; clicking the beginning or ending segment does not
                     select the join line.


                    You can also delete a join line by right-clicking its middle
                     segment and selecting the Delete command.


                    You can remove an unrelated field list from the Relationship
                     window by selecting it and pressing the [Delete] key.




   Procedures
           1. Open the Relationships window.
           2. Click the middle segment of the join line you want to delete.
           3. Press [Delete].
           4. Select Yes.




    Step-by-Step
           Delete a join line.

           If necessary, open the Relationships window.




Page 136                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 7 - Creating Relationships



           Steps                                         Practice Data
           1. Click the middle segment of the join       Click the middle segment
              line you want to delete.                   of the join line between
              The join line is bolded.                   the Customers and
                                                         Orders field lists
           2. Press [Delete].                            Press [Delete]
              A Microsoft Access warning box
              opens, prompting you to confirm the
              deletion.
           3. Select Yes.                                Click Yes
              The Microsoft Access warning box
              closes, and the join line is deleted.

          Practice the Concept: Delete the join line between the Orders and Line Items field
          lists. Then, remove all field lists from the Relationships window by selecting them and
          pressing [Delete], since none of them are related.

          Close the Relationships window and save the changes.
          Close WORLD08.MDB.




Canadian Beaver (CBIT)                                                                 Page 137
Lesson 7 - Creating Relationships                                               Access 2003 - Lvl 1




EXERCISE
CREATING RELATIONSHIPS

      Task
           Create relationships between tables and set referential integrity.

           1. Open Train08x.
           2. Open the Relationships window.
           3. Add the Client and Project tables to the Relationships window.
           4. Create a relationship between the Client ID field in the Client table
              and the Client ID field in the Project table; set referential integrity
              for the relationship.
           5. Add the Trainer table to the Relationships window.
           6. Create a relationship between the Trainer Initials field in the
              Project table and the Initials field in the Trainer table; set
              referential integrity for the relationship.
           7. Close the Relationships window and save the changes.
           8. Open the Client table in Datasheet view and view the subdatasheet
              for the CONCORD client. Then, collapse the subdatasheet and close
              the Client table.
           9. Open the Relationships window. Delete the relationship line between
              the Project and Trainer tables and remove the Trainer field list,
              since it is no longer related to any tables. Then, close the
              Relationships window and save the changes.
           10. Close the database file.




Page 138                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1      Lesson 7 - Creating Relationships




Canadian Beaver (CBIT)                           Page 139
LESSON 8 -
PRINTING DATA

In this lesson, you will learn how to:
         Print table data

         Change the page setup

         Use print preview

         Print selected records
Access 2003 - Lvl 1                                                          Lesson 6 - Printing Data




PRINTING TABLE DATA

    Discussion
          You can print table data from either the Database window or Datasheet view. When
          you use the Print button, Access uses the default printer settings.

          If there are too many fields to fit on one page, Access prints as many fields as possible
          on the first page and then prints additional pages for the next set of fields, etc., until
          all the fields have been printed. If you have a large table with multiple fields, the
          printout could result in many pages of disjointed information. Consequently, the Print
          button is best utilized for printing smaller, more compact tables.



                     If a filter is applied, only the displayed records will print.




   Procedures
          1. Open a table in Datasheet view.


          2. Click the Print button            on the Table Datasheet toolbar.




    Step-by-Step
          From the Student Data directory, open WORLD07.MDB.
          Print the data in a table.

          Open the Customers table in Datasheet view.

           Steps                                              Practice Data
           1. Click the Print button on the Table
              Datasheet toolbar.                              Click
              Access prints all data in the table.




Canadian Beaver (CBIT)                                                                     Page 141
Lesson 6 - Printing Data                                                       Access 2003 - Lvl 1



CHANGING THE PAGE SETUP

    Discussion
           You can control how a table is printed by changing the page setup options. You can
           adjust the margins, as well as change the page orientation so that more data can fit on
           fewer pages.

           On the Margins page in the Page Setup dialog box, you can change the size of the
           margins and choose whether or not to print column headings. On the Page page, you
           can select either portrait orientation (with the shorter edge of the page at the top) or
           landscape orientation (with the longer edge of the page at the top). You can also
           choose a different paper size, paper source, and printer.




                                          The Page Setup dialog box




                    You can also open the Page Setup dialog box by selecting the
                     Setup button in the Print dialog box.


                    Print settings are saved for forms and reports, but not for
                     tables.




Page 142                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                     Lesson 6 - Printing Data




   Procedures
          1. Open a table in Datasheet view.
          2. Select the File menu.
          3. Select the Page Setup command.
          4. Select the desired options.
          5. Select the Page tab.
          6. Select the desired options.
          7. Select OK.




    Step-by-Step
          Change the page setup.

          If necessary, open the Customers table in Datasheet view.

           Steps                                          Practice Data
           1. Select the File menu.                       Click File
              The File menu appears.
           2. Select the Page Setup command.              Click Page Setup...
              The Page Setup dialog box opens with
              the Margins page displayed.
           3. Select the desired options.                 Follow the instructions
              The options are selected, and a             shown below the table
              preview of the changes appears in the       before continuing on to
              Sample box.                                 the next step
           4. Select the Page tab.                        Click the Page tab
              The Page page appears.
           5. Select the desired options.                 Click    Landscape
              The options are selected.
           6. Select OK.                                  Click OK
              The Page Setup dialog box closes, and
              the page setup options are applied to
              the table.

          Enter .75 as the measurement for all the margins: top, bottom, left, and right.



Canadian Beaver (CBIT)                                                                      Page 143
Lesson 6 - Printing Data                                                        Access 2003 - Lvl 1


           Return to the table and continue on to the next step (step 4).


USING PRINT PREVIEW

    Discussion
           Before printing, you can preview a table to see how the data appears on each page.
           The Print Preview feature displays the pages as they will appear when printed,
           including all aspects of the layout. You can use the Print Preview feature to preview
           the appearance of your printed pages before printing them; thereby allowing you to
           make additional changes.

           The default view in print preview displays the full page, making the print difficult to
           read; however, you can increase the magnification of the page by zooming print
           preview. This option makes the text easier to read. When the mouse pointer is
           positioned over the page, it changes into a magnifying glass. When you click the page
           with the magnifying glass, the magnification increases so that you can read the area of
           the page you clicked. When you click the page again, the magnification returns to full
           page view.

           If your table contains multiple pages, you can view several pages at one time. The
           page images are reduced as necessary to fit in the print preview window. The pages
           displayed initially depend upon the location of the insertion point when you access the
           Print Preview feature.




                                                Print preview




Page 144                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                      Lesson 6 - Printing Data



                     You can also use the Page buttons at the bottom of the print
                      preview window to move between pages.


                     Clicking the Setup button on the Print Preview toolbar opens
                      the Page Setup dialog box.




   Procedures
          1. Open a table in Datasheet view.


          2. Click the Print Preview button           on the Table Datasheet
             toolbar.
          3. Click the area of the page you want to magnify.
          4. Click anywhere in the page to return to full page view.
          5. Press [Page Down] or [Page Up] to display the next or previous
             page of the printout, respectively.
          6. To view more than one page at a time, click and hold the Multiple

               Pages button        .
          7. Select the desired page layout.


          8. To view a single page, click the One Page button           .
          9. Select Close.




    Step-by-Step
          Use print preview to preview a table before printing.

          If necessary, open the Customers table in Datasheet view.

           Steps                                           Practice Data
           1. Click the Print Preview button on the
              Table Datasheet toolbar.                     Click
              Print preview opens.




Canadian Beaver (CBIT)                                                                 Page 145
Lesson 6 - Printing Data                                                     Access 2003 - Lvl 1



            Steps                                          Practice Data
            2. Click the area of the page you want to      Click in the middle of the
               magnify.                                    page
               The page is magnified.
            3. Click anywhere in the page to return to     Click anywhere in the
               full page view.                             document
               The entire page appears in print
               preview.
            4. Press [Page Down] or [Page Up] to           Press [Page Down]
               display the next or previous page of
               the printout, respectively.
               The next or previous page appears
               accordingly.
            5. To view more than one page at a time,
               click and hold the Multiple Pages           Click and hold
               button.
               A grid of available options appears.
            6. Select the desired page layout.             Drag to select the 1x3
               The pages appear in the selected page       Pages layout
               layout.
            7. To view a single page, click the One
               Page button.                                Click
               Only the current page appears in print
               preview.
            8. Select Close.                               Click Close
               Print preview closes.



PRINTING SELECTED RECORDS

    Discussion
           You can print selected, adjacent records. You can print selected records by first
           selecting the records in the table and then selecting the Selected Records(s) option in
           the Print dialog box.




Page 146                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                       Lesson 6 - Printing Data




                                           Printing selected records




                     You can also select other options in the Print dialog box, such
                      as the number of copies you want to print.


                     You can select multiple, adjacent records by selecting the first
                      record you want to print, holding the [Shift] key, and clicking
                      the last record you want to print.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the records you want to print.
          3. Select the File menu.
          4. Select the Print command.
          5. Select the Selected Record(s) option under Print Range.
          6. Select OK.




Canadian Beaver (CBIT)                                                                   Page 147
Lesson 6 - Printing Data                                                   Access 2003 - Lvl 1




    Step-by-Step
           Print selected records in a table.

           If necessary, open the Customers table in Datasheet view.

            Steps                                       Practice Data
            1. Select the records you want to print.    Drag in the record
               The records are selected.                selector, from the first
                                                        record to the sixth record
            2. Select the File menu.                    Click File
               The File menu appears.
            3. Select the Print command.                Click Print...
               The Print dialog box opens.
            4. Select the Selected Record(s) option     Click Selected
               under Print Range.                       Record(s)
               The Selected Record(s) option is
               selected.
            5. Select OK.                               Click OK
               The Print dialog box closes, and
               Access prints the selected records.

           Close WORLD07.MDB.




Page 148                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                   Lesson 6 - Printing Data




EXERCISE
PRINTING DATA

      Task
          Print data in Access.

          1. Open Train07x.
          2. Open the Trainer table in Datasheet view.
          3. Use the Print button to print the table data.
          4. Change the left and right margins to .75” and the orientation to
             landscape.
          5. Preview the printout and then close print preview.
          6. Print only the first five records.
          7. Close the table.
          8. Close the database.




Canadian Beaver (CBIT)                                                              Page 149
LESSON 9 -
FINDING AND FILTERING DATA

In this lesson, you will learn how to:
         Sort records

         Find specific records

         Find records using wildcards

         Use Replace

         Use Filter By Selection

         Apply/Remove a filter

         Use Filter Excluding Selection

         Use Filter For

         Use Filter By Form
Access 2003 - Lvl 1                                        Lesson 5 - Finding and Filtering Data




SORTING RECORDS

    Discussion
          When you display a table in Datasheet view, Access displays the records in order by
          their primary key. You may want to edit or print records in a different order (e.g., by
          last name, zip code, or amount due).

          You can sort records in either ascending or descending order. Ascending order sorts
          numerically from 0 to 9 and then alphabetically from A to Z. Descending order sorts
          numerically from 9 to 0 and then alphabetically from Z to A. The Table Datasheet
          toolbar provides buttons for both ascending and descending sorts.



                     You can remove a sort by selecting the Records menu and the
                      Remove Filter/Sort command.


                     You can also sort records by selecting the Records menu,
                      pointing to the Sort command, and selecting the desired sort
                      command, or by right-clicking in the field you want to sort
                      and selecting the desired sort command.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the field by which you want to sort.


          3. Click the Sort Ascending button            or the Sort Descending

               button        on the Table Datasheet toolbar.




    Step-by-Step
          From the Student Data directory, open WORLD06.MDB.
          Sort records in a table.

          Open the Customers table in Datasheet view.



Canadian Beaver (CBIT)                                                                  Page 151
Lesson 5 - Finding and Filtering Data                                         Access 2003 - Lvl 1



            Steps                                          Practice Data
            1. Select the field by which you want to       Click in the Store Name
               sort.                                       column
               The insertion point appears in the
               column.
            2. Click the Sort Ascending or Sort
               Descending button on the Table              Click
               Datasheet toolbar.
               The records are sorted accordingly.

           Practice the Concept: Scroll as necessary to view the Credit Limit column and sort
           the records by the Credit Limit field in descending order. Then, remove the sort by
           selecting the Records menu and the Remove Filter/Sort command.


FINDING SPECIFIC RECORDS

    Discussion
           The Find feature allows you to locate records quickly. You can search for records that
           contain a unique value in a certain field, or you can find all records that have a
           common value in a field. Access scans the selected field, beginning with the current
           record, and highlights the first instance of the search text it finds. You can repeat the
           search to find additional records containing the same data.

           In the Find and Replace dialog box, you can select options to control how Access
           searches for matching records. You can use the Look In list to search the current
           column or the entire table. The options on the Match list allow you to specify whether
           to match any part of the field, the whole field, or only the start of the field.

           The Find and Replace dialog box remains open during a search. If needed, you can
           move the dialog box out of the way to view the search as it progresses.




                                      The Find and Replace dialog box




Page 152                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 5 - Finding and Filtering Data



                     The Find and Replace dialog box retains the settings of the
                      most recent search. Pressing the [Shift+F4] key combination
                      performs the same search again.


                     You can also open the Find page in the Find and Replace
                      dialog box by selecting the Edit menu and the Find command.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the field you want to search.


          3. Click the Find button            on the Table Datasheet toolbar.
          4. Type the text you want to find in the Find What box.
          5. Select the Match list.
          6. Select the desired option.
          7. Select Find Next to begin the search.
          8. Select Find Next to find the next matching record.
          9. When all records have been searched, select OK.
          10. Select Cancel to close the Find and Replace dialog box.




    Step-by-Step
          Find specific records in a table.

          If necessary, open the Customers table in Datasheet view.

           Steps                                           Practice Data
           1. Select the field you want to search.         Scroll as necessary and
              The insertion point appears in the           click in the
              column.                                      State/Province column




Canadian Beaver (CBIT)                                                                Page 153
Lesson 5 - Finding and Filtering Data                                       Access 2003 - Lvl 1



            Steps                                         Practice Data
            2. Click the Find button on the Table
               Datasheet toolbar.                         Click
               The Find and Replace dialog box
               opens with the insertion point in the
               Find What box.
            3. Type the text you want to find in the      Type ny
               Find What box.
               The text appears in the Find What
               box.
            4. Select the Match list.                     Click Match
               A list of available options appears.
            5. Select the desired option.                 Click Whole Field, if
               The option is selected.                    necessary
            6. Select Find Next to begin the search.    Click Find Next
               The data in the first matching record is
               selected.
            7. Select Find Next to find the next          Click Find Next twice
               matching record.
               The data in the next matching record
               is selected, or a Microsoft Access
               message box opens to notify you that
               all records have been searched.
            8. When all records have been searched,       Click OK
               select OK.
               The Microsoft Access message box
               closes.
            9. Select Cancel to close the Find and        Click Cancel
               Replace dialog box.
               The Find and Replace dialog box
               closes.

           Practice the Concept: Find all records in the state of Pennsylvania. Then, close the
           Find and Replace dialog box.


FINDING RECORDS USING WILDCARDS

    Discussion
           You can use wildcards in a find. Wildcards are characters that represent other
           characters. They allow you to find records, even if you are not sure how the entry
           appears in the field. Wildcards also allow you to find records that share a common

Page 154                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 5 - Finding and Filtering Data


          entry within a field, but not necessarily the entire field entry. For example, you may
          want to search for the area code at the beginning of a telephone number field.

          You can use the following wildcards in a find:

           Wildcard       Description
                *        Represents any number of characters. It can be used
                         anywhere in the search text. For example, North* will
                         find all entries beginning with the word North; *Sports
                         will find all entries ending with the word Sports; and
                         *Sports* will find all entries containing the word Sports.
                ?        Represents only one character; for example, Sm?th will
                         find Smith, Smyth, etc.
                []       Finds any character enclosed in the set; for example,
                         b[ai]t will find bat and bit, but not bet and but.
                !        Finds any character except the ones enclosed in the set;
                         for example, b[!ai]t will find bet and but, but not bat or
                         bit.
                -        Finds any character in a range in a set; for example,
                         b[u-w]t finds but, bvt, and bwt.
                #        Represents only one digit; for example, 1980# finds
                         19801, 19802, 19803, etc.




   Procedures
          1. Open a table in Datasheet view.
          2. Select the field you want to search.


          3. Click the Find button         on the Table Datasheet toolbar.
          4. Type the text you want to find in the Find What box.
          5. Select the Match list.
          6. Select the desired option.
          7. Select Find Next to begin the search.
          8. Select Find Next to find the next matching record.
          9. When all records have been searched, select OK.
          10. Select Cancel.

Canadian Beaver (CBIT)                                                                  Page 155
Lesson 5 - Finding and Filtering Data                                      Access 2003 - Lvl 1




    Step-by-Step
           Find records in a table using wildcards.

           If necessary, open the Customers table in Datasheet view.

            Steps                                       Practice Data
            1. Select the field you want to search.     Scroll as necessary and
               The insertion point appears in the       click in the Store Name
               column.                                  column
            2. Click the Find button on the Table
               Datasheet toolbar.                       Click
               The Find and Replace dialog box
               opens with the text in the Find What
               box selected.
            3. Type the text you want to find in the    Type *athlete*
               Find What box.
               The text appears in the Find What
               box.
            4. Select the Match list.                   Click Match
               A list of available options appears.
            5. Select the desired option.               Click Any Part of Field
               The option is selected.
            6. Select Find Next to begin the search.     Click Find Next
               The field in the first matching record is
               selected.
            7. Select Find Next to find the next        Click Find Next four
               matching record.                         times
               The field in the next matching record
               is selected, or a Microsoft Access
               message box opens to notify you that
               all records have been searched.
            8. When all records have been searched,     Click OK
               select OK.
               The Microsoft Access message box
               closes.
            9. Select Cancel.                           Click Cancel
               The Find and Replace dialog box
               closes.



Page 156                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 5 - Finding and Filtering Data



USING REPLACE

    Discussion
          The Replace feature allows you to quickly locate and replace data in a field. This
          feature is especially useful if the same information must be changed in several
          records. Access scans the selected field, beginning with the current record, and selects
          the first instance of the search text it finds. You can replace the text or search for the
          next instance of the search text. You can also replace all the instances of the search
          text at once.

          In the Find and Replace dialog box, you can control how Access searches for
          matching records. The Look In list enables you to search either the current column or
          the entire table. The options on the Match list allow you to specify whether to match
          any part of the field, the whole field, or only the start of the field.

          The Find and Replace dialog box remains open during a search. You can move it as
          needed to view the datasheet records.




                                                Replacing data




                     You can also display the Replace page in the Find and
                      Replace dialog box by selecting the Edit menu and the
                      Replace command.




                     Selecting the Replace All button in the Find and Replace
                      dialog box replaces all data that matches the search text. Make
                      sure that you want to replace all data before you use the
                      Replace All button because you may not be able to retrieve
                      the data once it has been replaced.




Canadian Beaver (CBIT)                                                                    Page 157
Lesson 5 - Finding and Filtering Data                                         Access 2003 - Lvl 1




   Procedures
           1. Open a table in Datasheet view.
           2. Select the field you want to search.


           3. Click the Find button         on the Table Datasheet toolbar.
           4. Select the Replace tab.
           5. Type the text you want to find in the Find What box.
           6. Select the Replace With box.
           7. Type the desired replacement text.
           8. Select Find Next to begin the search.
           9. Select Replace.
           10. Select Replace All.
           11. Select Yes to replace all remaining matches.
           12. Select Cancel.




    Step-by-Step
           Use the Replace feature to replace record data in a table.

           If necessary, open the Customers table in Datasheet view.

            Steps                                          Practice Data
            1. Select the field you want to search.        Scroll as necessary and
               The insertion point appears in the          click in the Postal Code
               column.                                     column
            2. Click the Find button on the Table
               Datasheet toolbar.                          Click
               The Find and Replace dialog box
               opens.
            3. Select the Replace tab.                     Click the Replace tab
               The Replace page appears with the
               text in the Find What box selected.




Page 158                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                      Lesson 5 - Finding and Filtering Data



           Steps                                          Practice Data
           4. Type the text you want to find in the       Type 23211
              Find What box.
              The text appears in the Find What
              box.
           5. Select the Replace With box.                Press [Tab]
              The insertion point moves to the
              Replace With box.
           6. Type the desired replacement text.          Type 23209
              The text appears in the Replace With
              box.
           7. Select Find Next to begin the search.    Click Find Next
              The data in the first matching record is
              selected.
           8. Select Replace.                             Click Replace
              The data is replaced, and the data in
              the next matching record is selected.
           9. Select Replace All.                         Click Replace All
              A Microsoft Access warning box
              opens, warning you that you will not
              be able to undo the Replace operation.
           10. Select Yes to replace all remaining        Click Yes
               matches.
               The Microsoft Access warning box
               closes, and all remaining instances of
               the data are replaced.
           11. Select Cancel.                             Click Cancel
               The Find and Replace dialog box
               closes.



USING FILTER BY SELECTION

    Discussion
          You can filter data in Access. Filtering data allows you to view only those records
          with which you want to work by hiding the records you do not want to see. For
          example, you can filter data in a customers table so that only the records of those
          customers located in a specific region appear.

          A quick and easy way to filter data in Access is to use the Filter By Selection feature,
          in which only those records that match the data in the selected field are displayed.

Canadian Beaver (CBIT)                                                                  Page 159
Lesson 5 - Finding and Filtering Data                                          Access 2003 - Lvl 1



           A filter remains in effect until you remove it.




                                                A filtered table




                    When a filter is in effect, the (Filtered) indicator appears on
                     the status bar.


                    You can also filter by selection by selecting the Records
                     menu, pointing to the Filter command, and selecting the
                     Filter By Selection command, or by right-clicking the field
                     containing the value by which you want to filter and selecting
                     the Filter By Selection command.




   Procedures
           1. Open a table in Datasheet view.
           2. Select any field that contains the data by which you want to filter.


           3. Click the Filter By Selection button           on the Table Datasheet
              toolbar.




Page 160                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 5 - Finding and Filtering Data




    Step-by-Step
          Use the Filter By Selection feature.

          If necessary, open the Customers table in Datasheet view.

           Steps                                             Practice Data
           1. Select any field that contains the data        Scroll as necessary to the
              by which you want to filter.                   Region column and click
              The insertion point appears in the             in any Southeast field
              field.
           2. Click the Filter By Selection button
              on the Table Datasheet toolbar.                Click
              Only the filtered records appear.



APPLYING/REMOVING A FILTER

    Discussion
          The Apply Filter button acts a toggle. If a filter is in effect, it becomes the Remove
          Filter button. If no filter is in effect, it applies the most recent filter when you click it.



                     You can also reapply the previous filter by selecting the
                      Records menu and the Apply Filter/Sort command.


                     You can also remove a filter by selecting the Records menu
                      and the Remove Filter/Sort command or by right-clicking
                      any field and selecting the Remove Filter/Sort command.




   Procedures
          1. Open a table in Datasheet view.


          2. Click the Apply Filter or Remove Filter button                on the Table
             Datasheet toolbar, as applicable.




Canadian Beaver (CBIT)                                                                        Page 161
Lesson 5 - Finding and Filtering Data                                           Access 2003 - Lvl 1




    Step-by-Step
           Apply and remove a filter.

           If necessary, open the Customers table in Datasheet view and filter the table by the
           Southeast region.

            Steps                                           Practice Data
            1. Click the Apply Filter or Remove
               Filter button on the Table Datasheet         Click
               toolbar, as applicable.
               The filter is applied or removed
               accordingly.

           Practice the Concept: Click the Apply Filter button again to reapply the previous
           filter. Then, remove the filter again to display all the records.


USING FILTER EXCLUDING SELECTION

    Discussion
           The Filter Excluding Selection feature is similar to the Filter by Selection feature
           except that it displays all records that do not match the filter criteria. For example, if
           most of the customers in a customers table are located in the state of New York, you
           can use the Filter Excluding Selection feature to display only those records in which
           the customers are not located in New York.



                    You can also enable the Filter Excluding Selection feature by
                     right-clicking any field containing the value you want to
                     exclude and selecting the Filter Excluding Selection
                     command.




   Procedures
           1. Open a table in Datasheet view.
           2. Select the field that contains the data you want to exclude.
           3. Select the Records menu.
           4. Point to the Filter command.


Page 162                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                       Lesson 5 - Finding and Filtering Data


          5. Select the Filter Excluding Selection command.




    Step-by-Step
          Use the Filter Excluding Selection feature.

          If necessary, open the Customers table in Datasheet view and display all records.

           Steps                                           Practice Data
           1. Select the field that contains the data      Scroll as necessary to the
              you want to exclude.                         Country column and click
              The insertion point appears in the           in any U.S.A. text
              field.
           2. Select the Records menu.                     Click Records
              The Records menu appears.
           3. Point to the Filter command.                 Point to Filter
              The Filter submenu appears.
           4. Select the Filter Excluding Selection        Click Filter Excluding
              command.                                     Selection
              The records are filtered to exclude the
              selected data.

          Remove the filter.


USING FILTER FOR

    Discussion
          The Filter For feature allows you to quickly display records containing specific data
          in a field. When you apply this type of filter, Access filters the table and displays only
          those records containing data that matches the filter.




Canadian Beaver (CBIT)                                                                    Page 163
Lesson 5 - Finding and Filtering Data                                        Access 2003 - Lvl 1




                                              Using Filter For




                   You can use wildcards with the Filter For feature.




   Procedures
           1. Open a table in Datasheet view.
           2. Right-click the field by which you want to filter.
           3. Select the Filter For command.
           4. Type the desired filter.
           5. Press [Enter].




    Step-by-Step
           Use the Filter For feature.

           If necessary, open the Customers table in Datasheet view and display all records.




Page 164                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                       Lesson 5 - Finding and Filtering Data



           Steps                                          Practice Data
           1. Right-click the field by which you          Scroll as necessary and
              want to filter.                             right-click any field in the
              A shortcut menu appears.                    City column
           2. Select the Filter For command.              Click Filter For
              The insertion point appears in the
              Filter For box.
           3. Type the desired filter.                    Type boise
              The text appears in the Filter For box.
           4. Press [Enter].                              Press [Enter]
              The filtered records appear.

          Remove the filter.


USING FILTER BY FORM

    Discussion
          You can use the Filter By Form feature to filter data. The Filter By Form feature
          displays a blank record in the Filter by Form window. You enter the data by which
          you want to filter in the corresponding field. When the filter is applied, only those
          records with matching data appear; all the other records are hidden.

          The Filter by Form feature allows you to filter by more than one field. This feature is
          especially useful when you are filtering a large database with many fields and/or
          records.

          All fields in the Filter By Form window have a lookup list that displays existing field
          data so that you can easily choose the data by which you want to filter. These lookup
          lists are useful because you do not have to remember exactly how the data was entered
          into the field.




Canadian Beaver (CBIT)                                                                   Page 165
Lesson 5 - Finding and Filtering Data                                         Access 2003 - Lvl 1




                                         The Filter by Form window




                    The most recent filter appears in the Filter by Form window, if
                     applicable; you can click the Clear Grid button on the
                     Filter/Sort toolbar to clear the previous filter.


                    You can also enable the Filter By Form feature by selecting
                     the Records menu, pointing to the Filter command, and
                     selecting the Filter By Form command.




   Procedures
           1. Open a table in Datasheet view.


           2. Click the Filter By Form button          on the Table Datasheet
              toolbar.


           3. Click the Clear Grid button          on the Filter/Sort toolbar to clear
              the previous filter, if necessary.
           4. Select the field by which you want to filter.
           5. Select the field lookup list.
           6. Select the data by which you want to filter.



Page 166                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                      Lesson 5 - Finding and Filtering Data




          7. Click the Apply Filter button         on the Filter/Sort toolbar.




    Step-by-Step
          Use the Filter By Form feature.

          If necessary, open the Customers table in Datasheet view.

           Steps                                         Practice Data
           1. Click the Filter By Form button on
              the Table Datasheet toolbar.               Click
              The Filter by Form window opens, and
              the previous filter appears in the grid.
           2. Click the Clear Grid button on the
              Filter/Sort toolbar to clear the           Click
              previous filter, if necessary.
              The filter is cleared.
           3. Select the field by which you want to      Scroll as necessary and
              filter.                                    click in the Region
              The insertion point appears in the field   column
              and an arrow appears to the right of
              the field.
           4. Select the field lookup list.              Click Region
              A list of existing field data appears.
           5. Select the data by which you want to       Click Can. & Mex
              filter.
              The data appears in the field.
           6. Click the Apply Filter button on the
              Filter/Sort toolbar.                       Click
              The Filter by Form window closes,
              and the filtered records appear.

          Remove the filter. Then, close the Customers table without saving the changes.
          Close WORLD06.MDB.




Canadian Beaver (CBIT)                                                              Page 167
Lesson 5 - Finding and Filtering Data                                         Access 2003 - Lvl 1




EXERCISE
FINDING AND FILTERING DATA

      Task
           Find and filter data.

           1. Open Train06x.
           2. Open the Client table in Datasheet view.
           3. Sort the records in descending order by the Zip field.
           4. Sort the records in ascending order by the Client ID field.
           5. Find all the records in the state of Texas.
           6. Use a wildcard to find all records with a telephone number that starts
              with 713.
           7. Use the Replace feature to find the 206-707-7070 telephone number
              and replace it with 206-707-8954.
           8. Close and save the Client table.
           9. Open the Payment table in Datasheet view.
           10. Use the Filter By Selection feature to find all records with VISA as
               the type of payment. Then, remove the filter.
           11. Use the Filter Excluding Selection feature to find all records with a
               balance due that is not $0.00. Then, remove the filter.
           12. Use the Filter for feature to find all records with a payment date of
               3/30/01. Then, remove the filter.
           13. Use the Filter By Form feature to find all records with a balance due
               of 100. (Hint: Remember to clear the grid before you apply the new
               filter.) Then, remove the filter.
           14. Close and save the table.
           15. Close the database.




Page 168                                                                 Canadian Beaver (CBIT)
LESSON 10 -
USING SIMPLE QUERIES

In this lesson, you will learn how to:
         Use queries and recordsets

         Use the Simple Query Wizard

         Create a query in Design view

         Open a query

         Add a table to a query

         Join tables in a query

         Run a query
Lesson 8 - Using Simple Queries                                                Access 2003 - Lvl 1




USING QUERIES AND RECORDSETS

    Discussion
           A query is a means of extracting information from tables. You can use queries to
           analyze the data in a table or to extract data for a form or report. Queries are
           commonly used to display data in related tables and enable you to control not only
           which records to display, but also which fields. For example, you may want to give a
           sales representatives a list of the contacts and telephone numbers for a particular
           region; you can create a query to extract just the contact names and telephone
           numbers within the specified region.

           A query does not contain data; rather, it is a set of instructions. Access uses these
           instructions to select and display the desired records in a table. As a result, whenever
           new data is added to the queried table, the query is automatically updated; if the new
           records meet the conditions of the query, they will be included when the query runs.

           When you open or run a query, a recordset appears. A recordset contains all the fields
           and records that meet the conditions of the query. Although the recordset is not a
           table, it can be used to edit or add new records in the queried tables.



USING THE SIMPLE QUERY WIZARD

    Discussion
           The Simple Query Wizard guides you through the steps for creating a basic select
           query. When you use the Simple Query Wizard, you select the table you want to use
           and the fields you want the query to display. Then, you name the query and choose
           whether to display the results of the query (recordset) or go to Design view to work
           with the query design.




Page 170                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                             Lesson 8 - Using Simple Queries




                                           Adding fields to a query




                     You can also activate the Simple Query Wizard by double-
                      clicking the Create query by using wizard option in the
                      Queries object list in the Database window or by selecting the
                      Insert menu and the Query command.


                     You can also add fields to the Selected Fields list by double-
                      clicking the name of the field in the Available Fields list.




   Procedures
          1. Display the Queries object list.

          2. Select the        New button on the Database window toolbar.
          3. Select Simple Query Wizard.
          4. Select OK.
          5. Select the Tables/Queries list.
          6. Select the table or query you want to query.
          7. Select the first field you want to add to the query from the Sample
             Fields list box.

          8. Select the arrow           to the right of the Sample Fields list box.


Canadian Beaver (CBIT)                                                                 Page 171
Lesson 8 - Using Simple Queries                                                 Access 2003 - Lvl 1


           9. Add other fields to the Selected Fields list box, as desired.
           10. Select Next >.
           11. Type the desired query name.
           12. Select Finish.




    Step-by-Step
           From the Student Data directory, open WORLD09.MDB.
           Use the Simple Query Wizard to display selected fields in a table.

            Steps                                          Practice Data
            1. Display the Queries object list.
               The Queries object list appears in the      Click      Queries
               Database window.
            2. Select the New button on the Database
                                                           Click      New
               window toolbar.
               The New Query dialog box opens.
            3. Select Simple Query Wizard.                 Click Simple Query
               Simple Query Wizard is selected.            Wizard
            4. Select OK.                                  Click OK
               The New Query dialog box closes, and
               the Simple Query Wizard opens.
            5. Select the Tables/Queries list.             Click Tables/Queries
               A list of available tables and queries
               appears.
            6. Select the table or query you want to       Click Table: Customers
               query.
               All available fields in the selected
               table or query appear in the Available
               Fields list box.
            7. Select the first field you want to add to   Click Store Name
               the query from the Available Fields
               list box.
               The field is selected.
            8. Select the arrow to the right of the
               Available Fields list box.                  Click
               The field moves to the Selected Fields
               list box.



Page 172                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 8 - Using Simple Queries



           Steps                                           Practice Data
           9. Add other fields to the Selected Fields      Follow the instructions
              list box, as desired.                        below the table before
              The fields move to the Selected Fields       continuing on to the next
              list box.                                    step
           10. Select Next >.                              Click Next >
               The next page of the Simple Query
               Wizard appears with the text in the
               What title do you want for your
               query? box selected.
           11. Type the desired query name.                Type Contacts and Phone
               The name appears in the What title do       Numbers
               you want for your query? box.
           12. Select Finish.                              Click Finish
               The Simple Query Wizard closes, the
               query runs, and the recordset appears
               in Datasheet view.

          Add the Customer Number, Contact Name, and Phone Number fields to the query.

          Return to the table and continue on to the next step (step 10).

          Close the query. Notice that the Contacts and Phone Numbers query now appears in
          the Queries object list.


CREATING A QUERY IN DESIGN VIEW

    Discussion
          You can create a query in Design view. This option gives you the most flexibility in
          designing a query. It allows you to add criteria for selecting records, as well as sort the
          recordset.

          When you create a query in Design view, the design grid is used to set up the query.
          The field lists of all tables to be used in the query appears in the top pane. The design
          grid appears in the bottom pane. You drag the fields you want to use in the query to
          the design grid and then add the desired criteria and sorts.




Canadian Beaver (CBIT)                                                                    Page 173
Lesson 8 - Using Simple Queries                                                 Access 2003 - Lvl 1




                                      Creating a query in Design view




                   You can also open Design view by double-clicking the Create
                    query in Design view option in the Queries object list.


                   You can also add a field to a query by clicking in any blank
                    column in the Field row, clicking the drop-down arrow, and
                    then selecting the field you want to add, or by dragging the
                    field to the design grid.


                   You can add all the fields to the design grid by dragging the
                    asterisk (*) at the top of the field list to the Field row in any
                    column.




   Procedures
           1. Display the Queries object list.

           2. Click the       New button on the Database window toolbar.
           3. Select Design View.
           4. Select OK.
           5. Add the table you want to query.
           6. Select Close.


Page 174                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 8 - Using Simple Queries


          7. Add the desired fields to the query.


          8. Click the Save button         .
          9. Type the desired query name.
          10. Select OK.




    Step-by-Step
          Create a query in Design view.

          If necessary, display the Queries object list.

           Steps                                           Practice Data
           1. Click the New button on the Database
                                                           Click      New
              window toolbar.
              The New Query dialog box opens.
           2. Select Design View.                          Click Design View, if
              Design View is selected.                     necessary
           3. Select OK.                                   Click OK
              The New Query dialog box closes,
              Design view appears, and the Show
              Table dialog box opens.
           4. Add the table you want to query.             Double-click Customers
              The table field list appears in the top
              pane of Design view.
           5. Select Close.                                Click Close
              The Show Table dialog box closes.
           6. Add the first field to the query.            Double-click Customer
              The field appears in the design grid.        Number in the field list
           7. Add other fields to the query as             Follow the instructions
              desired.                                     shown below the table
              The fields appear in the design grid.        before continuing on to
                                                           the next step
           8. Click the Save button.
              The Save As dialog box opens with the        Click
              text in the Query Name box selected.




Canadian Beaver (CBIT)                                                                Page 175
Lesson 8 - Using Simple Queries                                                 Access 2003 - Lvl 1



            Steps                                           Practice Data
            9. Type the desired query name.                 Type Customer Query
               The name appears in the Query Name
               box.
            10. Select OK.                                  Click OK
                The Save As dialog box closes, and the
                query is saved.

           Add the Store Name, Sales Rep, Region, and Credit Limit fields to the query.

           Return to the table and continue on to the next step (step 8).

           Close the query. Notice that the Customer Query query now appears in the Queries
           object list.


OPENING A QUERY

    Discussion
           When you open a query, Access runs the query and displays its recordset in Datasheet
           view. If you have added records since the last time you ran the query, the new records
           will appear as long as they meet the query criteria.



                    You can also run a query by selecting it in the Queries object
                     list and then selecting the Open button or by right-clicking it
                     and then selecting the Open command.




   Procedures
           1. Select the Queries object list.
           2. Double-click the name of the query you want to run.




    Step-by-Step
           Open a query in Datasheet view.



Page 176                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 8 - Using Simple Queries


          If necessary, display the Queries object list. If the Customer Query query does not
          exist, use the Customer Query 2 query.

           Steps                                           Practice Data
           1. Double-click the name of the query           Double-click Customer
              you want to run.                             Query
              The query runs, and its recordset
              appears in Datasheet view.

          Close the recordset.


ADDING A TABLE TO A QUERY

    Discussion
          You can use more than one table in a query. The tables must be joined, however, in
          order for the query to produce accurate results. If the tables are not yet joined, you can
          create a join in the top pane of Design view.

          The field lists of all tables added to a query appear in the top pane of Design view. If
          the tables are already related, join lines appear as well.

          Once you have added a table to a query, you can then add fields from its field list to
          the design grid. The Table row in the design grid indicates the table in which a field is
          stored.

          When you create a new query in Design view, the Show Table dialog box opens
          automatically so that you can add the desired tables. However, when you modify an
          existing query in Design view, you must manually open the Show Table dialog box.



                     You can also open the Show Table dialog box by right-
                      clicking in the top pane of Design view and then selecting the
                      Show Table command or by selecting the Query menu and
                      the Show Table command.




   Procedures
          1. Select the Queries object list.
          2. Select the query you want to edit.



Canadian Beaver (CBIT)                                                                    Page 177
Lesson 8 - Using Simple Queries                                               Access 2003 - Lvl 1



           3. Select the      Design button on the Database window toolbar.


           4. Click the Show Table button           on the Query Design toolbar.
           5. Double-click the table you want to add to the query.
           6. Select Close.




    Step-by-Step
           Add a table to a query.

           If necessary, display the Queries object list. If the Customer Query query does not
           exist, use the Customer Query 2 query.

            Steps                                         Practice Data
            1. Select the query you want to edit.         Click Customer Query, if
               The query is selected.                     necessary
            2. Select the Design button on the
                                                          Click      Design
               Database window toolbar.
               The query opens in Design view.
            3. Click the Show Table button on the
               Query Design toolbar.                      Click
               The Show Table dialog box opens.
            4. Double-click the table you want to add     Double-click Orders
               to the query.
               The table field list is added to the
               query.
            5. Select Close.                              Click Close
               The Show Table dialog box closes.

           Add the Order Number and Order Date fields from the Orders field list to the
           query. You may have to scroll the design grid to display additional columns in the
           Field row.




Page 178                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                             Lesson 8 - Using Simple Queries



JOINING TABLES IN A QUERY

    Discussion
          If you have multiple tables in a query, the tables must be joined in order for the query
          to produce accurate and meaningful results. Otherwise, Access will not know which
          records are associated with which, so every possible combination of records would
          appear in the recordset. For example, if one table has 20 records and the other has 5,
          then the recordset will contain 100 records and the results are virtually meaningless.

          If table relationships have already been defined, the join lines appear automatically in
          Design view. In addition, Access will automatically create a join if there are fields
          with the same name in multiple tables. If Access cannot define the relationships
          between query tables, you must create them.

          The join type defined in the relationship is particularly important in queries. The
          default type is an inner join, in which records are only included in the recordset if
          there is matching data in the join fields of both tables. You can also create an outer
          join, in which all the records from the “one” table appear, even if there is no matching
          data in the “many” table.




                                           Joining tables in a query




                     Joins that you define in a query do not appear in the
                      Relationships window.




Canadian Beaver (CBIT)                                                                   Page 179
Lesson 8 - Using Simple Queries                                               Access 2003 - Lvl 1



                    You can remove a join line from a query by selecting it and
                     then pressing the [Delete] key. You must remove referential
                     integrity before deleting a join.


                    You can also open the Join Properties dialog box by right-
                     clicking the join line and selecting the Join Properties
                     command or by selecting the View menu and the Join
                     Properties command.




   Procedures
           1. Open the desired query in Design view.
           2. Add a new table to the query, if necessary.
           3. Drag the desired join field from one field list to the matching field in
              a second field list.
           4. Double-click the middle segment of the join line.
           5. Select the desired join type.
           6. Select OK.




    Step-by-Step
           Join tables in a query.

           If necessary, open the Customer Query query in Design view. If the Customer
           Query query does not exist, use the Customer Query 3 query.

            Steps                                           Practice Data
            1. Drag the desired join field from one         Scroll as necessary, and
               field list to the matching field in a        drag the Customer
               second field list.                           Number field in the
               A join line appears between the joined       Customers field list to the
               fields when you release the mouse            Customer ID field in the
               button.                                      Orders field list
            2. Double-click the middle segment of           Double-click the middle
               the join line.                               segment of the join line
               The Join Properties dialog box opens.




Page 180                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 8 - Using Simple Queries



           Steps                                          Practice Data
           3. Select the desired join type.               Click   2
              The join type is selected.
           4. Select OK.                                  Click OK
              The Join Properties dialog box closes.



RUNNING A QUERY

    Discussion
          You can run a query and display its recordset directly from Design view. This option
          is useful for testing the query design to see if its recordset contains the desired
          information.

          Running a query does not save the query design. If you close the recordset after
          running a query, you are prompted to save the changes.



                     You can also run a query by selecting the Query menu and the
                      Run command.


                     After running a query, you can switch back to Design view by
                      clicking the View button on the Query Datasheet toolbar.




   Procedures
          1. Open a query in Design view.


          2. Click the Run button           on the Query Design toolbar.




    Step-by-Step
          Run a query to test its design.

          If necessary, open the Customer Query query in Design view. If the Customer
          Query query does not exist, use the Customer Query 4 query.


Canadian Beaver (CBIT)                                                                 Page 181
Lesson 8 - Using Simple Queries                                           Access 2003 - Lvl 1



            Steps                                         Practice Data
            1. Click the Run button on the Query
               Design toolbar.                            Click
               The query runs and its recordset
               appears in Datasheet view.

           Click the View button on the Query Datasheet toolbar to switch back to Design
           view.

           Close the query and save the changes, if necessary.
           Close WORLD09.MDB.




Page 182                                                            Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 8 - Using Simple Queries




EXERCISE
USING SIMPLE QUERIES

      Task
          Use simple queries.

          1. Open Train09x.
          2. Use the Simple Query Wizard to create a query.
          3. Select the Client table and add the Client ID, Name, and Phone No
             fields.
          4. Name the query Client Names and view its recordset. Then, close
             the Client Names query.
          5. Create a query in Design view.
          6. Add the Client table to the query. Then add the Client ID and Name
             fields to the design grid.
          7. Save the query as Client Query and then close it.
          8. Open the Client Query query to view its recordset. Then, view the
             Client Query in Design view.
          9. Add the Project table to the Client Query query; notice that a join
             line appears automatically.
          10. Change the join type so that the query displays all the records from
              the Client table and only the matching ones from the Project table.
          11. Add the Project ID, Course Name, Start Date, and Cost fields
              from the Project field list to the query.




Canadian Beaver (CBIT)                                                               Page 183
Lesson 8 - Using Simple Queries                                         Access 2003 - Lvl 1




           12. Run the query from Design view.
           13. Close the Client Query query and save the changes.
           14. Close the database file.




Page 184                                                            Canadian Beaver (CBIT)
LESSON 11 -
MODIFYING QUERY RESULTS

In this lesson, you will learn how to:
         Sort a query

         Add criteria to a query

         Hide a field in a query

         Add a record using a query

         Print a query
Lesson 9 - Modifying Query Results                                              Access 2003 - Lvl 1




SORTING A QUERY

    Discussion
           When you run a query, the records in the recordset appear in the same order in which
           they appear in the design grid. You can either sort the recordset or assigning a sort
           order in the query design. You can sort a recordset just as you would sort a table;
           however, you would have to perform the sort every time you run the query. If you
           assign a sort order in the query design, Access will sort the recordset automatically
           each time you run the query.

           You can sort on more than one field. For example, you can sort by region and then by
           states within each region. To sort on more than one field, the first sort field must be to
           the left of the second sort field in the query design grid, since Access sorts from left to
           right.




                                      Adding a sort order to the design grid




   Procedures
           1. Open a query in Design view.
           2. Select the Sort row for the field by which you want to sort.
           3. Select the Sort list.
           4. Select the desired sort option.




    Step-by-Step
           From the Student Data directory, open WORLD10.MDB.
           Sort a query.

Page 186                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 9 - Modifying Query Results



          Display the Queries object list and open the Customer Query query in Design view.

           Steps                                             Practice Data
           1. Select the Sort row in the field by            Click in the Sort row
              which you want to sort.                        under Store Name
              The insertion point and a drop-down
              arrow appear in the Sort field.
           2. Select the Sort list.                          Click Sort
              A list of available sort options
              appears.
           3. Select the desired sort option.                Click Ascending
              The sort option appears in the Sort
              row in the design grid.

          Run the query. Notice that the recordset is sorted in ascending order by the Store
          Name field. Close the recordset and save the changes.

          Practice the Concept: Open the Customers table in Datasheet view. Click the New
          Record button on the Table Datasheet toolbar and create a new record by adding
          data only in the following fields:

                             Field                    Data
                             Customer Number          1025
                             Store Name               Acme Sports
                             Region                   Northeast
                             Sales Rep                SJS
                             Credit Limit             2500
                             Contact Date             3/17

          Close the Customers table and run the Customer Query query. Notice that the new
          record appears in the recordset and is sorted in the correct order. Notice also that there
          is a blank field under the Order Number field. Although there is no order for this
          customer, the outer join between the related tables allows the record to appear.

          Switch to Design view. Double-click the middle segment of the join line, change the
          join type to an inner join by selecting the 1 option, and select OK. Run the query
          again; notice that the Acme Sports record does not appear when the tables are joined
          with an inner join.

          Close the recordset and save the changes.




Canadian Beaver (CBIT)                                                                    Page 187
Lesson 9 - Modifying Query Results                                           Access 2003 - Lvl 1



ADDING CRITERIA TO A QUERY

    Discussion
           You can use the Criteria row in the design grid to restrict the number of records a
           query returns.

           To select records that match a single value, you can enter the value you want to match
           in the Criteria row of the applicable field. Access automatically inserts quotation
           marks (" ") around alphanumeric entries and number symbols (#) around date entries;
           nothing appears around numeric entries. When you run the query, only those records
           with values that match the criteria appear in the recordset.




                                         Adding criteria to a query




                   A field name in the Criteria row must be entered exactly as it
                    appears in the field list.




   Procedures
           1. Open a query in Design view.
           2. Select the Criteria row for the field you want to match.
           3. Type the value you want to match.
           4. Press [Enter].




Page 188                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 9 - Modifying Query Results




    Step-by-Step
          Add criteria to a query to select specific records.

          If necessary, display the Queries object list.

          Open the Customer Query 5 query in Design view.

           Steps                                           Practice Data
           1. Select the Criteria row for the field        Click in the Criteria row
              you want to match.                           under Region
              The insertion point appears in the
              Criteria field.
           2. Type the value you want to match.            Type Southeast
              The text appears in the Criteria row in
              the design grid.
           3. Press [Enter].                               Press [Enter]
              The criterion is entered and Access
              inserts the appropriate characters or
              symbols around it.

          Run the query. Notice that only records in the Southeast region appear in the
          recordset.

          Practice the Concept: Switch to Design view. Delete the “Southeast” criteria under
          the Region field and select the Criteria row under the Order Date field. Enter the
          criteria 5/5/01. Press [Enter]. Notice that Access inserts number symbols around the
          date value. Run the query and scroll as necessary to view the Order Date column.
          Notice that only records with a 5/5/01 date appear in the recordset.

          Switch to Design view and delete the criteria in the Order Date field. Click in the
          Criteria row under the Credit Limit field. Type 5000 and press [Enter]. Notice that
          Access does not insert any symbols around a numeric value. Run the query. Notice
          that only records with a 5000 credit limit appear in the recordset.

          Close the recordset and save the changes.


HIDING A FIELD IN A QUERY

    Discussion
          You can select records that meet specified field criteria without displaying the field in
          the recordset. This option is useful when all the records meet the same specified
          criteria and, as a result, the field does not need to appear.

Canadian Beaver (CBIT)                                                                   Page 189
Lesson 9 - Modifying Query Results                                                Access 2003 - Lvl 1



           For example, you may create a query to display customers in the Southeast region.
           The Region field must be added to the query with a criterion of Southeast; since all
           the data in the Region field would be the same (Southeast), the Region field does not
           need to appear in the recordset. In this case, you can hide the Region field.

           The design grid provides a Show row for each field. If the Show option is selected,
           the field will appear in the recordset; if it is deselected, the field will not appear in the
           recordset. The Show option is selected by default.




   Procedures
           1. Open a query in Design view.
           2. Deselect the Show option in the field you want to hide.




    Step-by-Step
           Hide a query field.

           If necessary, display the Queries object list.

           Open the Customer Query 6 query in Design view.

            Steps                                             Practice Data
            1. Deselect the Show option in the field          Scroll as necessary and
               you want to hide.                              click in the Show row
               The Show option is deselected.                 under Credit Limit to
                                                              deselect it

           Run the query. Notice that the Credit Limit field does not appear in the recordset.
           Close the query and save the changes.


ADDING A RECORD USING A QUERY

    Discussion
           You can use a query to update records in related tables. When you enter data into a
           query recordset, Access automatically completes the recordset data, as applicable, and
           enters the data into the related tables as well. For example, if you run a query using
           the related Orders and Customers tables and you enter a new order into the

Page 190                                                                     Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 9 - Modifying Query Results


          recordset, Access automatically completes the customer information in the recordset
          and enters the data into the Orders table as well.

          You can always edit information in queries based on a single table. In queries based
          on related tables, Access must be able to determine the relationship type (one-to-one
          or one-to-many) in order for you to be able to edit information. In queries based on a
          one-to-one relationship, you can always edit the data. In queries based on a one-to-
          many relationship, you may not always be able to edit the data. For instance, this can
          happen if the join field from the “many” table is not included in the query. For
          example, you cannot add a new customer order to a query recordset based on the
          related Customers and Orders table unless the join field (Customer ID) in the
          Orders table is included in the query.



                     You can use help to get more information about using a query
                      to update data in tables having a one-to-many relationship.




   Procedures
          1. Open a query in Datasheet view.


          2. Click the New Record button            on the Query Datasheet
             toolbar.
          3. Type the desired data into the first field.
          4. Select the next field.
          5. Enter data into each of the remaining fields, as desired.
          6. Press [Enter].




    Step-by-Step
          Add a record to related tables using a query.

          Display the Tables object list. Open the Items table in Datasheet view; notice that
          product 12-1687 has a product description of ball, soccer and a unit price of 16.43.
          Close the Items table.

          Display the Queries object list and open the Line Item query in Datasheet view.




Canadian Beaver (CBIT)                                                                 Page 191
Lesson 9 - Modifying Query Results                                           Access 2003 - Lvl 1



            Steps                                         Practice Data
            1. Click the New Record button on the
               Query Datasheet toolbar.                   Click
               The insertion point appears in a blank
               row at the end of the datasheet.
            2. Type the desired data into the first       Type 1820
               field.
               The data appears in the field.
            3. Select the next field.                     Press [Enter]
               The insertion point moves to the next
               field.
            4. Enter data into each of the remaining      Follow the instructions
               fields, as desired.                        shown below the table
               The data appears in the applicable
               fields.

           Type 12-1687 into the Product ID field and 10 into the Quantity field, pressing
           [Enter] after each entry.

           Close the query. Open the Line Items table in Datasheet view and scroll as necessary
           to record 123 to verify that the data was entered. Then, close the Line Items table.


PRINTING A QUERY

    Discussion
           You can print a query recordset. You can run the query and then print the recordset, or
           you can save time by printing the recordset directly from the Database window. If you
           print the recordset from the Database window, Access runs the query and sends the
           results directly to the printer, rather than to the screen.




   Procedures
           1. Select the Queries object list.
           2. Select the query you want to print.


           3. Click the Print button        on the Database toolbar.




Page 192                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                  Lesson 9 - Modifying Query Results




    Step-by-Step
          Print a query from the Database window.

          Display the Queries object list.

           Steps                                     Practice Data
           1. Select the query you want to print.    Click Customer Query 7
              The query is selected.
           2. Click the Print button on the
              Database toolbar.                      Click
              Access runs the query and prints the
              recordset.

          Close WORLD10.MDB.




Canadian Beaver (CBIT)                                                        Page 193
Lesson 9 - Modifying Query Results                                             Access 2003 - Lvl 1




EXERCISE
MODIFYING QUERY RESULTS

      Task
           Modify query results.

           1. Open Train10x.
           2. Open the Project table in Datasheet view. Scroll to the last record;
              notice that it has a Project ID of 1024, for a Client ID of WENDT.
              Close the Project table.
           3. Open the Client Query query in Design view.
           4. Sort the query in ascending order by the Project ID field. Then, run
              the query.
           5. Add a new record to the recordset, using the following data:

                              Field                 Data
                              Client ID             ABEL
                              Project ID            1025
                              Course Name           INTMA22
                              Start Date            6/19
                              End Date              6/19
                              Cost                  100
                              Trainer Initials      EK


           6. Close the query and save the changes.
           7. Open the Project table in Datasheet view. Scroll to the last record;
              notice the new ABEL record. Then, close the Project table.
           8. Open the Client Query query in Design view.
           9. Add a criterion of 125 to the Cost field. Then, run the query.
           10. Switch back to Design view. Hide the Cost field and run the query
               again. Then, close the Client Query query without saving the
               changes.



Page 194                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                    Lesson 9 - Modifying Query Results


          11. Print the Client Query recordset from the Database window.
          12. Close the database file.




Canadian Beaver (CBIT)                                                          Page 195
LESSON 12 -
USING OPERATORS IN QUERIES

In this lesson, you will learn how to:
         Use comparison operators

         Use an And condition

         Use an Or condition

         Use the Between And operator

         Use the Expression Builder

         Use a wildcard character
Access 2003 - Lvl 1                                                Lesson 10 - Analyzing Tables




USING COMPARISON OPERATORS

    Discussion
          You can add criteria to a query in order to limit the number of records in the recordset.
          The simplest criterion is to find all records matching a single value.

          You can also use comparison operators to limit the recordset to a group of records.
          Comparison operators are symbols that represent conditions recognized by Access.
          The following comparison operators are available in Access:

                             Operator      Description
                                  <        less than
                                 <=        less than or equal to
                                  >        greater than
                                 >=        greater than or equal to
                                  =        equal to
                                 <>        not equal to
                                Not        reverses logic

          You can use one or more comparison operators to compare a specified value to all the
          values in a field. For example, you may want to find all customers with credit limits of
          less than $1000 or all customers with a contract date on or before January 2001; you
          can use a combination of comparison operators and field values to write an expression
          defining the desired criteria (e.g., <1000 or <=1/1/01, respectively).

          When you run the query, only those records with values meeting the specified criteria
          appear in the recordset.




                                         Adding criteria to a query




Canadian Beaver (CBIT)                                                                  Page 197
Lesson 10 - Analyzing Tables                                                 Access 2003 - Lvl 1



                   Access automatically inserts number symbols (#) around date
                    values and quotation marks (" ") around alphanumeric values.
                    Access does not insert any symbols or characters around
                    numeric values.


                   You can also use the Expression Builder to add criteria to a
                    query. To open the Expression Builder, select the Criteria
                    row in the field to which you want to add criteria and click the
                    Build button on the Query Design toolbar or right-click the
                    Criteria row and select the Build command.




   Procedures
           1. Open the desired query in Design view.
           2. Select the Criteria row in the field to which you want to add criteria.
           3. Type the desired comparison operator and value.
           4. Press [Enter].




    Step-by-Step
           From the Student Data directory, open WORLD47.MDB.
           Use comparison operators in a query.

           Open the CSales Query query in Design view.

            Steps                                         Practice Data
            1. Select the Criteria row in the field to    Scroll as necessary and
               which you want to add criteria.            click in the Criteria row
               The insertion point appears in the         of the Credit Limit field
               Criteria row of the corresponding
               field.
            2. Type the desired comparison operator       Type <=5000
               and value.
               The criteria appears in the Criteria
               row.
            3. Press [Enter].                             Press [Enter]
               The criteria is entered into the design
               grid.


Page 198                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                 Lesson 10 - Analyzing Tables



          Run the query. Notice that only records with a credit limit of $5,000 or less appear in
          the recordset.

          Practice the Concept: Return to Design view and delete the criteria. Find all records
          of customers with contract dates on or after January 1, 2000, by adding >=1/1/00 to
          the Criteria row of the Contract Date field and pressing [Enter]. Notice that Access
          inserts number symbols (#) around the value to indicate a date criterion. Run the
          query. Notice that only those records that match the criterion appear in the recordset.

          Return to Design view and delete the criterion.


USING AN AND CONDITION

    Discussion
          Many times, a query requires more than one condition to obtain the desired result. If
          you want to find all customers in PA with sales to date of over $10,000, for example,
          you need two conditions: State=PA and Sales to Date>10000. The records must meet
          both conditions in order to be included in the recordset. When two or more criteria
          must be met, you are creating an And condition. An And condition is created using the
          And logical operator.

          You can create an And condition by adding multiple criteria to a single field. In order
          to do this, you must use the And operator to find records that fall within a range. For
          example, to find customers whose contract dates fall between 9/1/99 and 9/30/99, you
          would type both criteria on a single line in the Criteria row of the appropriate field
          (i.e., >=9/1/99 And <=9/30/99 in the Contract Date field).

          An And condition is assumed when you enter conditions in the same Criteria row of
          two or more different fields; in this case, the And operator is unnecessary. For
          example, to find customers in PA with sales to date of over $10,000, you would type
          each criterion in the same Criteria row of the appropriate fields (i.e., =PA in the first
          Criteria row of the State/Province field and >10000 in the first Criteria row of the
          Sales to Date field).



                     Criteria entered in the same row of the design grid create an
                      And condition, and criteria entered in different rows create an
                      Or condition.


                     You can also use the Expression Builder by selecting the
                      desired Criteria row and clicking the Build button on the
                      Query Design toolbar or by right-clicking the Criteria row
                      and selecting the Build command.




Canadian Beaver (CBIT)                                                                    Page 199
Lesson 10 - Analyzing Tables                                                Access 2003 - Lvl 1




   Procedures
           1. Open the desired query in Design view.
           2. Select the Criteria row of the desired field.
           3. Type the desired criterion.
           4. Type the And operator and an additional criterion in the same
              Criteria row and field or type additional criteria in the same
              Criteria row of one or more other fields.
           5. Press [Enter].




    Step-by-Step
           Use an AND condition in a query.

           If necessary, open the CSales Query query in Design view and delete any previous
           criteria.

            Steps                                         Practice Data
            1. Select the Criteria row of the desired     Click in the Criteria row
               field.                                     of the Credit Limit field
               The insertion point appears in the
               Criteria row of the corresponding
               field.
            2. Type the desired criterion.                Type >=6000
               The criterion appears in the design
               grid.
            3. Type the And operator and an               Type and <=8000
               additional criterion in the same
               Criteria row and field or type
               additional criteria in the same Criteria
               row of one or more other fields.
               The additional criterion appears in the
               design grid.
            4. Press [Enter].                             Press [Enter]
               The And condition is entered into the
               design grid.

           Run the query. Notice that only records that match the And condition appear in the
           recordset.


Page 200                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                 Lesson 10 - Analyzing Tables


          Practice the Concept: Return to Design view and delete the criteria. To find all
          records of customers with contract dates on or after 1/1/00 and sales to date of $4,000
          or more, type >=1/1/00 in the first Criteria row of the Contract Date field and
          >=4000 in the first Criteria row of the Sales to Date field. Run the query. Notice that
          only records that match both And conditions appear in the recordset.

          Return to Design view and delete the criteria.


USING AN OR CONDITION

    Discussion
          There are times you may want to find records that meet only one of several specified
          conditions. This is called an Or condition. If you want to find all customers in PA or
          all customers with sales to date of over $10,000, for example, you would need two
          conditions: State=PA and Sales to Date>10000. A record needs to meet only one of
          the conditions in order to be included in the recordset.

          You can create an Or condition in a single field by entering criteria in different
          Criteria rows of that field. You can also create an Or condition by typing criteria in
          different Criteria rows of two or more fields. For example, to find all customers with
          contract dates on or before January 1, 2001 or all those with credit limits above
          $3,000, you would type <=1/1/01 in the Criteria row of the Contract Date field and
          >3000 in the or row of the Credit Limit field.

          You can create additional Or and And conditions by typing criteria into the Criteria
          row, the or row, or any row below the or row. Any criteria entered into the same
          Criteria row creates an And condition, any criteria entered into different rows creates
          an Or condition, and you can even create a combination of And and Or conditions.




                                           Creating an Or condition




                     Criteria entered in the same Criteria row across the design
                      grid create an And condition, and criteria entered in different
                      Criteria rows create an Or condition.



Canadian Beaver (CBIT)                                                                  Page 201
Lesson 10 - Analyzing Tables                                                 Access 2003 - Lvl 1



                   You can also add criteria using the Expression Builder by
                    selecting the desired Criteria row and clicking the Build
                    button on the Query Design toolbar or by right-clicking the
                    Criteria row and selecting the Build command.


                   You can also create an Or condition for a single field in the
                    Criteria row using the Or operator. Typing Northeast Or
                    Southeast Or Central into the Criteria row of the Region
                    field finds all records in any one of the three regions.




   Procedures
           1. Open the desired query in Design view.
           2. Select the Criteria row of the desired field.
           3. Type the desired criterion.
           4. Select the or row under the same field.
           5. Type the second criterion.
           6. Press [Enter].




    Step-by-Step
           Use an Or condition in a query.

           If necessary, open the CSales Query query in Design view and delete any previous
           criteria.

            Steps                                         Practice Data
            1. Select the Criteria row of the desired     Click in the Criteria row
               field.                                     of the Sales Rep field
               The insertion point appears in the
               corresponding Criteria row.
            2. Type the desired criterion.                Type sjs
               The criterion appears in the Criteria
               row.
            3. Select the or row of the desired field.    Click in the or row of the
               The insertion point appears in the or      Region field
               row.


Page 202                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                  Lesson 10 - Analyzing Tables



           Steps                                             Practice Data
           4. Type the second criterion.                     Type southeast
              The second criterion appears in the or
              row.
           5. Press [Enter].                                 Press [Enter]
              The Or condition is entered into the
              design grid.

          Run the query. Notice that records matching either Or condition appear in the
          recordset.

          Return to Design view and delete all criteria.


USING THE BETWEEN AND OPERATOR

    Discussion
          You can use the Between And operator to find data that includes or falls between two
          stated values. To find all records of customers with credit limits between 1000 and
          2000, for example, you would enter Between 1000 And 2000 in the Criteria row of
          the Credit Limit field.

          The Between And operator can be used in text, numeric, or date fields.



                     The Between And operator is inclusive; all records with
                      values that include or fall between the stated criteria are
                      included in the recordset.


                     You can also use the Expression Builder by selecting the
                      desired Criteria row and clicking the Build button on the
                      Query Design toolbar or by right-clicking in the Criteria row
                      and selecting the Build command.




                     Spaces must be included between the criteria and the words
                      Between and And. If you do not include the proper spacing,
                      the Data type mismatch in criteria expression error message
                      will appear.




Canadian Beaver (CBIT)                                                                  Page 203
Lesson 10 - Analyzing Tables                                                  Access 2003 - Lvl 1




   Procedures
           1. Open the desired query in Design view.
           2. Select the Criteria row of the desired field.
           3. Type Between, the first value in the range, And, and the last value in
              the range.
           4. Press [Enter].




    Step-by-Step
           Use the Between And operator in a query.

           If necessary, open the CSales Query query in Design view and delete any previous
           criteria.

            Steps                                           Practice Data
            1. Select the Criteria row of the desired       Click in the Criteria row
               field.                                       of the Credit Limit field
               The insertion point appears in the
               corresponding Criteria row.
            2. Type Between, the first value in the         Type between 1000 and
               range, And, and the last value in the        2000
               range.
               The operator and the criteria appear
               in the Criteria row.
            3. Press [Enter].                               Press [Enter]
               The Between And operator and the
               criteria are entered into the design
               grid.

           Run the query. Notice that only records matching the criteria appear in the recordset.

           Return to Design view and delete the criteria.




Page 204                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                Lesson 10 - Analyzing Tables



USING THE EXPRESSION BUILDER

    Discussion
          When you enter criteria, you are actually creating an expression. Expressions are
          calculations and can include database objects, operators, and values. Objects range
          from table fields to controls in a form. Operators are standard mathematical operators
          used in calculations (such as +, -, *, /, (), <>). Values can be numbers, dates, text, and
          built-in functions, as well as field, control, and property identifiers.

          You can create an expression by typing the expression elements, or you can use the
          Expression Builder. The Expression Builder is a tool that provides all the elements
          needed to build the expression.

          The Expression Builder displays the Expression box in its top pane, a row of operator
          buttons below the Expression box, and three lower panes that display categories,
          subcategories, and values, respectively.

          New expressions appear in the Expression box. You can use a combination of
          methods to build a new expression. You can type some elements of the expression and
          select others (such as operators, functions, and values) from the element panes. If you
          make a mistake, the Undo button allows you to undo previous actions, one at a time.
          Additionally, you can select and delete any elements you want to remove from the
          Expression box.




                                        Using the Expression Builder




Canadian Beaver (CBIT)                                                                    Page 205
Lesson 10 - Analyzing Tables                                                   Access 2003 - Lvl 1




                    The Expression Builder is available in Design view for any
                     database object for which you need to create an expression.
                     For example, you can use the Expression Builder to create a
                     validation rule for a table field.


                    You can insert an element from an element pane into the
                     Expression box by double-clicking the element or by
                     selecting it and clicking the Paste button in the Expression
                     Builder.




                    Be careful to single-click when expanding a subcategory;
                     double-clicking a subcategory inserts the first subcategory
                     value.




   Procedures
           1. Open the desired query in Design view.
           2. Select the Criteria row under the desired field.


           3. Click the Build button          on the Query Design toolbar.
           4. Expand categories in the lower, left pane as necessary, and then click
              any category to display its subcategories in the center pane.
           5. Click the desired subcategory in the center pane.
           6. Double-click a subcategory in the center pane or a value in the right
              pane to insert it into the Expression box.
           7. To enter a value, select existing text or position the insertion point as
              needed in the Expression box.
           8. Type the desired value.
           9. Complete the expression.
           10. Select OK.




    Step-by-Step
           Use the Expression Builder.


Page 206                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                               Lesson 10 - Analyzing Tables



          If necessary, open the CSales Query query in Design view and delete any previous
          criteria.

           Steps                                          Practice Data
           1. Select the Criteria row of the desired      Click in the Criteria row
              field.                                      of the Contract Date field
              The insertion point appears in the
              corresponding Criteria row.
           2. Click the Build button on the Query
              Design toolbar.                             Click
              The Expression Builder opens.
           3. Expand categories in the lower, left        Click Operators
              pane as necessary, and then click any
              category to display its subcategories in
              the center pane.
              The corresponding subcategories
              appear in the center pane.
           4. Click the desired subcategory in the        Click Comparison
              center pane.
              The corresponding values appear in
              the right pane.
           5. Double-click a subcategory in the        Double-click Between in
              center pane or a value in the right pane the right pane
              to insert it into the Expression box.
              The element appears in the Expression
              box.
           6. To enter a value, select existing text or   Click the first <Expr> in
              position the insertion point as needed      the Expression box
              in the Expression box.
              The text in the Expression box is
              selected.
           7. Type the desired value.                     Type 6/1/99
              The text appears in the Expression
              box.
           8. Complete the expression.                    Follow the instructions
              The expression appears in the               shown below the table
              Expression box.                             before continuing on to
                                                          the next step
           9. Select OK.                                  Click OK
              The Expression Builder dialog box
              closes, and the expression appears in
              the Criteria row.


Canadian Beaver (CBIT)                                                                 Page 207
Lesson 10 - Analyzing Tables                                                    Access 2003 - Lvl 1



           Replace the second <Expr> element with 6/30/00.

           Return to the table and continue on to the next step (step 9).

           Run the query. Notice that only records matching the criteria appear in the recordset.

           Return to Design view and delete the criteria.


USING A WILDCARD CHARACTER

    Discussion
           You can use a wildcard in a query in place of one or several characters. Wildcard
           characters are helpful when you want to find criteria with a pattern (such as all last
           names beginning with M), or if you are not sure exactly how values you want to find
           appear (such as the correct spelling—Kline or Klein).

           The two most common wildcards are listed in the following table:

            Wildcard        Used for             Example
                  ?         Any single letter    Sm?th finds Smith and Smyth,
                            or number            whereas ?andy finds Sandy, Randy,
                                                 etc.
                  *         One or more          M* finds all records that start with
                            letters or           M; 8/*/00 finds all dates in August,
                            numbers              2000; and *ball* finds all records
                                                 that have the word ball anywhere in
                                                 the field




                     Wildcards are not case-sensitive (for example, *ill finds Bill
                      and bill).


                     When you use wildcard characters (? and *), Access
                      automatically inserts the word Like before the criteria and
                      quotation marks (“ ”) around text.


                     You can also use the Expression Builder by selecting the
                      desired Criteria row and clicking the Build button on the
                      Query Design toolbar or by right-clicking the Criteria row
                      and selecting the Build command.




Page 208                                                                    Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                               Lesson 10 - Analyzing Tables




   Procedures
          1. Open the desired query in Design view.
          2. Select the Criteria row of the desired field.
          3. Type the desired criteria, using wildcards as appropriate.
          4. Press [Enter].




    Step-by-Step
          Use a wildcard character in a query.

          If necessary, open the CSales Query query in Design view and delete any previous
          criteria.

           Steps                                           Practice Data
           1. Select the Criteria row of the desired       Click in the Criteria row
              field.                                       of the Store Name field
              The insertion point appears in the
              corresponding Criteria row.
           2. Type the desired criteria, using             Type sport*
              wildcards as appropriate.
              The text appears in the Criteria row.
           3. Press [Enter].                               Press [Enter]
              The criteria is entered into the design
              grid.

          Run the query. Notice that only records matching the criteria appear in the recordset.

          Return to Design view and delete all criteria.

          Then, close the query, saving the changes.
          Close WORLD47.MDB.




Canadian Beaver (CBIT)                                                                  Page 209
Lesson 10 - Analyzing Tables                                                  Access 2003 - Lvl 1




EXERCISE
USING OPERATORS IN QUERIES

      Task
           Use operators in queries.

           1. Open Train47x.
           2. Open the Course Query query in Design view.
           3. Set criteria to find only projects with a cost of more than $150.
              (Hint: Try using the > operator.)
           4. Run the query; the criteria should find 16 records. Then, return to
              Design view and delete the criterion.
           5. Use wildcards and an Or condition to find only records with the text
              excel or word occurring anywhere in the course name. (Hint: Use
              asterisks both before and after each criterion.)
           6. Run the query; the criteria should find 7 records. Then, return to
              Design view and delete the criteria from the query.
           7. Use the Expression Builder to find only records with a start date
              between 4/1/2001 and 6/30/2001. (Hint: Select Operators from the
              left pane, Comparison from the center pane, and Between from the
              right pane. Then, replace the <Expr> expressions with the
              appropriate dates.)
           8. Run the query; the criteria should find 12 records. Then, switch back
              to Design view and delete the criterion.
           9. Find all records with trainer initials of DF and a cost of $175 or
              more. The criteria should find 5 records.




Page 210                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                  Lesson 10 - Analyzing Tables




          10. Close the query, saving the changes.
          11. Close the database file.




Canadian Beaver (CBIT)                                                  Page 211
LESSON 13 -
ANALYZING TABLES

In this lesson, you will learn how to:
         Analyze a table

         Use relationships in splits

         Use the Table Analyzer Wizard

         Use the Performance Analyzer
Access 2003 - Lvl 1                                                 Lesson 10 - Analyzing Tables




ANALYZING A TABLE

    Discussion
          When you create or analyze a table, you should look at its structure to determine the
          efficiency of its design. If the same information appears in multiple records, the table
          can probably be split into two related tables. Splitting a table is more efficient because
          the tables are then smaller and, as a result, easier to manage. In addition, information
          will only need to be entered one time. Smaller tables also reduce the chance of making
          data entry errors.

          If you feel that a table may need to be split, you can use the Table Analyzer Wizard to
          analyze it for you and suggest which fields should be in each table. The Table
          Analyzer Wizard can also create the correct table relationships.



USING RELATIONSHIPS IN SPLITS

    Discussion
          When the Table Analyzer Wizard splits a table, it creates the necessary relationships
          in the resulting tables. These relationships allow data from all the tables to be used in
          queries, forms, and reports.

          Access provides two basic types of relationships: one-to-one and one-to-many. When
          a table is split, a one-to-many relationship is usually created. This relationship allows
          one record in the first table to be related to many records in the other related tables,
          eliminating data duplication.



USING THE TABLE ANALYZER WIZARD

    Discussion
          The Table Analyzer Wizard looks at a table to determine if it contains duplicated
          information. If the Table Analyzer Wizard determines that a table has a lot of
          duplicated data, it splits the table, creating new tables and leaving the original table
          intact.

          The Table Analyzer Wizard has seven pages. The first two pages are informational,
          with the first page describing the problem of duplicate data in the table and the second
          describing the solution. You can choose not to display these pages.

Canadian Beaver (CBIT)                                                                     Page 213
Lesson 10 - Analyzing Tables                                                    Access 2003 - Lvl 1



           You then select the table you want to analyze. On subsequent pages, you indicate
           whether you want the wizard to decide how to split the table or whether you want to
           do it yourself. If you allow the wizard to split the table, the recommended tables and
           the relationships between them appear. You can then move fields between tables or
           even drag fields to create new tables. Each piece of information should be stored in
           only one location, and each table should contain only data that refers to a single
           subject.

           Once the fields have been put into the correct tables, you can set the primary keys. In
           order to be related properly, each table must have a primary key, and the primary key
           must be a field that uniquely identifies each record. Therefore, a primary key field
           cannot contain duplicate data. Access will generate unique fields, if necessary.

           On the last page, you can allow the Table Analyzer Wizard to create a query. The
           query will be given the same name as the old table and the old table will be renamed.
           As a result, any reports, forms, or queries that refer to the old table can still be used.




                                          The Table Analyzer Wizard




                    You can also activate the Table Analyzer Wizard by selecting
                     the Tools menu, pointing to the Analyze command, and
                     selecting the Table command.


                    You may need to install the Additional Wizards feature in
                     order to use the Table Analyzer Wizard.




Page 214                                                                   Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                  Lesson 10 - Analyzing Tables




   Procedures
          1. Click the arrow on the Analyze button                 on the Database
             toolbar.
          2. Select the Analyze Table command.
          3. Select Next >.
          4. Select Next >.
          5. Select the table you want to analyze.
          6. Select Next >.
          7. Select whether or not you want to allow the Table Analyzer Wizard
             to decide which fields are put in each table.
          8. Select Next >.
          9. Double-click the title bar of the first table.
          10. Type the desired table name.
          11. Select OK.
          12. Double-click the title bar of the second table.
          13. Type the desired table name.
          14. Select OK.
          15. Select Next >.
          16. Select the field you want to set as a primary key.

          17. Click the Set Unique Identifier button            at the top of the Table
              Analyzer Wizard window.
          18. Select Next >.
          19. Select whether or not you want the Table Analyzer Wizard to create
              a query for you.
          20. Select Finish.
          21. Close the Microsoft Access Help window, if necessary.




    Step-by-Step
          From the Student Data directory, open WORLD11.MDB.


Canadian Beaver (CBIT)                                                                    Page 215
Lesson 10 - Analyzing Tables                                                  Access 2003 - Lvl 1


           Use the Table Analyzer Wizard.

           If necessary, display the Tables object list.

            Steps                                          Practice Data
            1. Click the arrow on the Analyze button       Click the arrow on
               on the Database toolbar.
               A menu of available commands
               appears.
            2. Select the Analyze Table command.           Click Analyze Table
               The Table Analyzer Wizard opens.
            3. Select Next >.                              Click Next >
               The next page of the Table Analyzer
               Wizard appears.
            4. Select Next >.                              Click Next >
               The next page of the Table Analyzer
               Wizard appears.
            5. Select the table you want to analyze.       Click Order Items
               The table name is selected.
            6. Select Next >.                              Click Next >
               The next page of the Table Analyzer
               Wizard appears.
            7. Select whether or not you want to           Click Yes, let the
               allow the Table Analyzer Wizard to          wizard decide., if
               decide which fields are put in each         necessary
               table.
               The option is selected.
            8. Select Next >.                              Click Next >
               The next page of the Table Analyzer
               Wizard appears.
            9. Double-click the title bar of the first     Double-click the Table1
               table.                                      title bar
               A Table Analyzer Wizard dialog box
               opens with the text in the Table Name
               box selected.
            10. Type the desired table name.               Type Line Items
                The name appears in the Table Name
                box.
            11. Select OK.                                 Click OK
                The Table Analyzer Wizard dialog box
                closes, and the new name appears in
                the table title bar.


Page 216                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                              Lesson 10 - Analyzing Tables



           Steps                                          Practice Data
           12. Double-click the title bar of the second   Double-click the Table2
               table.                                     title bar
               A Table Analyzer Wizard dialog box
               opens with the text in the Table Name
               box selected.
           13. Type the desired table name.               Type Items
               The name appears in the Table Name
               box.
           14. Select OK.                                 Click OK
               The Table Analyzer Wizard dialog box
               closes, and the new name appears in
               the table title bar.
           15. Select Next >.                             Click Next >
               The next page of the Table Analyzer
               Wizard appears.
           16. Select the field you want to set as a      Click Product ID in the
               primary key.                               Items table, if necessary
               The field is selected.
           17. Click the Set Unique Identifier button
               at the top of the Table Analyzer        Click
               Wizard window.
               A key symbol appears to the left of the
               selected field.
           18. Select Next >.                             Click Next >
               The next page of the Table Analyzer
               Wizard appears.
           19. Select whether or not you want the         Click No, don‟t create
               Table Analyzer Wizard to create a          the query., if necessary
               query for you.
               The option is selected.
           20. Select Finish.                             Click Finish
               The Table Analyzer Wizard closes, the
               new tables are created, the old table is
               renamed, and a Microsoft Access Help
               window opens.
           21. Close the Microsoft Access Help            Click   on the Microsoft
               window, if necessary.                      Access Help window title
               The Microsoft Access Help window           bar
               closes, and the new tables appear.

          Close the Items and Line Items tables. Then, click the Restore Up button on the
          Database window title bar to restore the window.

Canadian Beaver (CBIT)                                                                Page 217
Lesson 10 - Analyzing Tables                                                   Access 2003 - Lvl 1



USING THE PERFORMANCE ANALYZER

    Discussion
           The Performance Analyzer analyzes the efficiency of your database in order to
           optimize its performance.

           The Performance Analyzer window contains a page for each available object type in
           Access, as well as a Current Database page and an All Object Types page. The
           Current Database page allows you to analyze features that are not objects, such as
           relationships. You can analyze a single object or multiple objects on different pages. If
           you are analyzing different types of objects, the All Object Types page allows you to
           view all database objects in one location.

           The results of the performance analysis appear in the Analysis Results box. The
           results are categorized as a Recommendation, Suggestion, or Idea. The Analysis
           Notes box provides additional information about the selected result. You can use the
           Optimize button to have Access perform the action suggested in a Recommendation
           or Suggestion; you must manually perform the action for an Idea.




                                        Performance Analyzer results




                    After you have selected the Optimize button, the Performance
                     Analyzer marks the action as Fixed.


                    You can use the Select All button to select all the results in the
                     Analysis Results box.


                    The Additional Wizards feature may need to be installed in
                     order to use the Performance Analyzer.




Page 218                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                              Lesson 10 - Analyzing Tables




   Procedures
          1. Click the arrow on the Analyze button            on the Database
             toolbar.
          2. Select the Analyze Performance command.
          3. Select the tab containing the objects you want to analyze.
          4. Select the objects you want to optimize.
          5. Select OK.
          6. To view an explanation, click any result in the Analysis Results box.
          7. Select Close.




    Step-by-Step
          Use the Performance Analyzer to analyze the efficiency of a database.

           Steps                                         Practice Data
           1. Click the arrow on the Analyze button      Click the arrow on
              on the Database toolbar.
              A menu of available commands
              appears.
           2. Select the Analyze Performance             Click Analyze
              command.                                   Performance
              The Performance Analyzer opens.
           3. Select the tab containing the objects      Click the All Object
              you want to analyze.                       Types tab
              The corresponding object types page
              appears.
           4. Select the object you want to optimize.    Click    Customers
              The object is selected.
           5. Select additional objects as desired.      Click    Orders
              The objects are selected.
           6. Select OK.                                 Click OK
              The Performance Analyzer displays its
              recommendations in the Analysis
              Results box.



Canadian Beaver (CBIT)                                                               Page 219
Lesson 10 - Analyzing Tables                                                Access 2003 - Lvl 1



            Steps                                        Practice Data
            7. To view an explanation, click any         Click Table „Orders‟:
               result in the Analysis Results box.
               An explanation of the selected
               recommendation appears in the
               Analysis Notes box.
            8. Select Close.                             Click Close
               The Performance Analyzer closes.

           Display the Tables objects list, if necessary, and open the Customers table in Design
           view. Change the data type for the Customer Number field to Number and close and
           save the Customers table. Open the Orders table in Design view. Change the data
           type for the Customer ID field to Number. Then, close and save the Orders table.

           Practice the Concept: Open the Performance Analyzer, select all objects on the
           Tables page, and run the Performance Analyzer. Notice that the Performance
           Analyzer now has no suggestions for the selected objects. Select OK to close the
           Performance Analyzer message box.
           Close WORLD11.MDB.




Page 220                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                                Lesson 10 - Analyzing Tables




EXERCISE
ANALYZING TABLES

      Task
          Use the Table Analyzer Wizard to analyze tables.

          1. Open Train11x.
          2. Open the Table Analyzer Wizard. Select Next as needed and then
             select the Project List table. Select Next.
          3. Allow the wizard to decide how to split the table.
          4. Rename Table1 to Project and Table2 to Client.
          5. Drag the State field from the Project field list below the City field
             in the Client field list.
          6. Set the Client ID field as the primary key in the Client table.
          7. Do not have the Table Analyzer Wizard create a query.
          8. Select Finish.
          9. Close the Microsoft Access Help window. View the tables and then
             close them and restore the Database window.
          10. Analyze the performance of the Client and Payment tables. (Notice
              that the analysis result does not fit the data; changing the Zip field to
              a long integer field would not allow for zip codes with leading
              zeroes, such as 08054.)
          11. Close the Performance Analyzer.
          12. Close the database file.




Canadian Beaver (CBIT)                                                                    Page 221
LESSON 14 -
CREATING BASIC FORMS

In this lesson, you will learn how to:
         Use forms

         Use the Form Wizard

         View records in a form

         Print records in a form

         Base a form on a query

         Use AutoForm

         Add a record using a form
Access 2003 - Lvl 1                                          Lesson 11 - Creating Basic Forms




USING FORMS

    Discussion
          Forms, like datasheets, can be used for viewing and editing data. However, they can
          also be used to present data in a more attractive format. Forms are usually designed to
          display all the fields for a single record within the form window, eliminating the need
          to scroll. You can also display data from related tables in one form.

          Access provides six basic types of forms: columnar, tabular, datasheet, justified,
          PivotTable and PivotChart. In a columnar form, the field names are listed on the left
          side of the form and the field values appear in a column on the right. If space permits,
          there can be more than one column.

          In a tabular form, the field names are listed across the top of the form and the values
          appear in the corresponding columns beneath them. A datasheet form appears similar
          to Datasheet view. Both the tabular and datasheet forms display data in a table layout.

          In a justified form, the field names and values appear in alternate rows, evenly spaced
          across the page. Field values appear under the corresponding field names.

          A PivotTable form allows you to quickly create a form by dragging fields as desired
          in PivotTable view. Similar to a PivotTable form, a PivotChart form allows you to
          create a chart or graph by dragging fields as desired in PivotChart view.



USING THE FORM WIZARD

    Discussion
          You can use the Form Wizard to quickly and easily create a form. The Form Wizard
          guides you through the process. First, you must choose the table or query on which
          you want to base the form, and then you can select the fields you want to include.
          Next, you select the desired type of form layout: columnar, tabular, datasheet,
          justified, PivotTable, or PivotChart. You can then select a style from a variety of
          predefined styles provided by Access. Finally, you must name the form.




Canadian Beaver (CBIT)                                                                  Page 223
Lesson 11 - Creating Basic Forms                                              Access 2003 - Lvl 1




                                       The Customer Data Entry form




                    If you base a form on multiple tables, the tables must all be
                     related. In addition, the Form Wizard will prompt you to select
                     how you want to group the fields on the form.


                    You can also activate the Form Wizard by double-clicking the
                     Create form by using wizard option in the Forms object list
                     in the Database window, by selecting the Insert menu and the
                     Form command, or by clicking the New Object button on the
                     Database toolbar and selecting the Form command.




   Procedures
           1. Display the Forms object list.

           2. Select the       New button on the Database window toolbar.
           3. Select Form Wizard.
           4. Select OK.
           5. Select the Tables/Queries list.
           6. Select the table or query on which you want to base the form.
           7. Add the desired fields to the Selected Fields list box, or add all the
              fields.
           8. Select Next >.
           9. Select the desired form layout.


Page 224                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                       Lesson 11 - Creating Basic Forms


          10. Select Next >.
          11. Select the desired form style.
          12. Select Next >.
          13. Type the desired form name.
          14. Select Finish.




    Step-by-Step
          From the Student Data directory, open WORLD12.MDB.
          Use the Form Wizard.

           Steps                                        Practice Data
           1. Display the Forms object list.            Click Forms
              The Forms objects appear.
           2. Select the New button on the Database
              window toolbar.                           Click      New
              The New Form dialog box opens.
           3. Select Form Wizard.                       Click Form Wizard
              Form Wizard is selected.
           4. Select OK.                                Click OK
              The New Form dialog box closes, and
              the Form Wizard opens.
           5. Select the Tables/Queries list.           Click Tables/Queries
              A list of tables and queries appears.
           6. Select the table or query on which you Click Table: Customers,
              want to base the form.                   if necessary
              The table or query is selected, and the
              fields in the selected table or query
              appear in the Available Fields list box.
           7. Add the desired fields to the Selected
              Fields list box, or add all the fields.   Click
              The fields appear in the Selected
              Fields list box.
           8. Select Next >.                            Click Next >
              The next page of the Form Wizard
              appears.




Canadian Beaver (CBIT)                                                           Page 225
Lesson 11 - Creating Basic Forms                                             Access 2003 - Lvl 1



            Steps                                         Practice Data
            9. Select the desired form layout.            Click Columnar, if
               A preview of the layout appears in the     necessary
               Form Wizard.
            10. Select Next >.                            Click Next >
                The next page of the Form Wizard
                appears.
            11. Select the desired form style.            Click Blueprint
                A preview of the style appears in the
                Form Wizard.
            12. Select Next >.                            Click Next >
                The next page of the Form Wizard
                appears, and the text in the What title
                do you want for your form? box is
                selected.
            13. Type the desired form name.               Type Customer Data
                The name appears in the What title do     Entry
                you want for your form? box.
            14. Select Finish.                            Click Finish
                The Form Wizard closes, and the new
                form opens.



VIEWING RECORDS IN A FORM

    Discussion
           Navigating records in a form is similar to navigating records in Datasheet view. You
           can use the navigation buttons at the bottom of the window to display records in a
           form. The following navigation buttons are available in Form view:

                            Button      Description
                                        Displays the first record
                                        Displays the previous record
                                        Displays the next record
                                        Displays the last record




Page 226                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 11 - Creating Basic Forms



                     You can also press the [Ctrl+Home] key combination to
                      display the first record, the [Ctrl+End] key combination to
                      display the last record, and the [Page Up] and [Page Down]
                      keys display the previous and next records, respectively.


                     You can also go directly to a record by selecting the number in
                      the Record box (located within the navigation buttons at the
                      bottom of the window), typing the number of the record you
                      want to display, and pressing the [Enter] key.




   Procedures
          1. Display the Forms object list.
          2. Open a form in Form view.

          3. Click the Last Record button           at the bottom of the form
             window.

          4. Click the First Record button          at the bottom of the form
             window.

          5. Click the Next Record button           at the bottom of the form
             window.

          6. Click the Previous Record button           at the bottom of the form
             window.




    Step-by-Step
          View records in a form.

          If necessary, open the Customer Data Entry form in Form view. If the Customer
          Data Entry form does not exist, use the Customer Data Entry 2 form.

           Steps                                           Practice Data
           1. Click the Last Record button at the          Click
              bottom of the form window.
              The last record appears.




Canadian Beaver (CBIT)                                                                  Page 227
Lesson 11 - Creating Basic Forms                                               Access 2003 - Lvl 1



            Steps                                           Practice Data
            2. Click the First Record button at the         Click
               bottom of the form window.
               The first record appears.
            3. Click the Next Record button at the          Click
               bottom of the form window.
               The next record appears.
            4. Click the Previous Record button at          Click
               the bottom of the form window.
               The previous record appears.



PRINTING RECORDS IN A FORM

    Discussion
           You can print an open form.

           If you use the Print button to print all the data, Access fits as many whole forms as
           possible on each page; Access will not break a form in the middle. If a form is large
           and there are numerous records in it, you may be using a large amount of paper and
           printer time.

           If you use the Print dialog box, you can print all records, only specified pages, or all
           selected records, thereby saving time and paper.



                    You can also open the Print dialog box by right-clicking an
                     object in the Database window and selecting the Print
                     command.




   Procedures
           1. Display the Forms object list.
           2. Open a form in Form view.
           3. Select the File menu.
           4. Select the Print command.
           5. Select the desired options.


Page 228                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 11 - Creating Basic Forms


          6. Select OK.




    Step-by-Step
          Print records in a form.

          If necessary, open the Customer Data Entry form in Form view. If the Customer
          Data Entry form does not exist, use the Customer Data Entry 2 form.

           Steps                                          Practice Data
           1. Select the File menu.                       Click File
              The File menu appears.
           2. Select the Print command.                   Click Print...
              The Print dialog box opens.
           3. Select the desired options.                 Click Selected
              The option is selected.                     Record(s)
           4. Select OK.                                  Click OK
              The Print dialog box closes, and
              Access prints the records.

          Close the form.


BASING A FORM ON A QUERY

    Discussion
          Forms can extract information from a query as well as from a table.

          If the query recordset cannot be updated, you will not be able to edit the form or add
          records to it. Nevertheless, a form can be used to present query data in a more
          attractive manner.



                     You can also activate the Form Wizard by double-clicking the
                      Create form by using wizard option in the Forms object list
                      in the Database window, by selecting the Insert menu and
                      then selecting the Form command, or by clicking the New
                      Object button on the Database toolbar and selecting the
                      Form command.



Canadian Beaver (CBIT)                                                                  Page 229
Lesson 11 - Creating Basic Forms                                              Access 2003 - Lvl 1




   Procedures
           1. Display the Forms object list.

           2. Select the        New button on the Database window toolbar.
           3. Select Form Wizard.
           4. Select OK.
           5. Select the Tables/Queries list.
           6. Select the table or query on which you want to base the form.
           7. Add the desired fields to the Selected Fields list box, or add all the
              fields.
           8. Select Next >.
           9. Select the desired form layout.
           10. Select Next >.
           11. Select the desired form style.
           12. Select Next >.
           13. Type the desired form name.
           14. Select Finish.




    Step-by-Step
           Base a form on a query.

           If necessary, display the Forms object list.

            Steps                                          Practice Data
            1. Select the New button on the Database
               window toolbar.                             Click       New
               The New Form dialog box opens.
            2. Select Form Wizard.                         Click Form Wizard
               Form Wizard is selected.
            3. Select OK.                                  Click OK
               The New Form dialog box closes, and
               the Form Wizard opens.



Page 230                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 11 - Creating Basic Forms



           Steps                                         Practice Data
           4. Select the Tables/Queries list.            Click Tables/Queries
              A list of available tables and queries
              appears.
           5. Select the table or query on which you     Click Query: Order
              want to base the form.                     Items
              The table or query is selected, and the
              fields in it appear in the Available
              Fields list box.
           6. Add the desired fields to the Selected
              Fields list box, or add all the fields.    Click
              The fields appear in the Selected
              Fields list box.
           7. Select Next >.                             Click Next >
              The next page of the Form Wizard
              appears.
           8. Select the desired form layout.            Click   Tabular
              A preview of the layout appears in the
              Form Wizard.
           9. Select Next >.                             Click Next >
              The next page of the Form Wizard
              appears.
           10. Select the desired form style.            Click Stone
               A preview of the style appears in the
               Form Wizard.
           11. Select Next >.                            Click Next >
               The next page of the Form Wizard
               appears, and the text in the What title
               do you want for your form? box is
               selected.
           12. Type the desired form name.               Type Order Items, if
               The name appears in the What title do     necessary
               you want for your form? box.
           13. Select Finish.                            Click Finish
               The Form Wizard closes, and the new
               form opens.

          Close the form.




Canadian Beaver (CBIT)                                                            Page 231
Lesson 11 - Creating Basic Forms                                           Access 2003 - Lvl 1



USING AUTOFORM

    Discussion
           The fastest way to create a form is to use AutoForm. AutoForm automatically creates
           a simple columnar, tabular, datasheet, PivotTable, or PivotChart form from the
           selected table or query, without needing any input. However, you must select a table
           or query before AutoForm can create the form. All fields in the table or query will
           appear on the form, and the title of the form is the name of the table or query.

           AutoForm does not save the form for you automatically. When you close the form for
           the first time, Access prompts you to save it.



                   AutoForm creates a form using the defaults for the selected
                    form type. After AutoForm has created a form, however, its
                    formatting can be modified in Design view.


                   You can also use AutoForm to create a form based on the
                    selected table or query by selecting the Insert menu and the
                    AutoForm command or by clicking the New Object button
                    on the Database toolbar and selecting the AutoForm
                    command. When you use one of these methods, the form
                    always appears in a standard columnar format.


                   AutoForm can also be used to produce a formatted columnar
                    or tabular report.




   Procedures
           1. Select the Forms object list.

           2. Select the      New button on the Database window toolbar.
           3. Select the desired AutoForm option.
           4. Select the Choose the table or query where the object‟s data
              comes from list.
           5. Select the desired table or query.
           6. Select OK.




Page 232                                                              Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 11 - Creating Basic Forms




    Step-by-Step
          Use AutoForm to create a form.

          If necessary, display the Forms object list.

           Steps                                         Practice Data
           1. Select the New button on the Database
              window toolbar.                            Click        New
              The New Form dialog box opens.
           2. Select the desired AutoForm option.        Click AutoForm:
              The AutoForm option is selected.           Columnar
           3. Select the Choose the table or query       Click Choose the table or
              where the object‟s data comes from         query where the object‟s
              list.                                      data comes from
              A list of available tables and queries
              appears.
           4. Select the desired table or query.         Click Orders
              The table or query name appears in
              the Choose the table or query where
              the object’s data come from box.
           5. Select OK.                                 Click OK
              The New Form dialog box closes, and
              the new form opens in Form view.

          View the form. Then, close the form, saving it as Orders.

          Practice the Concept: Switch to the Tables object list and select the Line Items
          table. Click the arrow on the New Object button on the Database toolbar and select
          the AutoForm command. Close the form, saving it as Line Items. Then, switch to the
          Forms object list. Notice the new Line Items form.


ADDING A RECORD USING A FORM

    Discussion
          Forms are frequently used to add records to one or more tables. It can be easier to
          work in a well-designed form than in the underlying table because forms are usually
          designed to display all the fields for a single record within the form window,
          eliminating the need to scroll.




Canadian Beaver (CBIT)                                                               Page 233
Lesson 11 - Creating Basic Forms                                              Access 2003 - Lvl 1


           You can use the [Enter] key to move between fields as you enter data. If you want to
           skip one or more fields, you can press the [Tab] key until the insertion point appears
           in the desired field, or you can click in the field in which you want to enter data.
           When you press the [Enter] key after entering data in the last field in a record, Access
           automatically saves the record and displays a blank form for entering another record.



                    You can use the [Shift+Tab] key combination to move to the
                     previous field.


                    You can also use the New Record button on the Form View
                     toolbar to add a record to a form.




   Procedures
           1. Display the Forms object list.
           2. Open the form in Form view.


           3. Click the New Record button             at the bottom of the window.
           4. Type the desired data into the first field of the new record.
           5. Press [Enter].
           6. Enter data in additional data into the remaining fields as desired.




    Step-by-Step
           Add a record using a form.

           Open the Orders form in Form view. If the Orders form does not exist, use the
           Orders 2 form.

            Steps                                           Practice Data
            1. Click the New Record button at the
               bottom of the form window.                   Click
               The insertion point appears in the first
               field of the new record.
            2. Type the desired data into the first         Type 2000
               field of the new record.
               The data appears in the field.

Page 234                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 11 - Creating Basic Forms



           Steps                                          Practice Data
           3. Press [Enter].                              Press [Enter]
              The insertion point moves to the next
              field.
           4. Enter data in additional data in the        Follow the instructions
              remaining fields as desired.                shown below the table to
              The data appears in the fields.             complete this step

          Enter the following data in the corresponding fields:

                      Field                   Field Value
                      Customer ID             5995
                      Sales Rep               FLW
                      Order Date              (today’s date)
                      Shipping Date           (three days from today)
                      Shipping Method         UPS
                      Shipping Cost           12
                      Terms                   FOB
                      Full Name               Frances Wallace

          Close the form. To confirm that the information was entered into the form’s
          corresponding table, open the Orders table in Datasheet view and scroll to the last
          record to view the new entry. Then, close the Orders table.
          Close WORLD12.MDB.




Canadian Beaver (CBIT)                                                                Page 235
Lesson 11 - Creating Basic Forms                                           Access 2003 - Lvl 1




EXERCISE
CREATING BASIC FORMS

      Task
           Create basic forms.

           1. Open Train12x.
           2. Use the Form Wizard to create a new form using the Client table.
           3. Add all the fields to the form.
           4. Select the Columnar layout and the Ricepaper style.
           5. Name the form Client Data Entry and have the Form Wizard open
              the form to view or enter information.
           6. Print record 9, HEMCO.
           7. Add a new record, with the following data:

                            Field                 Data
                            Client ID             NELSON
                            Name                  Nelson Enterprises
                            Address               490 S. Maple St.
                            City                  Boston
                            State                 MA
                            Zip                   02188
                            Telephone Number      617-888-9261

           8. Close the form and open the Client table in Datasheet view to view
              the new record. Then, close the Client table.
           9. Use the Form Wizard to create a new form based on the Project
              Payments query.
           10. Add all the fields to the form.
           11. Select the Tabular layout and the Industrial style.
           12. Accept the default name, Project Payments, and have the Form
               Wizard open the form to view or enter information.




Page 236                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 11 - Creating Basic Forms




          13. Close the form.
          14. Create a tabular AutoForm based on the Trainer table.
          15. Close the form, saving it with the default name, Trainer.
          16. Close the database file.




Canadian Beaver (CBIT)                                                            Page 237
LESSON 15 -
CREATING BASIC REPORTS

In this lesson, you will learn how to:
         Use reports

         Use the Report Wizard

         Use print preview - reports

         Print pages of a report

         Group and summarize report data

         Base a report on a query

         Use AutoReport
Access 2003 - Lvl 1                                          Lesson 12 - Creating Basic Reports




USING REPORTS

    Discussion
          Although you can print records from a table or form, a report provides more precise
          control over the final output. Reports can include page headers and footers, calculated
          totals and subtotals, and even graphics. In addition, reports can be used for invoices,
          orders, presentations, and mailing labels.

          There are two basic types of reports: columnar and tabular. In a columnar report, the
          field names are listed on the left side of the page, and the field values are listed on the
          right. If space on the page permits, there can be more than one column. In a tabular
          report, the field names are listed across the top of the report, and the field values
          appear in the corresponding columns.

          Reports can include data from a single table or related tables. Reports can also be
          based on queries.



                     You cannot edit data in a report.




USING THE REPORT WIZARD

    Discussion
          You can use the Report Wizard to quickly and easily create a report. The basic steps
          needed to create a report using the Report Wizard are as follows:

                  1. Select the table(s) you want to use.
                  2. Select the fields you want to include.
                  3. Group the data.
                  4. Add grouping levels.
                  5. Sort the data.
                  6. Select a layout.
                  7. Select a style.
                  8. Name the report.


Canadian Beaver (CBIT)                                                                     Page 239
Lesson 12 - Creating Basic Reports                                           Access 2003 - Lvl 1


           When you have finished creating a report, the Report Wizard displays it in print
           preview. Print preview allows you to view the report before you print it.




                                         Using the Report Wizard




                   If you base a report on only one table, the Report Wizard does
                    not ask you to specify a table on which to group the data.


                   If you include fields from unrelated tables in the report,
                    Access closes the Report Wizard and opens the Relationships
                    window so that you can create the necessary relationship.


                   You can also activate the Report Wizard by double-clicking
                    the Create report by using wizard option in the Reports
                    object list in the Database window, by selecting the Insert
                    menu and the Report command, or by clicking the New
                    Object button on the Database toolbar and selecting the
                    Report command.




   Procedures
           1. Display the Reports object list.

           2. Select the      New button on the Database window toolbar.


Page 240                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 12 - Creating Basic Reports


          3. Select Report Wizard.
          4. Select OK.
          5. Select the Tables/Queries list.
          6. Select the table or query on which you want to base the report.
          7. Add the desired fields to the Selected Fields list box.
          8. To add another table to the report, select the Tables/Queries list.
          9. Select the desired table or query.
          10. Add the desired fields to the Selected Fields list box.
          11. Select Next >.
          12. Select the desired option in the How do you want to view your
              data? list box.
          13. Select Next >.
          14. Select the desired grouping level in the Do you want to add any
              grouping levels? list box.
          15. Select Next >.
          16. To sort the records, select the 1 list.
          17. Select the field by which you want to sort.
          18. Select Next >.
          19. Select the desired report layout.
          20. Select the desired report orientation.
          21. Select Next >.
          22. Select the desired report style.
          23. Select Next >.
          24. Type the desired report name.
          25. Select Finish.




    Step-by-Step
          From the Student Data directory, open WORLD13.MDB.
          Use the Report Wizard to create a report.




Canadian Beaver (CBIT)                                                               Page 241
Lesson 12 - Creating Basic Reports                                          Access 2003 - Lvl 1



           Steps                                         Practice Data
           1. Display the Reports object list.           Click Reports
              The Reports objects appear.
           2. Select the New button on the Database
              window toolbar.                            Click     New
              The New Report dialog box opens.
           3. Select Report Wizard.                      Click Report Wizard
              Report Wizard is selected.
           4. Select OK.                            Click OK
              The New Report dialog box closes, and
              the Report Wizard opens.
           5. Select the Tables/Queries list.            Click Tables/Queries
              A list of available tables and queries
              appears.
           6. Select the table or query on which you     Click Table: Orders
              want to base the report.
              The table or query is selected, and the
              fields in it appear in the Available
              Fields list.
           7. Add the desired field to the Selected      Double-click Order
              Fields list box.                           Number
              The field appears in the Selected
              Fields list box.
           8. Add additional fields as desired.          Double-click Order Date
              The fields appear in the Selected
              Fields list box.
           9. To add another table to the report,        Click Tables/Queries
              select the Tables/Queries list.
              A list of available tables and queries
              appears.
           10. Select the desired table or query.        Click Table: Customers
               The table or query is selected, and the
               fields in it appear in the Available
               Fields list box.
           11. Add the desired fields to the Selected    Follow the instructions
               Fields list box.                          shown below the table
               The fields appear in the Selected         before continuing on to
               Fields list box.                          the next step
           12. Select Next >.                            Click Next >
               The next page of the Report Wizard
               appears.


Page 242                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                       Lesson 12 - Creating Basic Reports



           Steps                                          Practice Data
           13. Select the desired option in the How       Click by Orders, if
               do you want to view your data? list        necessary
               box.
               A preview of the option appears in the
               Report Wizard.
           14. Select Next >.                             Click Next >
               The next page of the Report Wizard
               appears.
           15. Select the desired grouping level in the   Double-click Sales Rep
               Do you want to add any grouping
               levels? list box.
               A preview of the grouping level
               appears in the Report Wizard.
           16. Select Next >.                             Click Next >
               The next page of the Report Wizard
               appears.
           17. To sort the records, select the 1 list.    Click 1
               A list of available report fields
               appears.
           18. Select the field by which you want to      Click Order Number
               sort.
               The field is selected.
           19. Select Next >.                             Click Next >
               The next page of the Report Wizard
               appears.
           20. Select the desired report layout.          Click Stepped, if
               A preview of the layout option appears     necessary
               in the Report Wizard.
           21. Select the desired report orientation.     Click     Landscape
               The orientation option is selected.
           22. Select Next >.                             Click Next >
               The next page of the Report Wizard
               appears.
           23. Select the desired report style.           Click Soft Gray
               A preview of the report style appears
               in the Report Wizard.




Canadian Beaver (CBIT)                                                             Page 243
Lesson 12 - Creating Basic Reports                                            Access 2003 - Lvl 1



            Steps                                          Practice Data
            24. Select Next >.                             Click Next >
                The next page of the Report Wizard
                appears with the text in the What title
                do you want for your report? box
                selected.
            25. Type the desired report name.              Type Order Information
                The text appears in the What title do
                you want for your report? box.
            26. Select Finish.                             Click Finish
                The Report Wizard closes, and the
                report appears in print preview.

           From the Customers table, add the Customer Number, Store Name, Contact
           Name, Phone Number, and Sales Rep fields.

           Return to the table and continue on to the next step (step 12).

           Close print preview. Notice that the new report appears in the Reports object list.


USING PRINT PREVIEW - REPORTS

    Discussion
           When you open a report, it appears in print preview. Print preview allows you to see
           how the printed report will look before you print it.

           Print preview provides options for viewing the report. By default, the report appears
           magnified at 100%, the same size as the printout. You can zoom out to see more of the
           report or zoom in to see a portion of the report in more detail. The Zoom list in print
           preview allows you to choose from several magnification options, from as small as
           10% to as large as 500% or 1000%.

           You can display a report in One Page, Two Pages, or Multiple Pages view. The
           buttons at the bottom of the window allow you to navigate pages, and the scroll bars
           allow you to view different areas of a page.




Page 244                                                                  Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 12 - Creating Basic Reports




                                             Using print preview




                     You can also access print preview by right-clicking a report in
                      the Database window and selecting the Print Preview
                      command.


                     You can also use the Zoom list on the Print Preview toolbar
                      to switch the magnification between 100% and to fit the
                      window.




   Procedures
          1. Display the Reports object list.
          2. Select the report you want to preview.

          3. Select the        Preview button on the Database window toolbar.
          4. Click the area of the report page you want to magnify to 100%.
          5. Click anywhere in the report page to change the magnification back
             to fit the window.

          6. Click the Two Pages button            on the Print Preview toolbar to
             display two pages of the report.




Canadian Beaver (CBIT)                                                                  Page 245
Lesson 12 - Creating Basic Reports                                             Access 2003 - Lvl 1




           7. Click the One Page button          on the Print Preview toolbar to
              display one page of the report.

           8. Click the Next Page button         at the bottom of the report window
              to display the next page of the report.

           9. Click the Last Page button         at the bottom of the report window
              to display the last page of the report.

           10. Click the Previous Page button      at the bottom of the report
               window to display the previous page of the report.

           11. Click the First Page button         at the bottom of the report window
               to display the first page of the report.




    Step-by-Step
           Use print preview to view a report.

           If necessary, display the Reports object list. If the Order Information report does
           not exist, use the Order Information 2 report.

            Steps                                          Practice Data
            1. Select the report you want to preview.      Click Order
               The report is selected.                     Information, if necessary
            2. Select the Preview button on the
               Database window toolbar.                    Click       Preview
               The report appears in print preview.
            3. Click the area of the report page you       Click the initials for the
               want to magnify to 100%.                    first sales representative,
               The report area is magnified to 100%.       at the top of the page
            4. Click anywhere in the report page to        Click anywhere in the
               change the magnification back to fit        page
               the window.
               The entire page appears in print
               preview.
            5. Click the Two Pages button on the
               Print Preview toolbar to display two        Click
               pages of the report.
               Two pages appear in print preview.




Page 246                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                           Lesson 12 - Creating Basic Reports



           Steps                                             Practice Data
           6. Click the One Page button on the
              Print Preview toolbar to display one           Click
              page of the report.
              One page appears in print preview.
           7. Click the Next Page button at the              Click
              bottom of the report window to display
              the next page of the report.
              The next page of the report appears.
           8. Click the Last Page button at the              Click
              bottom of the report window to display
              the last page of the report.
              The last page of the report appears.
           9. Click the Previous Page button at the          Click
              bottom of the report window to display
              the previous page of the report.
              The previous page of the report
              appears.
           10. Click the First Page button at the            Click
               bottom of the report window to display
               the first page of the report.
               The first page of the report appears.

          Practice the Concept: Use the Zoom list on the Print Preview toolbar to change the
          magnification to 150%. Scroll through the report to view the text. Then, click
          anywhere in the report to change the magnification back to fit the window.

          Close print preview.


PRINTING PAGES OF A REPORT

    Discussion
          You can print specific pages of a report. This option is useful if the report contains
          numerous pages, and you only need information from one or more particular pages.
          The Print dialog box allows you to specify the pages you want to print.



                     You can print a report from the Database window or from
                      print preview.


                     To print an entire report, select it and click the Print button on
                      the Database toolbar.


Canadian Beaver (CBIT)                                                                     Page 247
Lesson 12 - Creating Basic Reports                                           Access 2003 - Lvl 1




                    You can also open the Print dialog box by right-clicking the
                     report you want to print and selecting the Print command.




   Procedures
           1. Display the Reports object list.
           2. Select the report you want to print.
           3. Select the File menu.
           4. Select the Print command.
           5. Select the Pages option.
           6. Type the number of the first page you want to print.
           7. Select the To box.
           8. Type the number of the last page you want to print.
           9. Select OK.




    Step-by-Step
           Print pages of a report.

           If necessary, display the Reports object list. If the Order Information report does
           not exist, use the Order Information 2 report.

            Steps                                         Practice Data
            1. Select the report you want to print.       Click Order
               The report name is selected.               Information, if necessary
            2. Select the File menu.                      Click File
               The File menu appears.
            3. Select the Print command.                  Click Print...
               The Print dialog box opens.
            4. Select the Pages option.                   Click      Pages
               The Pages option is selected, and the
               insertion point appears in the From
               box.



Page 248                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 12 - Creating Basic Reports



           Steps                                            Practice Data
           5. Type the number of the first page you         Type 2
              want to print.
              The number appears in the From box.
           6. Select the To box.                            Press [Tab]
              The insertion point appears in the To
              box.
           7. Type the number of the last page you          Type 4
              want to print.
              The number appears in the To box.
           8. Select OK.                                    Click OK
              The Print dialog box closes, and
              Access prints the selected pages.



GROUPING AND SUMMARIZING REPORT DATA

    Discussion
          The Report Wizard provides options for grouping and summarizing report data. You
          can organize your report by selecting the fields into which you want to group data. If
          you create more than one group, you can prioritize the groups into levels.

          In addition to grouping data by a field, you can add grouping intervals. Grouping
          intervals vary, depending upon the selected field. For instance, a date field can be
          grouped by month, quarter, year, etc.; a numeric field can be grouped by numeric
          intervals of 10s, 50s, 100s, etc.; and a text field can be grouped by its first letter,
          second letter, etc.

          If you have included a field with numeric data in your report, you can add summary
          calculations. Summary calculations include Sum, Avg (average), Min (minimum),
          and Max (maximum). If you select the Sum calculation, you can include a calculated
          percentage of the total for each group. You can also display just the summary
          calculations in the report or both the field data (details) and the summary calculations.




Canadian Beaver (CBIT)                                                                      Page 249
Lesson 12 - Creating Basic Reports                                            Access 2003 - Lvl 1




                                        Adding a grouping interval




                   If you are grouping on multiple fields, you can use the up and
                    down Priority buttons to change the order of the groups in the
                    Report Wizard.


                   You can add a new field to a specific location in a report by
                    first selecting the field in the Selected Fields list box below
                    which you want to insert the new field.


                   You must use Design view to add grouping levels and
                    summaries to an existing report.




   Procedures
           1. Display the Reports object list.

           2. Select the      New button on the Database window toolbar.
           3. Select Report Wizard.
           4. Select OK.
           5. Select the Tables/Queries list.
           6. Select the table or query on which you want to base the report.



Page 250                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 12 - Creating Basic Reports


          7. Add the fields you want to include in the report to the Selected
             Fields list box.
          8. Select Next >.
          9. Select the desired grouping option from the How do you want to
             view your data? list box.
          10. Select Next >.
          11. Select the desired grouping level from the Do you want to add any
              grouping levels? list box, if applicable.
          12. Select Grouping Options.
          13. Select the Grouping intervals list.
          14. Select the desired interval.
          15. Select OK.
          16. Select Next >.
          17. If you have included a Number data type field, select Summary
              Options.
          18. Select the desired summary options.
          19. Select OK.
          20. When you have finished selecting options from the Report Wizard,
              select Finish.




    Step-by-Step
          Group and summarize report data.

          If necessary, display the Reports object list.

           Steps                                           Practice Data
           1. Select the New button on the Database
              window toolbar.                              Click    New
              The New Report dialog box opens.
           2. Select Report Wizard.                        Click Report Wizard
              Report Wizard is selected.
           3. Select OK.                            Click OK
              The New Report dialog box closes, and
              the Report Wizard opens.



Canadian Beaver (CBIT)                                                              Page 251
Lesson 12 - Creating Basic Reports                                         Access 2003 - Lvl 1



           Steps                                        Practice Data
           4. Select the Tables/Queries list.           Click Tables/Queries
              A list of available tables and queries
              appears.
           5. Select the table or query on which you    Click Table: Orders
              want to base the report.
              The table or query is selected, and the
              fields in it appear in the Available
              Fields list box.
           6. Add the fields you want to include in     Follow the instructions
              the report to the Selected Fields list    shown below the table
              box.                                      before continuing on to
              The fields appear in the Selected         the next step
              Fields list box.
           7. Select Next >.                            Click Next >
              The next page of the Report Wizard
              appears.
           8. Select the desired grouping option        Click by Customers
              from the How do you want to view
              your data? list box.
              A preview of the grouping option
              appears in the Report Wizard.
           9. Select Next >.                            Click Next >
              The next page of the Report Wizard
              appears.
           10. Select the desired grouping level from   Double-click Order Date
               the Do you want to add any
               grouping levels? list box, if
               applicable.
               A preview of the grouping level
               appears in the Report Wizard.
           11. Select Grouping Options.                 Click Grouping
               The Grouping Intervals dialog box        Options...
               opens.
           12. Select the Grouping intervals list.      Click Grouping intervals
               A list of available intervals appears.
           13. Select the desired interval.             Click Quarter
               The interval appears in the Grouping
               intervals box.




Page 252                                                               Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                         Lesson 12 - Creating Basic Reports



           Steps                                           Practice Data
           14. Select OK.                                  Click OK
               The Grouping Intervals dialog box
               closes, and the interval appears in the
               group heading.
           15. Select Next >.                              Click Next >
               The next page of the Report Wizard
               appears.
           16. If you have included a Number data          Click Summary
               type field, select Summary Options.         Options...
               The Summary Options dialog box
               opens.
           17. Select the desired summary options.         Click    Sum
               The summary options are selected.
           18. Select OK.                                  Click OK
               The Summary Options dialog box
               closes.
           19. When you have finished selecting            Click Finish >
               options from the Report Wizard, select
               Finish.
               The Report Wizard closes, and the
               report appears in print preview.

          Add the Order Number, Order Date and Shipping Cost fields. Select the Order
          Number field in the Selected Fields list box, select the Customers table from the
          Tables/Queries list, and add the Store Name field below the Order Number field.

          Return to the table and continue on to the next step (step 7).

          Close print preview. Notice that the new Customers report appears in the Reports
          object list.


BASING A REPORT ON A QUERY

    Discussion
          You can use a query as the basis for a report. The query recordset appears as the
          report data. The report is updated each time it is opened or printed to reflect changes
          made to queried data.




Canadian Beaver (CBIT)                                                                   Page 253
Lesson 12 - Creating Basic Reports                                            Access 2003 - Lvl 1



                    You can also activate the Report Wizard by double-clicking
                     the Create report by using wizard option in the Reports
                     object list in the Database window, by selecting the Insert
                     menu and the Report command, or by clicking the New
                     Object button on the Database toolbar and selecting the
                     Report command.




   Procedures
           1. Display the Reports object list.

           2. Select the        New button on the Database window toolbar.
           3. Select Report Wizard.
           4. Select OK.
           5. Select the Tables/Queries list.
           6. Select the table or query on which you want to base the report.
           7. Add the desired fields to the Selected Fields list box, or add all
              fields.
           8. Select Next >.
           9. Select the desired grouping option from the How do you want to
              view your data? list box.
           10. Select Next >.
           11. Select the desired grouping level from the Do you want to add any
               grouping levels? list box, if applicable.
           12. Select Next >.
           13. Select the 1 field list.
           14. Select the first field by which you want to sort.
           15. Select additional sort fields, if desired.
           16. Select Next >.
           17. Select the desired report layout.
           18. Select the desired report orientation.
           19. Select Next >.
           20. Select the desired report style.
           21. Select Next >.


Page 254                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 12 - Creating Basic Reports


          22. Type the desired report name in the What title do you want for
              your report? box.
          23. Select Finish.




    Step-by-Step
          Base a report on a query using the Report Wizard.

          If necessary, display the Reports object list.

           Steps                                           Practice Data
           1. Select the New button on the Database
              window toolbar.                              Click     New
              The New Report dialog box opens.
           2. Select Report Wizard.                        Click Report Wizard
              Report Wizard is selected.
           3. Select OK.                            Click OK
              The New Report dialog box closes, and
              the Report Wizard opens.
           4. Select the Tables/Queries list.              Click Tables/Queries
              A list of tables and queries appears.
           5. Select the table or query on which you       Click Query: Order
              want to base the report.                     Items
              The query is selected, and the fields in
              it appear in the Available Fields list
              box.
           6. Add the desired fields to the Selected
              Fields list box, or add all fields.          Click
              The fields appear in the Selected
              Fields list box.
           7. Select Next >.                               Click Next >
              The next page of the Report Wizard
              appears.
           8. Select the desired grouping option           Click by Line Items, if
              from the How do you want to view             necessary
              your data? list box.
              A preview of the grouping option
              appears in the Report Wizard.




Canadian Beaver (CBIT)                                                               Page 255
Lesson 12 - Creating Basic Reports                                          Access 2003 - Lvl 1



           Steps                                         Practice Data
           9. Select Next >.                             Click Next >
              The next page of the Report Wizard
              appears.
           10. Select the desired grouping level from    Double-click Order
               the Do you want to add any                Number
               grouping levels? list box, if
               applicable.
               A preview of the grouping level
               appears in the Report Wizard.
           11. Select Next >.                            Click Next >
               The next page of the Report Wizard
               appears.
           12. Select the 1 field list.                  Click 1
               A list of available report fields
               appears.
           13. Select the first field by which you       Click Product ID
               want to sort.
               The sort field is selected.
           14. Select Next >.                            Click Next >
               The next page of the Report Wizard
               appears.
           15. Select the desired report layout.         Click     Outline 2
               A preview of the layout option appears
               in the Report Wizard.
           16. Select the desired report orientation.    Click Portrait, if
               The orientation option is selected.       necessary
           17. Select Next >.                            Click Next >
               The next page of the Report Wizard
               appears.
           18. Select the desired report style.          Click Corporate
               A preview of the style appears in the
               Report Wizard.
           19. Select Next >.                            Click Next >
               The next page of the Report Wizard
               appears with the text in the What title
               do you want for your report? box
               selected.




Page 256                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 12 - Creating Basic Reports



           Steps                                            Practice Data
           20. Type the desired report name in the          Type Order Items
               What title do you want for your
               report? box.
               The name appears in the What title do
               you want for your report? box.
           21. Select Finish.                               Click Finish
               The Report Wizard closes, and the
               report appears in Print Preview.

          Close print preview. Notice that the new report appears in the Reports object list.


USING AUTOREPORT

    Discussion
          You can use AutoReport to create a report quickly. AutoReport automatically creates
          a simple columnar or tabular report from the selected table or query without
          displaying any dialog boxes or needing any input. You must, however, first select the
          table or query. All fields in the table or query will appear in the report, and the report
          title will be the same as the table or query name.

          AutoReport does not automatically save a report; the first time you close the report,
          Access prompts you to save it.



                     The AutoReport Wizard creates the report using the most
                      recently used report settings. You can switch to Design view
                      to change the formatting of an existing report.


                     You can also activate the AutoReport Wizard by first selecting
                      the table or query you want to use, and then selecting the
                      Insert menu and the AutoReport command or clicking the
                      New Object button on the Database toolbar and selecting the
                      AutoReport command. When you use either of these
                      methods, the report appears in a standard columnar format,
                      with no formatting, headers, or footers.




Canadian Beaver (CBIT)                                                                     Page 257
Lesson 12 - Creating Basic Reports                                            Access 2003 - Lvl 1




   Procedures
           1. Select the Reports object list.

           2. Select the       New button on the Database window toolbar.
           3. Select the desired AutoReport option.
           4. Select the Choose the table or query where the object‟s data
              comes from list.
           5. Select the desired table or query.
           6. Select OK.




    Step-by-Step
           Use AutoReport to create a report based on a table.

           If necessary, display the Reports object list.

            Steps                                           Practice Data
            1. Select the New button on the Database
               window toolbar.                              Click     New
               The New Report dialog box opens.
            2. Select the desired AutoReport option.        Click AutoReport:
               The AutoReport option is selected.           Columnar
            3. Select the Choose the table or query         Click Choose the table or
               where the object‟s data comes from           query where the object‟s
               list.                                        data comes from
               A list of available tables and queries
               appears.
            4. Select the desired table or query.           Click Customers
               The table or query appears in the
               Choose the table or query where the
               object’s data comes from box.
            5. Select OK.                            Click OK
               The New Report dialog box closes, and
               the report appears in print preview.

           View the report and then close print preview. Save the report as Customers1 when
           you are prompted.
           Close WORLD13.MDB.

Page 258                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                        Lesson 12 - Creating Basic Reports




EXERCISE
CREATING BASIC REPORTS

      Task
          Create and print basic reports.

          1. Open Train13x.
          2. Use the Report Wizard to create a new report.
          3. Select the Project table and add the Project ID, Course Name, and
             Cost fields to the report.
          4. Select the Client table and add the Client ID and Name fields to the
             report.
          5. Group the data by Client and then add the Project ID grouping
             level.
          6. Sort the report by Course Name and calculate costs using the Sum
             function. Show both detail and summary calculations.
          7. Select the Outline 2 layout, the Portrait orientation, and the Bold
             style.
          8. Name the report Projects by Client and display it in print preview.
          9. Zoom to Fit to display the entire page.
          10. Zoom to 100% to display the report in more detail.
          11. Print page 3. Then, close print preview.
          12. Use Report Wizard to create a report from the Project Payments
              query.
          13. Add all the fields to the report, remove the Client ID grouping,
              group the report by Project ID, and sort it by Client ID.
          14. Select the Stepped layout, the Landscape orientation, and the
              Corporate style.
          15. Accept the default name of Project Payments and preview the
              report. Then, close print preview.
          16. Use AutoReport to create a formatted tabular report for the Trainer
              table.
          17. Preview the report. Then, close print preview, saving the report as
              Trainer.

Canadian Beaver (CBIT)                                                              Page 259
Lesson 12 - Creating Basic Reports            Access 2003 - Lvl 1


           18. Close the database file.




Page 260                                  Canadian Beaver (CBIT)
LESSON 16 -
EXPORTING DATA TO EXCEL AND WORD

In this lesson, you will learn how to:
         Export data to an Excel workbook

         Drag and drop data into Excel

         Create Excel workbooks - Office links

         Create Word documents - Office links
Lesson 13 - Using Online Help                                                Access 2003 - Lvl 1




EXPORTING DATA TO AN EXCEL WORKBOOK

    Discussion
           You can export database records from a database file in Access to a workbook in
           Excel. The Access data source can be a table, query, form, report, view, or macro.
           When you export data from a database, Access creates a workbook file in Excel with
           the data from the database. Any field names from the data source appear in the first
           row of the workbook.

           When you export to Excel, you can choose to export to an existing workbook, or you
           can indicate that you want to export to a new workbook by providing a new name in
           the File name text box in the Export dialog box. You can also choose to save the
           fonts, field widths, and data displayed from Lookup fields in your Access datasheet by
           selecting the Save formatted option before exporting.

           Exporting data to Excel is a useful way to display and work with data using the
           capabilities of Excel. For example, you could transfer a table containing investment
           portfolio information to Excel in order to perform calculations on investment
           performance.



                   When you export to an existing workbook file, the data in the
                    workbook is deleted and replaced by the Access data, unless
                    you are exporting to an Excel version 5.0 or later, in which
                    case the data is copied to the next available worksheet.


                   The Save formatted option in the Export dialog box is only
                    available in Excel versions 5.0 - 7.0 or in Excel 97 - 2000.


                   You can also open the Export dialog box by right-clicking in
                    the Database window and then selecting the Export
                    command.




   Procedures
           1. Open the desired database.
           2. Display the desired object list.
           3. Select the data source you want to export.
           4. Select the File menu.


Page 262                                                                Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 13 - Using Online Help


          5. Select the Export command.
          6. Select the Save as type list.
          7. Select the file type corresponding to the workbook format to which
             you want to export.
          8. Select the Save in list.
          9. Select the drive to which you want to export.
          10. Select the folder to which you want to export.
          11. Select the Save formatted option, if desired.
          12. Select Export.




    Step-by-Step
          From the Student Data directory, open WORLD68.MDB.
          Export Access data to an Excel workbook.

          Display the Tables object list.

           Steps                                         Practice Data
           1. Select the data source you want to         Click Reps
              export.
              The data source is selected.
           2. Select the File menu.                      Click File
              The File menu appears.
           3. Select the Export command.                 Click Export...
              The Export dialog box opens.
           4. Select the Save as type list.              Click Save as type
              A list of file types appears.
           5. Select the file type corresponding to      Scroll as necessary and
              the workbook format to which you           click Microsoft Excel 97-
              want to export.                            2003
              The file type is selected.
           6. Select the Save in list.                   Click Save in
              A list of available drives appears.
           7. Select the drive to which you want to      Click the student data
              export.                                    drive
              A list of available folders appears.



Canadian Beaver (CBIT)                                                               Page 263
Lesson 13 - Using Online Help                                              Access 2003 - Lvl 1



            Steps                                        Practice Data
            8. Select the folder to which you want to    Double-click to select the
               export.                                   student data folder
               A list of available files appears.
            9. Select Export.                            Click Export
               The Export dialog box closes.

           Open the Reps workbook in Excel. Close the workbook and Excel.


DRAGGING AND DROPPING DATA INTO EXCEL

    Discussion
           You can drag and drop data from Access into Excel. For example, you can drag and
           drop tables and queries from Access and place them in a workbook in Excel. The drag
           and drop method is very useful when you are building information in an Excel
           workbook and want to use data from an Access database file in the workbook. When
           you drag and drop data from Access to Excel, it is best to display the two program
           windows side by side on the desktop.

           When you drag and drop data to Excel, any formatting that has been applied to the
           data in Access will be lost in Excel. However, the field headings in Access are
           preserved in Excel.



                   You can also drag and drop into Excel by dragging the data
                    from Access to the program button on the taskbar until the
                    Excel window appears and then dragging the data into the
                    desired workbook.




   Procedures
           1. Open Microsoft Excel.
           2. Open the desired workbook.
           3. Open the desired database in Access.
           4. Display the desired object list.
           5. Select the Access data source you want to drag into Excel.
           6. Drag the data source to the desired location in the Excel workbook.


Page 264                                                              Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                            Lesson 13 - Using Online Help




    Step-by-Step
          Drag and drop data into Excel.

          Display the Tables object list. Open a blank workbook in Excel and resize the Excel
          and Access windows so that they appear side by side.

           Steps                                         Practice Data
           1. Select the Access data source you          Click Line Items
              want to drag into Excel.
              The data source is selected.
           2. Drag the data source to the desired        Drag the Line Items table
              location in the Excel workbook.            to cell A1 in the Excel
              The insertion point changes into an        workbook
              outline with a plus sign (+) attached
              when it is positioned over the Excel
              workbook and then the Access data
              appears in the Excel workbook.

          Notice the Line Items table appears in the workbook in Excel. Close the workbook
          and Excel and do not save the changes.


CREATING EXCEL WORKBOOKS - OFFICE LINKS

    Discussion
          You can use the Office Links feature to export a database data source from an Access
          database to an Excel workbook. The Access data source can be a table, query, form, or
          report. When you export data from a database, Access creates a workbook file in
          Excel with the data from the database. Any field names from the data source appear in
          the first row of the workbook.

          When you export to Excel using the Office Links feature, a new workbook is created.
          The workbook is automatically named the same as the data source. Exporting data
          using this method is a quick way to create a workbook that you can share with others
          who may not have Access installed on their computers.



                     You can also use the Tools menu to access the commands on
                      the Office Links submenu.



Canadian Beaver (CBIT)                                                               Page 265
Lesson 13 - Using Online Help                                                 Access 2003 - Lvl 1




   Procedures
           1. Open the desired database.
           2. Display the desired object list.
           3. Select the data source you want to export.

           4. Click the arrow on the OfficeLinks button            on the Standard
              toolbar.

           5. Select the Analyze It with Microsoft Excel           command.




    Step-by-Step
           Create an Excel workbook using the Office Links feature.

           Display the Tables object list.

            Steps                                          Practice Data
            1. Select the data source you want to          Click Items
               export.
               The data source is selected.
            2. Click the arrow on the OfficeLinks
               button on the Standard toolbar.             Click
               The OfficeLinks menu appears.
            3. Select the Analyze It with Microsoft        Click Analyze It with
               Excel command.
               Excel opens and the data appears in a       Microsoft Excel
               new workbook named the same as the
               Access data source.

           Close the workbook and Excel.


CREATING WORD DOCUMENTS - OFFICE LINKS

    Discussion
           You can use the Office Links feature to export a database data source from an Access
           database to create a Word document. The Access data source can be a table, query,

Page 266                                                                 Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                             Lesson 13 - Using Online Help


          form, or report. When you export data from a database, Access creates a document in
          Word with the data from the database. When a table, query, or form is exported to
          Word, the data is inserted into a table. Any field names from the data source appear in
          the first row of the table and are shaded. Reports appear in Word as standard
          documents with tabs used to separate the data.

          When you export to Word using the Office Links feature, a new document is created
          in the .rtf (rich text format) file format. The document is automatically named the
          same as the data source. Exporting data using this method is a quick way to create a
          document that you can share with others who may not have Access installed on their
          computers. In addition, it enables you to use the formatting features found in Word.



                     You can also use the Tools menu to access the commands on
                      the Office Links submenu.




   Procedures
          1. Open the desired database.
          2. Display the desired object list.
          3. Select the data source you want to export.

          4. Click the arrow on the OfficeLinks button            on the Standard
             toolbar.


          5. Select the Publish It with Microsoft Word            command.




    Step-by-Step
          Create a Word document using the Office Links feature.

          Display the Reports object list.

           Steps                                          Practice Data
           1. Select the data source you want to          Click Items per Order
              export.
              The data source is selected.




Canadian Beaver (CBIT)                                                                 Page 267
Lesson 13 - Using Online Help                                          Access 2003 - Lvl 1



            Steps                                     Practice Data
            2. Click the arrow on the OfficeLinks
               button on the Standard toolbar.        Click
               The OfficeLinks menu appears.
            3. Select the Publish It with Microsoft   Click Publish It with
               Word command.
               Word opens and the data appears in a   Microsoft Word
               new document named the same as the
               Access data source.

           Close the document and Word.
           Close WORLD68.MDB.




Page 268                                                          Canadian Beaver (CBIT)
Access 2003 - Lvl 1                                          Lesson 13 - Using Online Help




EXERCISE
EXPORTING DATA TO EXCEL AND WORD

      Task
          Export Access data into Excel and Word.

          1. Open Train68x.
          2. Export the Invoice table as an Excel workbook named Invoice.
          3. Open Excel and the Invoice workbook that was just created.
          4. Insert a new worksheet into the workbook and name it Payment.
          5. Tile the Access and Excel windows.
          6. Drag the Payment table in Access to cell A1 of the Payment
             worksheet in Excel.
          7. Save and close the workbook and Excel.
          8. Create a new Excel workbook using the Office Links feature and the
             data in the New Payment Form form.
          9. Close the workbook and Excel.
          10. Create a new Word document using the Office Links feature and the
              data in the Projects by Client report.
          11. Save the report as a Word document and close Word.
          12. Close the database file.




Canadian Beaver (CBIT)                                                            Page 269
INDEX

Access
  exiting, 20, 21
  starting, 3
Aide
  Microsoft Office Online, 31
  Tapez une question, 28
Answer Wizard, 25
Ask a Question, 3, 25
AutoForm, 232, 233
AutoReport, 257, 258
Cell effects
  changing, 58, 59
Column width
  changing, 55, 56
Columns
  freezing, 64, 65
  hiding, 62
  moving, 60, 61
  selecting, 59, 60
  unhiding, 63, 64
Comparison operators
  using, 198
Conditions
  And, 199, 200
  Or, 201, 202
Criteria
  creating expressions, 205, 206, 207
Data
  replacing, 157, 158
Data types
  assigning, 80, 82
  AutoNumber, 81
  Currency, 81
  Date/Time, 81
  Hyperlink, 81
  Lookup Wizard, 81
  Memo, 80
  Number, 80
  OLE object, 81
  Text, 80
  Yes/No, 81
Database objects
  opening, 16, 17
  types, 13
Database window, 14, 15
Database Wizard
  creating databases, 69, 70, 71
Access 2003 - Lvl 1                             Lesson 2 - Working with the Office Assistant


Databases, 2
   creating, 69, 70, 71
   creating new, 74, 75
   Database window, 14
   forms, 13
   macros, 14
   modules, 14
   opening existing, 4, 5, 6
   opening objects, 16, 17
   pages, 14
   queries, 13
   relational, 2
   reports, 13
   tables, 13
   using objects, 13
Datasheet view
   opening a query, 176
   using, 41
Date/Time formats
   setting, 100, 101
Default values
   setting, 104, 105
Design view
   creating a query, 173, 174, 175
   creating tables, 76, 77, 78
   using, 78
Excel
   creating workbooks using Office Links, 265, 266
   dragging and dropping data into, 264, 265
   exporting data to, 262, 263
Expression box, 205, 206
Expression Builder
   using, 205, 206
Field descriptions
   adding, 83
Field names
   adding, 79
Field properties
   creating an input mask, 108, 109, 110, 111
   limiting the field size, 95, 96
   lookup list, 120, 121
   property types, 94
   setting date/time formats, 100, 101
   setting default values, 104, 105
   setting number formats, 99
   setting validation rules/text, 106, 107
   setting yes/no formats, 102, 103
   using, 94
Field size
   limiting, 96
Fields, 2

Canadian Beaver (CBIT)                                                             Page 271
Lesson 2 - Working with the Office Assistant       Access 2003 - Lvl 1


   hiding in a query, 189, 190
   navigating, 41
Filter By Form, 165, 166, 167
Filter By Selection, 159, 160, 161
Filter Excluding Selection, 162, 163
Filter For, 163, 164
Filters
   applying, 161, 162
   Filter By Form, 165, 166, 167
   Filter By Selection, 159, 160, 161
   Filter Excluding Selection, 162, 163
   Filter For, 163, 164
   removing, 161, 162
Find
   finding specific records, 152, 153
   using wildcards, 154, 155, 156
Font attributes
   changing, 56, 57
Form Wizard, 223, 224, 225
Forms, 13, 223
   adding a record, 233, 234
   AutoForm, 232, 233
   basing on a query, 229, 230
   columnar, 223
   creating, 223, 224, 225
   datasheet, 223
   Form Wizard, 223, 224, 225
   justified, 223
   PivotChart, 223
   PivotTable, 223
   printing records, 228, 229
   tabular, 223
   viewing records, 226, 227
Help
   Office Assistant, 32
   using, 25
Input mask
   creating, 109, 110
   creating custom, 114, 115
Input Mask Wizard, 108, 114
Join lines
   deleting, 136
Lookup field
   creating, 117, 118
   properties, 120, 121
Lookup list
   creating, 117, 118
Lookup properties
   modifying, 120, 121
Lookup Wizard
   creating a list, 117, 118

Page 272                                       Canadian Beaver (CBIT)
Access 2003 - Lvl 1                            Lesson 2 - Working with the Office Assistant


Macros, 14
Menu bar, 6
Menus
   changing options, 11, 12
   full, 6
   short, 6
   shortcut, 7
   submenus, 7
   using commands, 6, 7, 8
Modules, 14
Number formats
   setting, 97, 99
Object types
   selecting, 14, 15
Office Assistant, 25
   changing options, 35, 37
   displaying, 33
   hiding, 33
   querying, 34
Office Links
   using to create Excel workbooks, 265, 266
   using to create Word documents, 266, 267
Office on the Web, 25
Operators
   And, 199, 200
   Between And, 203, 204
   Or, 201, 202
Page setup
   changing, 142, 143
Pages, 14
Performance Analyzer, 218, 219
Primary keys
   setting, 84, 85
Print preview, 144, 145, 244, 245, 246
Printing
   changing the page setup, 142, 143
   print preview, 144, 145
   queries, 192, 193
   records in a form, 228, 229
   recordsets, 192, 193
   report pages, 247, 248
   selected records, 146, 147, 148
   table data, 141
Queries, 13, 170
   adding a table, 177, 178
   adding criteria, 188, 189
   adding records, 190, 191
   basing reports on, 253, 254, 255
   creating in Design view, 173, 174, 175
   hiding a field, 189, 190
   joining tables, 179, 180

Canadian Beaver (CBIT)                                                            Page 273
Lesson 2 - Working with the Office Assistant         Access 2003 - Lvl 1


  opening, 176
  printing, 192, 193
  running, 181
  Simple Query Wizard, 170, 171, 172
  sorting, 186
  using an And condition, 199, 200
  using an Or condition, 201, 202
  using comparison operators, 197, 198
  using the Between And operator, 203, 204
  using the Expression builder, 206, 207
  using wildcard characters, 208, 209
Records, 2
  adding, 42, 43
  adding using a query, 190, 191
  deleting, 50, 51
  editing, 47, 48
  finding specific, 152, 153
  moving through, 44, 45
  printing selected, 146, 147, 148
  replacing data, 157, 158
  saving, 49
  selecting, 46
  sorting, 151
  viewing in forms, 226, 227
Recordsets, 170
  printing, 192, 193
Referential integrity
  setting, 131, 133
Relationships
  creating between tables, 128, 129, 130
  deleting join lines, 136
  join types, 179
  one-to-many, 128, 213
  one-to-one, 128, 213
  setting referential integrity, 131, 133
  using related tables, 127
  window, 128
Replace, 157, 158
Report Wizard, 239, 240, 241
Reports, 13
  AutoReport, 257, 258
  basing on a query, 253, 254, 255
  columnar, 239
  creating, 239, 240, 241
  grouping and summarizing data, 249, 250, 251
  printing pages, 247, 248
  tabular, 239
  viewing, 244, 245, 246
Row height
  changing, 54
ScreenTips, 9

Page 274                                         Canadian Beaver (CBIT)
Access 2003 - Lvl 1                            Lesson 2 - Working with the Office Assistant


Simple Query Wizard, 170, 171, 172
Subdatasheets
  viewing, 134, 135
Table Analyzer Wizard, 213, 215
Table Wizard
  creating a table, 86, 87, 88
Tables, 2, 13
  adding field descriptions, 83
  adding field names, 79
  adding records, 42, 43
  adding to a query, 177, 178
  analyzing, 213, 215, 218, 219
  assigning data types, 80, 82
  changing cell effects, 58, 59
  changing font attributes, 56, 57
  changing the column width, 55, 56
  changing the row height, 54
  creating in Design view, 76, 77, 78
  creating with the Table Wizard, 86, 87, 88
  deleting join lines, 136
  deleting records, 50, 51
  designing, 75
  editing records, 47, 48
  freezing columns, 64, 65
  hiding columns, 62
  joining, 128
  joining in a query, 179, 180
  moving columns, 60, 61
  moving through records, 44, 45
  navigating, 44, 45
  navigating fields, 41
  printing, 141
  related, 127
  relating, 2
  saving new, 85, 86
  saving records, 49
  selecting columns, 59, 60
  selecting records, 46
  setting a primary key, 84, 85
  splitting, 213
  unhiding columns, 63, 64
  using Datasheet view, 41
  using Design view, 78
  viewing subdatasheets, 134
Task pane
  New File, 3, 18, 19
  using, 18, 19
Toolbars
  changing options, 11, 12
  displaying, 8, 9
  hiding, 8, 9

Canadian Beaver (CBIT)                                                            Page 275
Lesson 2 - Working with the Office Assistant            Access 2003 - Lvl 1


  using hidden buttons, 10
Validation rules/text
  setting, 106, 107
Wildcard characters
  using, 208, 209
Wildcards
  using, 154, 155, 156
Wizards
  Input Mask, 108, 114, 115
Word
  creating documents using Office Links, 266, 267
Yes/No formats
  setting, 102, 103




Page 276                                            Canadian Beaver (CBIT)
Canadian Beaver (CBIT)   Page 277

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:76
posted:8/13/2011
language:English
pages:283