Docstoc

Access Database Tutorial

Document Sample
Access Database Tutorial Powered By Docstoc
					                                                    Access 2003 Tutorial


SECTION ONE .................................................................................................................. 3
  Planning an Access Database.......................................................................................... 3
  Special Note: ................................................................................................................... 4
  A Database Design Checklist.......................................................................................... 4
  How are Tables Related? ................................................................................................ 5
  Database Objects ............................................................................................................. 6
  Tables .............................................................................................................................. 6
  Queries ............................................................................................................................ 7
  Forms .............................................................................................................................. 7
  Reports ............................................................................................................................ 7
  Data Access Pages .......................................................................................................... 8
  Macros and Modules ....................................................................................................... 8
  Choosing the Right File Format ...................................................................................... 8
  Working with Database Objects ..................................................................................... 9
  Creating new Objects .................................................................................................... 10
  Managing Database Objects ......................................................................................... 11
  Modifying Object Properties......................................................................................... 11
  Using Expressions in Database Objects ........................................................................ 12
  Using Wizards to Create Databases and Objects .......................................................... 14
  Exporting and Importing Data ...................................................................................... 16
  Importing external Text Files........................................................................................ 17
    To import a delimit text file ...................................................................................... 18
  Viewing Your Data ....................................................................................................... 19
  Creating a New Table ................................................................................................... 20
  Saving a Table Design .................................................................................................. 22
  Entering Data into Your Table ...................................................................................... 22
  Printing a Table ............................................................................................................. 23
  Closing a Database ........................................................................................................ 23
SECTION II ...................................................................................................................... 24
  Enhancing a Table......................................................................................................... 24
  Creating a Form from an Existing Table ...................................................................... 24
  Form Design View and Other Form Options ................................................................ 25
  Adding, Deleting, Moving, and Modifying Fields ....................................................... 26
  Using Shortcut Menus................................................................................................... 27
  Hiding/Unhiding Fields ................................................................................................ 27
  Edit, Add, and/or Delete Records ................................................................................. 27
SECTION III..................................................................................................................... 29
  Working with date in the Datasheet view of a table: .................................................... 29
  Options related to the Datasheet ................................................................................... 29
  Datasheet ....................................................................................................................... 30
  Changes to a Specific Column within the Datasheet .................................................... 31
SECTION IV .................................................................................................................... 35
  Query Introduction ........................................................................................................ 35
  Using Queries to Extract Data from a Database ........................................................... 35
  Choosing the Right Query Type ................................................................................... 36
  SELECT QUERIES ...................................................................................................... 37



                                                           Page 1 of 53
                                                 Access 2003 Tutorial


  CROSSTAB QUERIES ................................................................................................ 39
  PARAMETER QUERIES ............................................................................................ 39
  ACTION QUERIES...................................................................................................... 40
  DEFINING QUERY PROPERTIES ............................................................................ 43
  Saving a Query.............................................................................................................. 44
  Using all Fields of a Table for a Query......................................................................... 44
  Moving a Column in a Query ....................................................................................... 44
  Renaming a Query ........................................................................................................ 45
  Printing a Query ............................................................................................................ 45
SECTION V ...................................................................................................................... 46
  Building Great Forms and Reports ............................................................................... 46
  DESIGN 101: WORKING WITH CONTROLS ......................................................... 46
  ADDING A NEW CONTROL TO A FORM OR REPORT ....................................... 48
  POSITION CONTROL AND LABLES ....................................................................... 49
  MAKING FORMS EASIER TO USE .......................................................................... 50
  MAKING REPORTS EASIER TO UNDERSTAND .................................................. 51
  Creating a Report with the Report Wizard ................................................................... 53




                                                        Page 2 of 53
                                   Access 2003 Tutorial



SECTION ONE
Planning an Access Database

Of all the Office applications, Microsoft Access is by far the most demanding and
conceptually challenging. Anyone who‟s ever composed an interoffice memo or put
together a budget can relate to Word and Excel instantly; an Access database, on the
other hand, is made up of many individual objects, each of which must be built
individually. With a wizard‟s help, it‟s possible to put together a simple database
application in a relatively short time--to handle everything from tracking the contents of a
wine cellar to managing inventory. Access applications can also scale up to enormous
sizes, serving the information needs of large organizations and acting as a front end to
data stored on mainframes and other network database servers.

Using an Access database, you can store and manage large quantities of data for a wide
variety of business and personal activities. If you‟ve previously used flat-file database
managers to perform simple list management tasks, you‟ll discover that Access is for
more powerful, with the capability to maintain and link multiple tables and create
applications using a robust programming language. Before you begin laying out the
structure of a database, it helps to understand the components that make up Access.
     The database engine is the (generally invisible) software that actually stores,
        indexes, and retrieves data. When you create a standalone database, Access uses
        its own engine to manage data. If you choose File, New and select one of the
        Project options, you can use the Microsoft Data Engine (MSDE) instead. MSDE
        is compatible with Microsoft‟s enterprise-wide database program, SQL Server.
     Database objects provide the interface you use to view, enter, and extract
        information from a database. The most common database objects are tables,
        forms, queries, and reports.
     Access includes a full set of design tools that you use to create objects. The
        reports designer, for example, enables you to sort data, group by fields, and add
        headers and footers to each page as well as the entire report.
     Finally, Access includes a rich set of programming tools you can use to automate
        routine tasks. Confusingly, the Access database window includes an object type
        called macros; these automation tools are completely different from the Visual
        Basic for Applications code you can add to most database objects.

The basic file type in Access is a database, which uses the extension *.mdb. Curiously,
an Access database file doesn‟t have to contain any data at all; if you use Access strictly
as a front end to retrieve information from a SQL Server database, the *.mdb file need
contain only form, report and query objects. If you design your database to hold data,
you must create one or more table object to hold the data, and those objects as well as the
data itself are stored in the database file.



                                       Page 3 of 53
                                  Access 2003 Tutorial


Special Note:
Access wizards make it relatively easy to create and work with database objects, but
don‟t underestimate the challenges of build a robust, easy-to-use database application.
Access applications have a way of creeping up in complexity, and it‟s all too easy to get
in over your head. Defining database table relationships and their associated referential
integrity rules, adhering to standard database normalization rules, adding error-handling
routines, and setting up security for multiple users are just a few of the significant
challenges you‟ll face as you build an application. Don‟t even consider using Access to
handle mission-critical data, such as the accounting system for your business, unless
you‟re a skilled programmer. And even then, chances are good that a commercial
program will handle the job more smoothly and safely than anything you can build on
you own.


A Database Design Checklist

The biggest single reason an Access database application fails to materialize into a
functional smooth working applications is the lack to planning, pre-development analyze
and table layout. Before you begin creating database object, think carefully about who
will use the database and what kind of reports you will need to produce from the data
within it. Use the following checklist to define your design:

      Storing data:
          o How much data do you plan to enter?
          o Home much training and expertise will be required of those who are
              responsible for that data entry?
          o Will you need to create systems to ensure accurate and reliable data entry?
      Retrieving data:
          o What relationships do you need to establish between the different set of
              data that you produce?
          o How can you exploit these relationships to create new combinations of dta
              from different sources?
          o Do you need to subtotal or summarize the data in any way?

      Modifying data:
         o Who will be responsible for editing data, and what level of understanding
             will be required of them?
         o What safeguards will you need to create to ensure that changes in your
             database do not result in duplicate records or inconsistent data?
         o If several people are using the database, how do you prevent unauthorized
             changes to data, while still allowing users to retrieve the information they
             need?




                                       Page 4 of 53
                                   Access 2003 Tutorial


How are Tables Related?

There are many pitfalls and potential show stoppers you may come in contact with when
you develop a database from scratch. Of these, the three that most often lead to failure
are: Lack of planning, data analysis, and table design.

Lack of planning is often the direct result of how easy it is to open Access, generate a
new database, generate a table or two, import or input some records and generate a few
queries and a report or two. Several weeks later after several hundred records have been
posted, things start to go wrong. Suddenly the reports no longer want to work, data you
know is in the database tables is not showing up on these reports, duplication of data
seems to be running amok, queries that should be returning dataset with a few records
suddenly are returning thousands of records. All the praise you have been receiving for
this database has turned to smoke and ashes. “How come?”, “What happened?”, “This
data is garbage”, and “What are you going to do about it?” seem to be the remarks of the
day. To fix the problem, you will often find yourself totally redesigning the database
from scratch. You may have some luck in saving all or most of the current data but that
is not a given. Planning, data analysis, and table design/definition can eliminate this.

When designing a database, you should provide at least one field in each table that may
be repeated in at least one of the other tables. This field, called a Primary Key, needs to
be unique and once defined and saved will not allow duplicate entries within the database
table. The primary key in a primary table is related to secondary table(s) as a Foreign
Key within the secondary table. This relational database management system (RDMS)
function allows you to create queries such as shown above to generate ad hoc or formal
reports needed for data analysis or data presentation.

   Figure 1
                                  Primary Table One with a primary key
                                  defined and shown in bold letters.




         Foreign Key
         .                                                   Mid table with a primary key defined
                                                             (shown in bold letters.) Two foreign
                                                             keys from the first and second
                                                             primary




                                        Page 5 of 53
                                      Access 2003 Tutorial


In comparison, a manual database, such as a library database, would use a book‟s call
number where you could create a report listing both the book‟s title and author (first
table) and the price and publisher (second table). In this case, the call number would be
the primary key for the book table and a foreign key for the publisher table. There are
several special rules you must adhere to when defining primary and foreign keys within
Access. For the purpose of this unit, we will not go into defined these keys.

Database Objects
The basic building blocks of an Access database are objects. Although Access supports
many types of object, the most common by fare are tables, queries, forms, and reports. A
database can contain any number of objects. The database wind, which is visible by
default when you open a new database, let you create and edit objects contained in the
current database. As you can see in figure 1, the Access database window consists of two
distinct regions: a larger region on the right that displays a list of object in the current
database.
               Figure 2 Use the database window to see a list of all database objects,
                                        arranged by type




Tables

A table is the basic unit for storing a collection of data in an Access database. A table‟s
definition consists of a list of fields, each of which stores a discrete piece of information
for a single record. For example, an Employees table might contain the fields
EmployeeID, LastName, FirstName, Position, DateHired, and HourlyRate. Because each
record consists of one complete set of fields, a single record in the Employees table
contain all available fields for a single employee. Fields such as HomeAddress,
HomePhone, MarrageStatus, NumberDependents, WifeName, and SocialSecurityNumber
may also be part of this table or be part of an associated table due to company policy or
security reasons.


                                           Page 6 of 53
                                  Access 2003 Tutorial



The arrangement of fields and records is most obvious in Datasheet view, which displays
data in rows and columns. When you open a table in Datasheet view, each record consist
of a single row, and each column represents a single field.


Queries

Queries enable you to extract a subset of data from a single table, from a group of related
tables, or from other queries, using criteria you define. By saving a query as a database
object, you can run the query at any time, using the current contents of the database.
When you display a query in Datasheet view, it looks exactly like a table; the crucial
difference is that each row of the query‟s result set can consist of fields drawn from
several tables. A query can also contain calculated fields, which display results based on
the contents of other fields.

CAUTION          Be careful when working with queries. Queries typically contain
                 live data. If you change data in a query Datasheet or form, it
                 changes in the underlying table as well without any warning.



Forms

Access forms enable user to enter, view, and edit information, generally one record at a
time. Yu can design forms that closely resemble pages forms such as invoices and time
sheets, or you can create forms that are organized for data entry, complete with data-
validation rules. A form window can also include a subform that displays information
from a related table. For example, a form that shows a single record from the
Departments table might include a subform that displays all the employees who work in a
give department, allowing you to edit information about those employees.


Reports

Reports enable you to present data from one or more tables or queries in a readable style
and a professional format, generally for printed output. A report might include detailed
list of specific data, with each row consisting of a single record, or it might provide a
statistical summary of a large quantity of information. A report design can include
grouping and sorting options; for example, you might create a weekly sales summary that
runs a query, groups the query result set by salesperson, and displays details of each sale
in a list beneath each name.




                                       Page 7 of 53
                                  Access 2003 Tutorial


TIP           Access reports transfer well to other Office applications, when you can
              use more powerful editing and analysis tools to create good-looking
              documents and charts. For example, buttons on the Access toolbar
              enable you to export a report to Microsoft Word, edit the page, and
              then print it or save it on a Web server. You can also sent a report to
              Excel, perhaps to create a chart or PivotTable report for use in
              another document.



Data Access Pages

Pages--also known as data access pages--enable you to publish database information on a
corporate intranet or (with some difficulty) on the Web. You can design pages that
present data, enable users to edit the contents of a database--including changing data and
appending new data to a table--or provide tools for data analysis. It‟s relatively easy to
create a data access page, thanks to the Page Wizards; however, editing a data access
page and managing its security setting require advanced skills. Unlike all other database
objects, a data access page is stored on disk as a separate HTML file; icons in the
database window are shortcuts to the actual file.


Macros and Modules

The final two selections in the database window allow you to automate actions in an
Access database:

      Macros enable you to define a sequence of actions in an Access database. Macros
       are generally easy to create, even for users who have no programming
       background. You select each action by name, fill in the appropriate action
       arguments, and optionally supply a condition under which the action will be
       performed. For example, you can specify that a particular macro is to run every
       time you open a specific for, or you can attach the macro to a command button in
       a form. To run a macron, select the object in the Macros list and click the Run
       button on the Access toolbar.
      Modules are collections of Visual Basic procedures and declarations, designed to
       perform specific tasks in the context of your database. Unlike Word, Excel, and
       PowerPoint, Access does not have a macro recorder that can generate VBA code
       automatically.


Choosing the Right File Format

Access 2003 and Access 2002 share a common file format, making it easy to share
database files between the two versions. If you expect that you will want to use your


                                      Page 8 of 53
                                   Access 2003 Tutorial


database with earlier version of Access, you need to pay special attention to your file
format options.

To guarantee backward compatibility with older version, any new databases you create
using Access w003 or Access 2003 use the older Access 2000 format. This option
ensures that your database will work properly in a large organization where some users
are still running Access 2000. Using this format allows all Access 2000/2002/2003 users
(with correct permissions) to modify the design and content of the database.

The Access 2002-2003 format include a handful of new functions, methods, and
properties, and its method for sorting objects within the database file is more efficient. If
you‟re certain you will use a database only on systems running Access 2002 or later, you
can convert it to the new format by choosing Tools, Database Utilities, Convert Database,
To Access 2002-2003 Format. (If you choose this menu option with no database open,
you will be prompted to supply the name of the database to convert.)


Working with Database Objects

As noted earlier all the objects in a given database are available for browsing in the
database window. You can visit this location and create a new object with a single click.

The default action for database object is Open--if you double-click a query icon, Access
executes that query and returns its result set in Datasheet view. Likewise, double-
clinging a form or report icon opens the select object using the current contents of the
database. The effect is the same if you select an object and click the Open button at the
top of the database window. (If you select a Report object, the Open button is replaced by
the Review button.)

To view and edit the definition and structure of an object, select any object and click the
Design button. In Design view, you can modify the appearance of an object (the fonts
and colors on a form), change the table or query from which it derives data, or adjust any
of hundreds of other properties for the selected object.




                                        Page 9 of 53
                                   Access 2003 Tutorial



Creating new Objects

To create a new table, query, form, or report, use either of the following techniques:

Figure 3                                                      Click the New button on the
                                                               toolbar at the top of the database
                                                               window, and select one of the
                                                               options listed in the resulting
                                                               dialog box. Figure 2 show the
                                                               New Form window, listing a
                                                               variety of wizards and AutoForm
                                                               tools for create new forms. In
                                                               addition, the first entry in the list
                                                               enable you to go directly to the
                                                               Design view for a form, and
                                                               begin your work on a new form
                                                               object from scratch.

                                                                Double-click any of the
Figure 4                                                         template icons show at the
                                                                 beginning of any object list.
                                                                 These icons (which resemble a
                                                                 scratchpad with a key icon on
                                                                 it) offer multiple ways to create
                                                                 a new object, including various
                                                                 wizards and the option to create
                                                                 an object from scratch in
                                                                 Design view. As figure 3
                                                                 demonstrates, the canned
                                                                 choice available form the New
                                                                 Table Wizard can be a useful
                                                                 starting point for business and
                                                                 personal databases.

                                                    When used judiciously, wizards can be
enormously helpful in the initial design of any database object. Some wizards are
valuable mainly as introductory tools for newcomers to Access, whereas others are
consistently useful even for experienced database developers. The Table and Query
Wizards are acceptable for generic databases, but in both cases, Design view represents a
far more efficient way to develop individual objects that precisely match specify
requirements. On the other hand, the Form and Report Wizards almost always provide an
excellent starting point for creating new forms and reports. When working with forms
and report in Design view, it‟s usually easier to move or modify existing object than it is
to add and edit new controls.



                                       Page 10 of 53
                                   Access 2003 Tutorial


TIP        When you use a wizard to create the initial version of an Access object,
           always expect to switch over to Design view to fine-tune the result. A
           wizard seldom produces the exact object that you need; in particular, the
           Design views for Forms and Reports offer direct Access to important
           design elements such as controls, properties, fields, sections, and groups--
           and enable you to make detailed changes in the appearance and behavior
           of database objects.



Managing Database Objects

To see a concise list of available options for existing object, select any object in the
database window and use the right-click shortcut menus. Many of these options are also
available from buttons on the Access toolbar, or from Access menus. You can open any
object to view its content or its design; you can also rename or delete an object, cut or
copy it to the Windows Clipboard, or add it to a group of favorite shortcuts for quick
access.

TIP        All version of Access since Access 2000 are “smart” about handling
           changes to the names of objects. When you rename a field in a table,
           Access automatically changes any reference to that field in queries, forms,
           report, and other objects. If you change the name of a field, you shouldn’t
           need to edit any other objects. However, any captions that reference those
           fields on a existing form or report are unchanged.



Modifying Object Properties

Confusingly, every database object has two set of properties. If you right-click the
object‟s icon in the database window and choose Properties, you see a bare-bones dialog
box that list the object‟s General properties. These include the object‟s name, a text
description, the data the object was created, and the date it was last modified. Ho-hum

By contrast, if you open an object in Design view and click the properties button, you see
a complete list of properties that enable you to control the appearance and behavior of
that object. Figure 4 shows the Properties dialog box for a form, with all available setting
organized by category on five tabs.




                                       Page 11 of 53
                                       Access 2003 Tutorial

Figure 5 The Design view properties of a database object include a much more important
list of settings that can dramatically change the way you see and use an objects.




Because the properties dialog box is modeless, you can leave it open as you work with
different object. The contents of the dialog box always match the currently selected
object. Rather that closing and reopening the dialog box as you edit a form, move it off
to the side where you can see its contents without hiding fields or controls on the form.
This technique is the most efficient way to adjust the properties of text boxes, subforms,
labels, and other part of a form or report.


Using Expressions in Database Objects

When designing database objects, you do not have to limit yourself to data stored in a
table. Extend the power of a database by writing expression to transform the data on the
fly. An expression is a combination of symbols, values, and identifiers (the name of a
field, control, or property) that calculates a numeric result, combines text, or produces a
logical value. Some of the operators you will use in expression include everyday
arithmetic operators: + (plus), - (minus), * (multiplication), and / (division). These are
the same arithmetic operators you use in Excel. Other operators used in expression might
be less familiar.

Expressions are useful throughout Access in may types of objects. The following are a
few examples:

       In a query, you might include calculated fields, in which each entry is the result of
        an expression. The operands in the expression might include other field in the
        same table or in a related table. You can use an expression to calculate a due date
        for an invoice ([SaleDate]+30) or to product a total, such as [Qty]*[UnitPrice].
        You supply the expression for the column and Access performs the operation for
        each record in the resulting Datasheet.


                                           Page 12 of 53
                                   Access 2003 Tutorial


      The design of an individual field in a table might include a validation rule, which
       specifies a range of acceptable entries in the field itself. You might create a rule
       that prohibits users from entering a value in the SaleDate field that is in the future
       or more than 30 days in the past. If a given data entry does not meet the condition
       expressed in the rule, Access rejects the entry. To create a validation rule, write
       an expression that will evaluate to True or False for each new entry. If the result
       is True, the entry is accepted; if False, it is not.
      A criterion is an expression that you can use to select a target group of records for
       a particular operation. Any record that meets the criterion becomes part of the
       group; a record that does not meet the criterion is excluded form the group.
       Again, a criterion expression results in a value of True or False for each record
       examined.

In these and other examples, you use specify types of operators in expression to produce
the appropriate types of values. The following categories of operators are commonly
used:

      The Arithmetic Operators -- In addition to the familiar four (+,-<*,/), these
       include ^ (exponentiation), \ (integer division), and MOD (the remainder from the
       division of two integers). These operators require numeric operands and produce
       numeric results.
      The Comparison Operators -- < (less than), <= (less than or equal), <> (not
       equal, > (greater than), >= (greater than or equal), and Between (expressing a
       numeric range). These operators produce logical values, indicating whether a
       comparison is True or False.
      The Logical Operators -- These take logical operands and product logical
       results. A logical operator might combine the values of two comparison
       expressions. Among these operators, the most commonly used are And (true if
       both operands are true), Or (true if one or both operands are true), and Not
       (produces the opposite value of an operand). Other logical operators include Eqv
       (true if both operands have the same value), Imp (true if the first operand is true
       and the second is false), and Xor (true if the operands have different values.

              WARNING: When using logical operators within a query, form, or
              report it is very important for you to have a good idea as to what the
              resulting dataset should be. Assuming you have used the correct
              operand can often result in bogus data.


      A String Operator -- The & symbol represent concatenation, the process of
       combining two text values. Example: [FirstName] &„ „&[LastName] in a report.




                                      Page 13 of 53
                                  Access 2003 Tutorial


                                                              Because expression are so
Figure 6
                                                              central to the design of
                                                              database object, Access
                                                              provide a special tool call
                                                              the Expression Builder to
                                                              help you write expressions
                                                              quickly and accurately. As
                                                              you see in Figure 5, the
                                                              Expression builder contains
                                                              button representing
                                                              operands, along with other
                                                              categories of identifiers that
                                                              might become part of an
                                                              expression.

                                                             You can generally open the
                                                             Expression builder by
                                                             clicking the Build button
                                                             (labeled …) next to the box
                                                             where the expression is
entered. Or, right-click inside the box and choose Build from the shortcut menu.

As you can see, the Expression builder is a very complex wizard and will require some
practice to become comfortable with its use. The easiest way to start using the expression
builder is to generate a simple expression you already know using the various buttons,
drop downs and options. In this way, you reinforce your use of the Expression builder
and start to understand the mechanics of using it.


Using Wizards to Create Databases and Objects

As noted earlier, Access offers a collection of wizards to help you create individual
object in a database that you are developing. In addition, Access includes another set of
wizards that create complete, special-purpose databases, devoted to specific business
procedures. These wizards create fully developed database applications designed to
handle common business tasks, such as expense accounting, time and billing
management, and order entry.

The wizard-generated databases create a full set of tables, queries, forms, and reports, as
well as a sophisticated database application. The centerpiece of each of these canned
databases is a form called a switchboard, which offers one-click options for viewing
tables, opening data-entry forms, running queries, and producing reports. The menus and
forms are tied together with VBA code, which you can easily inspect and borrow for
other applications.

To build a new database by using any of the database wizards, follow these steps:


                                      Page 14 of 53
                                 Access 2003 Tutorial



   1. Click the New button on the Access toolbar, or choose File, New. In the New File
      task pane, click the On My Computer link under the Templates heading.
   2. Click the Databases tab and select any of the database wizard‟s icons. Don‟t
      expect any help from the preview window on the right--all it shows is an abstract
      illustration intended to represent the functions of the corresponding database
      application.
   3. Click OK to start the wizard. In the File New Database window, enter a name for
      the database file the wizard will generate, choose a new location if necessary, and
      click the Create button.
   4. Follow each step of the wizard to read a brief description of the database
      application, select fields for specific tables, and choose formatting options, such
      as background images and presentation styles.
                                                                  5. After you have
      Figure 7
                                                                     completed all the
                                                                     steps, click Finish.
                                                                     The wizard generates
                                                                     the database and all
                                                                     its object and opens
                                                                     the main switchboard,
                                                                     such as the one shown
                                                                     in figure 6.




Figure 7 shows you the different canned databases you can select from. Sometimes, it is
easier to develop a database application by starting with a canned database and then
modifying it to fit your current needs.
       Figure 8




                                     Page 15 of 53
                                  Access 2003 Tutorial


By click on the Templates on Office Online button you can easily find list of other
templates that may fit your current needs.


       Figure 9




Exporting and Importing Data

Sooner or later--probably sooner-- you will want to transfer information stored in an
Access database to some other software environment, or move data originally created in
another program into Access. In some cases, you‟ll want to move entire tables between
database programs for use in different applications. You might want to copy a table of
supplier names and addresses from Access so that another database developer can
incorporate that data into an application created with SQL Server or Oracle. Or, if you
are building an Access database to replace an application create in another program such
as Excel. Often you will need to import this data at least twice--once when you begin
designing the database, so you can test forms, reports, and queries using real data, and a
second time when your are ready to switch from the old system to the new one.

Even when you are extremely careful, exporting and importing information between
database formats runs a serious risk of creating duplicate data sets. If you keep
information about customers and product in Access and in a SQL Server database,
whoever is responsible for data entry has to enter changes in two places, and it‟s almost
certain that some records will be out of sync or contain errors and inconsistencies. When
you must use the same data in two different database programs, you should choose one
program to store the data, and then create a link to that data from the other database


                                      Page 16 of 53
                                   Access 2003 Tutorial


program so that you can add or edit records or run queries. Because Access can link to
data stored in a variety of formats--including dBase, SQL Server, and Paradox--you will
most often want to store shared data in another program and create links to it from
Access.

In other cases, your need for Access data is strictly temporary. If you‟ve created a report
or query in Access, you can transfer the data to Word to incorporate it into a larger report,
or sent it to Excel, where you can easily analyze it with the help of PivotTables and
charts.

The simplest and efficient tools in this category are known as OfficeLinks; these shortcuts
are designed to send Access data directly to another Office application. To use one of the
three OfficeLinks options, first select a target object in the current database window, or
open a form, report, or query‟ then choose Tools, Office Links, or click the OfficeLinks
drop-down list on the Access toolbar. This list includes the following three links:

      Merge It with MS Word -- This option sends a table of data to a new or existing
       mail merge document in Word. When the transfer is complete, you can insert
       fields from the Access table as merge fields in the Word document.
      Publish It with MS Word -- This option creates a text field on disk--in RTF
       format from a selected Access object, and immediately opens the file in Microsoft
       Word. This file might become the starting point for a larger business document or
       report.
      Analyze It with MS Excel -- This option creates a worksheet file on disk (in
       Excel workbook format) from a selected Access object, and immediately opens
       the file in Excel. In the worksheet environment, you can perform mathematical,
       statistical, and other analytical operations that might not be possible in Access.
       You can also make use of Excel‟s versatile charting capabilities. If the report you
       start with include grouping, the resulting Excel worksheet will include subtotals
       as well.

You can also create a Word or Excel file by selecting an object in the database window
and the choosing File, Export. In the resulting Export dialog box, you can choose a
specific format (including formats for pervious versions of Office), and you can supply a
nondefault name for the file that will be created. You might prefer this approach to the
OfficeLink options if you need more control over the formation or the name of the
resulting file.


Importing external Text Files

In the initial development of a Access database, the ability to import current data from
another source can shortcut hours of data entry and database development time.
However, external data is seldom in a acceptable database table format and will require
some data manipulation. Data can exist in many different formats and repositories.
Incorporating external data into a database is an essential time saving task. There is no


                                       Page 17 of 53
                                   Access 2003 Tutorial


need to rekey existing electronically stored data, you can just import it. Of course,
Access can‟t possibly read all of the different types of data formats that exist, but you can
save data as a delimited text files in most applications. Delimited indicates that each
section of data is separated or defined by some sort of special character. For example, the
comma, the quote, and the space are very common delimiters. The data can then be
interpreted from this file and imported into Access. This same method can be used to
extract data from Access into other databases systems. Maybe your are going to import
the results of a Web-based survey and then analyze the data to summarize the results.


To import a delimit text file
                                                      1. On the File menu, point to Get
Figure 10
                                                          External Data, and then click
                                                          Import.
                                                      2. In the Import dialog box, in the
                                                          Files of type drop-down list,
                                                          click Text Files, and then
                                                          navigate to the file you want to
                                                          import.
                                                      3. Double-click the file, or Click
                                                          the file, and click Import.
                                                      4. The Import Text Wizard opens,
                                                          Click Next to accept a delimited
                                                          text file.
                                                      5. Select the First Row Contains
                                                          Field Names check box. The
       data is shown in the main pane of this page of the wizard. Click Next.
   6. Click Next to indicate that you want to store your data in a new table.
   7. Click Next to accept the field settings
   8. Click No primary key, and then click Next.
   9. Change the name of the table in you want, and then click Finish.
   10. Click OK to respond to the information box that notifies you that the import
       completed successfully.

You not have a new table listed in the database Container that is named by your selection
in step 9.
Figure 11
                                                  Figure 8 shows you some of the current
                                                  File types that can be imported into
                                                  Access in addition to a delimited text
                                                  file.




                                       Page 18 of 53
                                 Access 2003 Tutorial




Viewing Your Data

Figure 12                                       There are several ways to view your
                                                data. When you first create a table, you
                                                can use the Design View to set up the
                                                column or field names, data type and
                                                description.

                                                 After you have completed the process of
                                                defining the various fields within the
                                                Access database, you are ready to enter
                                                records. There are three different ways
                                                to generate a Access database table and
                                                the above example is the one used most
                                                often.

When you want to enter records or view all of the data in your file (table), you may use
the Datasheet View. Once again, this is one of several ways you may choose to view your
table data. It is the one most often used because it closely resembles the normal Excel
Figure 13                                     spreadsheet view of data. When you are
                                              creating a form, query, or report, you will
                                              also have access to that particular view.
                                              These view options are accessed from either
                                              the View Button on the Toolbar




Figure 14

                                              Or by double-clicking on the desired table
                                              icon within the Assess database form.




                                     Page 19 of 53
                                  Access 2003 Tutorial




Creating a New Table

By default, when you open a new blank database you will Create your table in Table
Design View. This is where the Field Names, Data Types, Description, and Field
Properties are defined.

1. Select the Tables Tab (selected by default)
2. Click on the New button
3. Select Design View option

Field Name    The field name is the heading for each field. It can be up to 64 characters
              long and can include spaces and some special characters; however, it is
              wise to keep the field name short, especially when you have numerous
              fields in your table. Spaces are often used to help define a fieldname. You
              should use the “underscore (_)” character for this because a space in a
              field name will cause problems when you are using the field name in other
              functions. Although it is possible to use some special characters as part of
              a field name, this is not a good practice. The use of upper, lower, or
              mixed case is optional. It is strongly suggested that what ever case you
              use be used throughout the various database table(s) you will be defining.
              There are several documented naming standards utilized by professional
              database designers and organizations.
The following URL will provide you with the naming standards for the National
              Park Service:
              http://www.nature.nps.gov/im/units/swan/Documents/Data_Managem
              ent/im_naming_guide.pdf

Data Type    The Data Type option is used to specify the type of data stored in a table
             field. Each field can store data consisting of only a single data type.
     Setting                                         Type
Text                 Text or combination of text and numbers that don‟t require
                     calculations.
                     Maximum size is 255.
Memo                 Lengthy text or combination of text and numbers. Maximum size is
                     65,535.
Number               Various forms of numerical data used in mathematical calculations.




Date/Time             Date and time values in various formats.


                                      Page 20 of 53
                                   Access 2003 Tutorial




Currency              Currency values involving data with 1 to 4 decimal places in
                      various formats.




AutoNumber            A unique sequential number assigned to each record. The number is
                      automatically entered for each new record. Useful as a Primary
                      Key. However, it may not prevent the actual duplication of data
                      which is the primary function of the primary key.

Yes/No                A check box with Yes and No values and fields that contain only
                      one of two values (Yes/No, True/False, or On/Off. Use the spacebar
                      to place or remove the checkmark for this cell.
OLE Object            A linkage to an object in another file or table.
Hyperlink             Data stored as text and used as a hyperlink address. A path to a file
                      on your hard drive, a LAN server, or a URL address.
Lookup Wizard         Creates a field that allows you to choose a value from another table
                      or from a list of values by using a list box or combo box. Clicking
                      this option starts the Lookup Wizard, which creates a lookup field.



In addition each field name has various attributes (properties) associated with it to help
define the specific field:




                                       Page 21 of 53
                                    Access 2003 Tutorial



Saving a Table Design
                                    Description is optional, but it helps you identify the field and can
Figure 15                           be viewed by the user. The description can be up to 255 characters




                                                        After a database object has been
                                                        designed, it must be named and saved.
                                                        1. Click on the File Menu option.
                                                        2. Click on Save or Save As.
                                                        3. You will be asked if you want to set a
                       Field name                       primary key (if you have not done so
                       Properties
                                                        already).



   NOTE: You do not have to set a primary key. You can always set one later.
4. Select the drive (A: for disk) you want.
5. Name the file. You can use up to 64 characters and spaces but keep it short when
possible.
   NOTE: Do not use a period or type an extension to the filename.


Entering Data into Your Table
Figure 16                                                                  1. Switch to Database
                                                                              View.

                                                              2. Enter the data into the
                                                                  appropriate cells. Notice
                                                                  this will look and feel
                                                                  the same as when you
                                                                  enter data in the cells
                                                                  within an Excel
                                                                  spreadsheet. However,
                                                                  there is a very subtle
                                                                  difference. In Excel
       input or changes to the spreadsheet are not saved until you specifically do a „save‟
       or „save as‟ or exit the spreadsheet. In Access as soon as you leave the row you
       are working on, the data is saved to the database table. You can however „undo‟
       any changes or input to the current row you are working on. This subtle
       difference can be a blessing and it can be a monstrous „OOPS‟.


   3. Use the Tab key, the “mouse” or the keyboard “arrows” keys to move to the next
      cell; use Shift +Tab or the “mouse” to move back a cell.


                                        Page 22 of 53
                                  Access 2003 Tutorial



   4. It is not necessary to resave your table after data entry or data editing because
      Access does this for you. However, if you are doing a lot of data editing to
      current data, it may be desirable to make a backup copy of the current table prior
      to the actual data editing to protect yourself against the „OOPS‟ principle. To do
      this:
           1. Select the table you will be editing
           2. Click on EDIT then Click Copy
           3. Click on EDIT then Click Paste. You will be prompted for a table name.
               Enter any name that will allow you to remember that this is a copy table.
           4. If you are successful with your data editing and you no longer need the
               copy table, you can click on the Copy Table and do an EDIT then delete to
               remove this table from the database. You can also click on the table and
               then click the delete key to do the same function.


Printing a Table

You can print your data either in Datasheet or Form View.

   1. Select either Datasheet or Form View
   2. Click on File Menu
   3. Click on Print Option
   4. If you want to set options for paper, graphics, orientation, etc., click on the
      Properties button
   5. You can also print a specific range of data and select how many copies you want
   6. Click OK when you are ready to print


Closing a Database

1. Click on File, select Exit OR
2. Click on the X in the upper right hand corner




                                      Page 23 of 53
                                Access 2003 Tutorial



SECTION II

Enhancing a Table

Using the Format Menu, you can do the following:

Figure 17                                    1. Change the Font size and type
                                             2. Change the appearance of the cell
                                             (gridlines, background, effect) and the
                                             text within the cell (color)
                                             3. Change Row Height (can also click
                                             and drag row height)
                                             4. Change Column Width (can also click
                                             and drag column width)
                                             5. Rename a Column
                                             6. Hide or Unhide Columns
                                             7. Freeze or Unfreeze Columns




Creating a Form from an Existing Table
Figure 18                                    The New Object Button can be used to
                                             create a new form in various formats or
                                             to generate a special ChatWizard or
                                             PivotTable your existing table. You also
                                             have the option of using the create form
                                             in design view icon or the create form
                                             using the form wizard icon located on in
                                             the table window.


                                               Be sure to select the table you
                                               want to use for this AutoForm.




                                   Page 24 of 53
                      Access 2003 Tutorial


Figure 19
                                   The AutoForm option automatically
                                   creates a new form using the fields and
                                   data of the selected table. In this
                                   example the AutoForm: Columnar format
                                   has been selected




Form Design View and Other Form Options

                                              With a form open in view
Figure 20                                     mode, you can click on the
                                              form header to open a form
                                              options popup menu. From
                                              this menu, you have several
                                              options for modifying the
                                              current form.
                    Clicking on the Form
                    Design option would
                    open the following
                    view of this form.

                                   Clicking on the AutoForm
                                   option would allow you to
                                   change the overall
  Figure 22                        appearance of the form
                                                               AutoForm Option
                                                               Menu popup

                                  Figure 21




                          Page 25 of 53
                                   Access 2003 Tutorial


Figure 23


                                                       Adding, Deleting, Moving,
                                                       and Modifying Fields

                                                       Once you are in the Design mode
                                                       you have several options to add,
                                                       delete or modify the appearance of
                                                       the fields within the form. Each field
                                                       will have an attached label which be
                                                       default will be the fieldname you
                                   ToolBox             choose when designing or importing
                                                       you table data. This label can be
                                                       detached, modified, or renamed as
desired. The actual field can also be modified using the various formatting options from
the format toolbar. It can also be moved, stretched or shrunk via the use of the mouse.
Click on the field and then select one of the field anchors.
Figure 24


                                                       Use the Toolbox popup menu to add
                                                       new objects to your form




                                       Page 26 of 53
                                      Access 2003 Tutorial




Using Shortcut Menus
Figure 25
                                                     Commonly used commands for
                                                     selected objects, controls or data may
                                                     be found on the Shortcut Menu that is
                                                     displayed when you right-click on the
                                                     mouse. This is a quicker way to access
                                                     menu options. All shortcut options are
                                                     found within the Menu options.




                                                     Hiding/Unhiding Fields

You also have the option to Hide/Unhide fields within your form. However, this is not
recommended. If you no longer need a field, it is better to remove if from your form. If
you determine later you do need this field, you can add it back via the toolbox popup
menu.

Edit, Add, and/or Delete Records (can be done in either Datasheet or Form View)

Figure 26                               Edit a Record You can add or remove text by
                                        clicking on the location and inserting the new text
                                        or by pressing the delete or backspace key to
                                        remove text. Pressing the Esc key will cancel any
                        Field to be     changes made in a field and return it to the original
                        modified
                                        data. Remember when you move to a new record,
                                        any changes made to the previous record will
                                        automatically be saved to the database.




                                         Page 27 of 53
                                       Access 2003 Tutorial



Figure 27
                                         Add a Record: Records can be added but only at
                                         the end of the existing records. Records can be
                  Go to the last
                                         rearranged later by using the Sorting option. In
                  Record form button     most cases records will automatically be rearranged
                  and click it.          when you close the form by the primary key of the
                                         table.
                                                                                          Number of Records
                                            Figure 28



                                                                                            New Record
                                 First Record
                                                        Next Record         Last Record



Figure 29                              Delete a Record: Right mouse click within the
                                       black tab area of the record you want to cut or copy.
                                       This selects the entire record. Click on the desire
                                       option. In a will designed database records from a
                                       master table may not be cut [deleted] if there are
                                       child records associated with the master records in
                                       other tables. Example, you can not delete a
                                       customer record from the customer table if there are
                                       invoice records associated to the customer record
                                       you are trying to cut. Access will popup a error
                              dialog box to inform you of this and not allow for the
                              deletion of this master record.
                  Edit Menu
Figure 30                                Find and Replace Records: You may need to find
                                         a specific record for editing or       informational
                                         purposes. You can find records by searching the
                                         entire form or by selecting a specific field for the
                                         search. After opening the form, Form View, click on
                                         the Edit Menu and select the Find or Find and
                                         Replace option.

                                       After completing the Find popup menu options,
                                       click on the Find Next button. Access will find the
first record within the database table that meets the Find What option. At this point, you
can edit the record and modify it as needed or by clicking on the Find Next button again
move to the next occurrence.




                                          Page 28 of 53
                        Access 2003 Tutorial



SECTION III

Working with date in the Datasheet view of a table:

Figure 31

                                               Options related to the
                                               Datasheet
                                               By clicking the right mouse
                                               button within the table header,
                                               you will open a popup menu of
                                               various options you can apply
                                               to the complete table. Three
                                               options are often used from this
                                               popup menu:




Figure 32
                                               Table Design: In this mode,
                                               you can:
                                                    change Field names
                                                    Add, remove, or modify
                                                      table field properties
                                                    Generate Lookup a
                                                      table with values for a
                                                      specific field
                                                    Change the Data Type
                                                      and/or its value




                           Page 29 of 53
                                   Access 2003 Tutorial


REMEMBER: Making changes to the Primary Key field(s) may or may not be allowed
once table relationships and data integrity rules have been applied. Access will notify
you if you are affecting table relationships or data integrity rules.


Figure 33                                                 Cut [Delete] a specific
                                                          record(s):
                                                          By holding down the shift key
                                                          and dragging the mouse through
                                                          a block of records you can
                                                          select multiple records for
                                                          cutting (deleting) from a table.

                                                          By holding down the ctrl key
                                                          and clicking on individual
                                                          records you can select a set of
                                                          records for cutting (deleting)
                                                          from a table.
                                                          Make modifications to the
                                                          layout and format of the
                                                          Datasheet

WARNING: If you select a record for cutting that has associated child records in
another table, you will not be allowed to delete this primary record unless special data
integrity rules have been turned on. [Cascading delete and/or Cascading update]
Figure 34


                                                        Datasheet
                                                        Selecting the Datasheet option
                                                        will allow you to Sorting allows
                                                        you to rearrange the information
                                                        so that you can look at it in a
                                                        different way. Remember these
                                                        datasheet changes will apply to
                                                        the overall datasheet.

                                                            Special Note:
                                                            The field to the far right of this table
                                                            with the “+” sign tells the user that
                                                            there is a special popup datasheet
                                                            associated with this datasheet. It will
                                                            usually be the associated child records
                                                            from another table within the database.




                                      Page 30 of 53
                       Access 2003 Tutorial




Changes to a Specific Column within the Datasheet

 Figure 35


                                     Right click on the column header to
                                     open the column popup menu options.

                                     The following options are often used
                                     from this method of working with data
                                     in the datasheet view:




Figure 36


                                     Sort Ascending or Sort Descending:
                                     By default tables are usually sorted in
                                     ascending order based on the primary
                                     key field(s). However, by clicking on
                                     the Sort Descending option, the table
                                     will be sorted in descending order of
                                     the selected column.


                                                                               Result of
                                                                               changes.




                          Page 31 of 53
            Access 2003 Tutorial


Figure 37


                         Hiding Column(s) Notice that the
                         Company Name field is no longer
                         visible within the current datasheet. By
                         selecting the column(s) to hide and
                         clicking the Hide Column command, the
                         selected columns will be hidden from the
                         datasheet view.

                           Clicking on Format and then selecting Unhide
                           will display this popup. Notice that Company
                           Name does not have a checked box. This means
                           that this field is hidden. You can unhide it by
                           clicking on the checkbox.
Figure 38




                               Figure 39




               Page 32 of 53
                                   Access 2003 Tutorial



Figure 40
                                                     Freezing Column(s):

                                                     When working with a large table with
                                                     a lot of different columns, the use of
                                                     the Freeze Column options can be a
                                                     very effective method for data entry or
                                                     data editing.

                                                     Notice: You can NOT freeze
                                                     disjointed columns. They must be
                                                     adjacent to eachother.



Figure 41
                                                   Column Width
                                                   There are two methods of changing the
                                                   column width. The first and most often
                                                   used method is to click on the column
                                                   header. Move the mouse to the far right
                                                   edge and drag the column to its new
                                                   desired size.

                                                   The second method is much more
                                                   accurate because you will be defining the
                                                   new column width from a column width
                                                   popup menu when you click on the
                                                   Column Width option

                                                     Figure 42
Notice this popup will allow you to modify the
actual width (user inputted), select the default
standard width, or do a Best Fit.


Find Option: The find option is not any
different then selecting using the find option from the Edit menu drop down. It is not
specific to a column of data. It is just another way to find data within the table.




                                      Page 33 of 53
                                 Access 2003 Tutorial


Figure 43                                    Delete Option: When you select the
                                             Delete Option, you will be presented this
                                             popup to verify that you really want to do
                                             this. If you select yes, you will be
                                             modifying the database table and this
                                             process may affect child tables. It there
                                             are data integrity rules in effect for this
                                             column, you may or may not be allowed to
                                             delete the column subject to how the
                                             cascading delete and/or cascading update
                                             rules have been applied.

                                                 WARNING: Before performing any
table field deletes it is a normal Best Practice rule to backup the database.



Figure 44
                                             Rename a Column:

                                             When you select the Rename option the
                                             column header will be highlighted. Type in
                                             the new Column name and click the enter
                                             key. The Column will be renamed in the
                                             underlying table. If this column is used as
                                             a foreign key in another table, the foreign
                                             key table name will NOT be changed.

                                                NOTICE: The key to relationships
between tables is not the name of a field but the field type.




                                     Page 34 of 53
                                   Access 2003 Tutorial



SECTION IV

Query Introduction

Queries enable you to extract a subset of data from a single table, from a group of related
tables, or from other queries, using criteria you define. By saving a query as a database
object, you can run the query at any time, using in the current contents of the database.
When you display when you display a query in Datasheet view, it looks exactly like a
table; the critical difference is that each row of the queries results can consist of fields
drawn from several tables. Queries can also contain calculated fields, which display
results based on the contents of other fields.

CAUTION            Be careful when working with queries. Queries typically contain
                   live data. If you change data in a query data sheet or form, it
                   changes in the underlying table(s) as well without any warning



Using Queries to Extract Data from a Database
Queries are database objects that enable you to extract data from a database to use in
another way--as the source of data a used in a printed report or to produce a list of items
for use in a look up control on a data entry form. A query can be based on a single table
or on multiple related tables. In addition to field drawn directly from tables, a query can
also contained calculated fields that transform data--adding sales tax to an invoice
amount or performing statistical analysis(totals, averages, and the like) on group on
groups of records drawn from multiple tables.

Like other office wizards, the access query wizards are efficient at guiding you smoothly
through the steps of a complex process, providing detailed explanations of the chores you
need to make, and enabling you to view graphic representations of the results.

Although design view is a more versatile environment in which to create queries, it‟s
often easier to begin by using a wizard to create a basic query. After you finish with the
wizard, you can then open the query in design view to modify the results.

To view a list of available quarry wizards, press at 11 to open the database window, and
then choose Insert, Query. The New Query dialog box lists four query wizards. Double-
click any of the options to launch a wizard.




                                       Page 35 of 53
                                  Access 2003 Tutorial



TIP    In their Air Force to simplify task, query wizards sometimes unnecessarily restrict your
       choices. In a Crosstab query Wizard, for example, you must base your new query on a
       single existing table or query. If you want to use more than one table, you must first create
       a query that contains all of the target fields. By contrast, if you create a Crosstab Query in
       designing view. You can add fields from two or more related tables



Choosing the Right Query Type

Access enables you to create several types of queries. The most common is a select
query, which extracts information from one or more tables. You can also create Crosstab
quarries, which group and summarize information in row-and-column formats such as an
Excel PivotTable. Some of the most powerful (and potentially dangerous) things you can
do with access involved action queries, which actually change the data in the underlying
table based upon criteria you defined in the query.

Like queries, filters enable you to work with a subset of records in a database. Filters
offer a quick way to temporarily limit the display of records in Datasheet or Form views.
You can create a filter by entering data in a form or by making a selective in datasheet
view. Even though the display of data is filtered, it still represents live data, not a
separate copy as in a report. If you enter changes in a Datasheet view based on a query,
Access changes the data in the underlying table.

TIP    Filters represent an excellent way to create a query without diving into the
       sometimes confusing Query Design view. For instance, you can open a table in
       Datasheet view and select a fragment of data in a single field (the word nylon, for
       instance). Create a filter based on the selection, and then switch to Advanced
       Filter/Sort view to save the filter as a query (Products That Contain the Word
       Nylon) that you can reuse anytime.



Figure 45                                         To create a new query, choose Insert,
                                                  Query, or choose Queries in the
                                                  database window and then click the
                                                  New Button. The New Query dialog
                                                  box lets you start from scratch in
                                                  Design view or use a wizard to build
                                                  one of several specify types of queries.




                                      Page 36 of 53
                                   Access 2003 Tutorial


SELECT QUERIES
When you use design view to create a new query from scratch, access creates a select
query a default. As the name implies, the purpose of select query is to gather data from
want or more tables, and to present it in a format that you save as part of the query itself.
Select queries can include any of the following elements:
     Fields drawn from want or more tables or queries. You can base a query on
        another query, a comment to make when you want to create a summary view of
        data gathered from multiple tables. Access uses declined relationships to match
        records from different sources and a find relevant connections between the data.
        You can also define a new relationship between tables and/or queries and define
        them as part of the query.
     Calculated fields, which display the results of expressions, using fields from want
        or more source tables.
     Totals, which performs the statistical operations, such as some an average, on
        fields from a source table.
     Selection criteria, which the buying the specific set of records the query will
        return. For example, in an invoice is table, you might define criteria for the
        InvoiceDate field to return only invoice is prepared in the past 30 days.
     Sorting instructions, which arrange the query results in numerical, alphabetical, or
        Chronicle order by want or more columns.
     Hidden fields, which are included for the purpose of defining criteria or sorting
        instructions, but are not actually shown in the query‟s results.

Figure 46                                       When you save the query, you save the
                                                instructions for achieving and display and
                                                records from the database, not the records
                                                themselves. As a result, running and
                                                saved quarry always display is the current
                                                dataset.

                                                Notice the last column, which contains a
                                                calculated field that multiplies the retail
                                                price by the current quantity on hand for
each product. The label in front of the formula in the third column defines the name of
the calculated field. Use a colon to separate the label from the formula used to calculate
the field results.
Figure 47
                                      In Design view, a query includes two panes: The
                                      top pane contains field list for each table and query
                                      used as a data source; this pane also show
                                      relationships between the data sources. The lower
                                      pane contains a grid with one column for each field
                                      that makes up the query. When you design a query,
                                      you can drag any field reference directly from the
                                      lists in the upper pane, or you can choose from drop-
                                      down list that appear when you activate a given


                                       Page 37 of 53
                                  Access 2003 Tutorial


column in the grid. (Double-click a field name to quickly add it to the grid.) The You
can enter calculated columns manual or with the help of the Expression Builder.

      The first step in using the expression builder is to click on an empty column in the
       lower pane (grid).
      Then right click the mouse to open up the option menu as shown.
      Select the Build option to open the Expression Builder wizard.


In the rolls below each field name, you can see specifications that explicitly determine
the content of the query:

      The Table row shows the source of each field. This row is visible by default.
      The Total row lets you specify operations to be performed on that field -- Sum,
       Average, and so on. This row is normally hidden. The default selection is Group
       By, which displays all values in the selected field without performing a
       calculation.
      The Sort row specifies whether a particular column will be used for sorting, and is
       so, whether the sort is in ascending or descending order. If you specify a sort
       order in multiple columns, Access sorts by each column, going in order from left
       to right.
      The Show row contains a check for each field that will be displayed as part of the
       query‟s result. Clear this check box when you want to use a field for sorting or
       filtering but you don‟t want it to appear in Datasheet view.
      The Criteria rows contain one or more criterion expressions for determining
       which records will be included in the query. Example: today() - 30 would show
       only records for the past thirty days. Today() is a built-in function.


The Expression Builder dialog box [wizard] is somewhat complex and will require some
Figure 48                                experimenting to learn how to use it effective.
                                              Note: Simple rules for building calculated fields:
                                              Function Format - Sum((«expr»)),Avg («expr»)
                                              Field Names - [UnitPrice], [UnitsOnHand]
                                              Arithmetic Operators - +,-,*,/
                                              Comparison Operators - <=,+,>=,>
                                              Logical Operators and, or, like not like
                                              String Operators & concatenation system

                                              Put it all together for a simple calculated field:
                                              Inventory: Sum([UnitsInStock]*[UnitPrice])




                                      Page 38 of 53
                                  Access 2003 Tutorial




CROSSTAB QUERIES
Another kind of query, known as a Crosstab, transforms record-oriented data into a
summary view that resembles an Excel PivotTable.


  Figure 49                                       Step One: Select Crosstab query
                                                  wizard from the New Query dialog box
                                                  and then select the table, row headings,
                                                  column headings and function. The
                                                  last step will be to provide a name for
                                                  this query when you click on the next
                                                  button. Access will provide a default
                                                  name you can accept or modify as
                                                  desired.




 Figure 50                                       In the Design window of a Crosstab
                                                 query, select fields for row heading,
                                                 column headings and values. The
                                                 Crosstab Query Wizard fills in these
                                                 values automatically. However,
                                                 creating a Crosstab query from scratch
                                                 in the Design window is fairly
                                                 straightforward process, but it‟s much
                                                 easier when you use the Crosstab
                                                 Query Wizard.



PARAMETER QUERIES
Normally, saving a query includes all the criteria you‟ve defined for that query. If you
want to see all sales results by product for all vendors, it‟s easy to save a query that
extracts those results from the current contents of the database each time you run the
query. But what do you do when you want to specify slightly different criteria every time
you run the query? For instance, what if you want to enter a specific vendor number or a
maximum price when you run a query? For that task, you need a parameter query.

Each time you open a parameter query, Access displays a dialog box asking you to enter
a piece of data to be used in the selection criteria for the query. You define the input
prompt as part of the query‟s definition.




                                     Page 39 of 53
                                   Access 2003 Tutorial



Figure 51                                                 To create a parameter query,
                                                          open the query in Design view
                                                          and click in the Criteria box for
                                                          the field in which you want to
                                                          add selections criteria. The
                                                          expression should include the text
                                                          you want to display as the input
                                                          prompt, enclosed in square
                                                          brackets where you would
                                                          normally enter a constant value.
For example the query shown to the right prompts you to enter the minimum retail price
you want to use as the selections criterion in a select query.

The value the user enters into the prompt becomes the parameter in the expression, which
in this examples specifies a selection criterion for the query of $1.00 dollar.

The simplest parameter queries include a single value, but you can also use wildcards or
multiple parameters as part of a query. The following examples should give you some
ideas:

      To create a input dialog box that prompts the user to enter the beginning of a
       search string, use an asterisk with the parameter. For instance, entering like
       [Enter beginning of product name & “*” will search for all records in which the
       specified field begins with the value of user inputs.
      To search for a string anywhere in a given field, use two asterisks: Like
       “*”&[Enter any text that appears in the product name]&”*” will do the trick.
      To define a beginning and ending range of numbers or dates, use two parameters
       in a single expression, such as: Between [Enter beginning date] And [Enter
       ending date]. When you run a query with multiple parameters, Access displays
       an input dialog box for each one.


ACTION QUERIES
An action query potentially changes the data in an existing table, or creates a new table.
Access enables you to create four kinds of actions queries:

Figure 52                                         An update query replaces data in
                                                   existing records. In the designing of an
                                                   update query, you write selection
                                                   criteria to identify the target records,
                                                   and you prove an expression that
                                                   generates the replacement data. Use an
                                                   update query to change a group of
                                                   records at once--when an area code
                                                   changes, for example, or to make an


                                       Page 40 of 53
                                Access 2003 Tutorial


      across-the-board price increase. The example shown shows the properties of an
      update query that include a parameter: It adds 5% to the amount in the UnitPrice
      field for all records that match the supplier code you enter.
     A make-table query creates a new table
      object from the result of the query
      itself. For instance, you might build a
      query that produces a list of all
      customers who have not ordered
      product from you in the past year and
      copy those records into an Inactive Customers table. This type of query does not
      affect the underlying source data. You can choose a table from the current
      database or from another database file. If you enter the name of a table that does
      not currently exist, access creates it for you.
     An append query adds new records to an existing table from a source query. This
      type of query is most commonly used when importing data from an external
      source. Append queries enable you to take some or all of the imported
      information and move it into an existing set of tables in the correct format. When
      you choose Query, Append Query, Access prompts you to enter a table name by
      using a dialog box that is identical to the one for a make-table query.
     A delete query removes records that match specified selection criteria for an
      existing table. You might use a delete query to prune outdated records from a
      database. When you create a delete query, Access adds a delete row to the
      query‟s design grid.

     TIP           Want to archive information from a database table? Run a make-table
                   query to copy records that meet specific criteria, such as customers
                   who have not placed an order in more than a year. Then, aver
                   running that query, use the same selection criteria as part of a delete
                   query to remove the same records from the original table.




CAUTION            Running an action query can absolutely and irrevocably scramble
                   your data, and in most cases the effect of an action query cannot be
                   undone. If you inadvertently run an action query that doesn’t behave
                   as you expected, you might find it difficult or impossible to restore
                   your original data. For this reason, you should proceed with great
                   care when designing and running an action query. At a minimum,
                   make a copy of the original table under another name, so that you can
                   restore the data if your action query has unintended consequences.




                                    Page 41 of 53
                                 Access 2003 Tutorial




Following are two fail-safe mechanisms that can prevent a data
disaster:

     Before you begin designing an action query, create a copy of the table you intend
      to modify. In the database window, select the table icon, press Ctrl+C to copy it
      to the Clipboard, and then immediately press Ctrl+V . In the Paste table as dialog
      box, enter a name such as Copy of Tablename, choose the structure and data
      option, and click OK to create the copy. If the action query does not work
      properly, you can return to the original state by deleting the modified table and
      renaming the backup copy the same name as the original.
     Before running an action query, test its effects by designing a select query that
      uses the same selection criteria. When you open this query in Datasheet view,
      inspect the results carefully; because these records will ultimately be the same
      ones that change as a result of the action query. After inspecting the results,
      return to Design view and choose Query, followed by the menu choice for the
      type of query you want to create. This will not guarantee success but it might
      point out a logic error.
      If the selected table has referential integrity rules applied to it, some action
      queries will not work as a result of the desired action. For instance, if you are
      trying to remove old records from the customer table that still have child records
      in the invoice table, Access may not allow for the deletion of a parent record until
      the child records have been deleted first. The exception would be if the cascading
      delete option was turned on within the parent table referential integrity definition.



CAUTION
                   You can save an action query for future use, or you can
                   abandon it after a single use. If the purpose of the query
                   is to perform a one-time maintenance chore, get rid of the
                   query when you no longer need it. That way you do not
                   risk accidentally corrupting your database. The only
                   reason to save a query is if you expect to reuse it as part of
                   ongoing database management-for example, in a monthly
                   database cleanup routine.




                                     Page 42 of 53
                                  Access 2003 Tutorial




DEFINING QUERY PROPERTIES

In addition to the settings discussed so far, which are available in the design grid for a
query, you can also adjust a host of settings that apply to the entire query. Open a query
and switch to Design view. If necessary, click the Properties button to display the
Properties dialog box, and then click anywhere in the background area of the query
design window.




Figure 53




                                                                      
      Top Values - Returns a specified number or percentage of records. This setting is
       most often used in conjunction with a sort setting; to see the 10 most expensive
       products.
      Unique Values - Returns a query result in which no duplicate records exist.
       Choose Yes if you want to extract a unique set of values from a database. Access
       eliminated duplicates from the result set based on records visible in the query‟s
       result, not on the contents of the underlying table or tables.
      Unique Records - Returns a query result after eliminating duplicate records in the
       data source. Depending on the fields you choose to display, you might see
       duplicate values in the query results.


                                      Page 43 of 53
                                   Access 2003 Tutorial


      Column Headings - This property, used only in Crosstab queries, lets you limit
       the columns to be displayed. Separate entries with semicolons. In a data source
       that contains a Regional Office, field, for example, you might specify
       East;West;Midwest in this property. Access ignores all other values when
       performing the Crosstab query and displays these three columns in the specified
       order.
      Output All Fields - Specifies that you want the query to return all fields from all
       tables included in the query, regardless of whither the field name is on the design
       grid or the Show box is checked. When you set this property, you need to add
       fields to the grid only to set Criteria and Sort properties.
      Link Child Fields, Link Master Fields - Used to set the relationship between a
       main form and a subform or other embedded object. Normally, Access sets this
       property automatically based on relationships you define between the tables.




Saving a Query

1. Click on the File menu and select the Save or Save As option.

NOTE: Any new data that is later entered into a table and that meets the criteria will
appear in the query.


Using all Fields of a Table for a Query

There are three ways of selecting all fields of a table for a query:
    The first way -When a selected table appears in the top pane of the Query Design
       Window, the first field option is the asterisk (*) which represents all fields in the
       table. If this option is selected, only the table name will appear in the lower pane.
    The second way - Click in the table name header (blue area) and hold the mouse
       button down. Now drag the table name to an empty grid column. Release the
       mouse button and all fields within the table will be added to the grid. If there is a
       field you do not want, click on the area just above the field name [column should
       turn black] and hit the delete key.
    The third way - Select the Output ALL fields from the query properties menu.
       Remember this will select ALL fields for all tables defined in the query. They do
       not have to be displayed within the query grid.

Moving a Column in a Query

1. Click on the Datasheet View of the Query.
2. Select the column by clicking on the field name (column will be highlighted).
3. Click again on the field name holding the mouse button down.


                                       Page 44 of 53
                                   Access 2003 Tutorial


4. Drag the line to the vertical grid line that will become the new left boundary of the
moved column.
5. Release the mouse button to insert column in new location.

Renaming a Query

1. Click on the Query Tab
2. Highlight the name in the Query database window list
3. Click again to activate the edit mode
4. Type the new name

Printing a Query

A query can be printed in the same manner used to print other database files. The printout
will include both a header, containing the query name and current date, and a footer
containing the page number.




                                       Page 45 of 53
                                   Access 2003 Tutorial



SECTION V

Building Great Forms and Reports

Access provides a design and development environment that gives you extensive control
over the appearance and functionality of forms and reports. The tools you use for either
task are remarkably similar. The differences typically reflect the different design goals of
forms and reports, as explained here:

      A form is a formatted database object, generally used to display one record at a
       time in an onscreen window. Forms are most commonly used to create
       convenient the until then, the blanks windows for entering or editing data symbol,
       in this case, you use a table or query as the data source for the form.
      Access reports typically organize data in a format suited for printing or
       publishing. Although you can also using a form to view data onscreen, reports are
       better suited for this task and often represent the most important in product of a
       database

When designing a form or report, you start by specifying a data source--one arm one or a
one or more tables or queries, or as stated written in SQL--and position controls on a
design. Although you can create a report, using a wizard is often a better starting point.
The wizard produces the best structure, and you then open the form or report in design
view to make detailed changes to its content and appearance.

As with other database objects, Access provides several ways to create a form or report.
After selecting Forms or Reports from the database window, click to tilt the New Form or
New Report dialog box. The option shown here let you create a new form or in Fort by
opening a blank form in design view or by choosing wizards and AutoForm/AutoReport
options that enable you to create a default form or report with a single click.

To open an existing form or report in Design view, selected from the database window
and click the Design button. If the form or report is already opened, click the Design
button to switch into Design view.


DESIGN 101: WORKING WITH CONTROLS

The building blocks of any form or report are object objects called controls, which
include text boxes, labels, option buttons, list, command buttons, toggles, and other
familiar Windows interface elements. Controls have their own property settings, as do
individual sections of the form or report; by changing the settings of these properties, you
can modify the appearance and content of the form or report. Controls can take any of
three forms.


                                       Page 46 of 53
                                   Access 2003 Tutorial


      Some control some controls are directly tied to a field in a table or query. In a
       peculiar jargon of Access, these are called bound controls. When you enter data
       in a control that is bound to a particular field, Access adds the data to that field;
       when you view data by using a form or report, Access checks the Control Source
       property for each control to see which data is should display.
       Figure 54                                              Figure 1 shows the Properties
                                                              dialog box for a text box
                                                              bound to the CatagoryName
                                                              field in the underlying table.

                                                              Two additional dialog boxes
                                                              are also shown:
                                                                          o Product by
                                                                            Category field
                                                                            names
                                                                          o Form/Report
                                                                            ToolBox
                                                                            controls dialog
                                                                            box.



                                                                     Some controls are
                                                                      unbound--that is, not
                                                                      tied to any data
       source. For example, a line, box, clipart, picture, or freestanding text label is an
       unbound control.
      When you enter an expression in the Control Source property box, Access creates
       a calculated control. The expression = [SupplierPrice]*2, for example,
       multiplies the contents of the SupplierPrice field by 2 and displays the result.

When you open a form or report in Design view, you can change the font, font size,
color, borders, and other formatting properties of any control. In Design view, Access
lets you position controls in a grid for precise alignment. You can also group and align
controls.

When you work with a form or report in Design view, three interface elements are
essential:

      Use the Toolbox to add new controls or change existing controls. You can
       customize the Toolbox buttons just as you can any other Office toolbars; to
       toggle it on or off while you work, click the Toolbox button or choose View,
       Toolbox.




                                       Page 47 of 53
                                     Access 2003 Tutorial


      The Field List displays a list of all the fields in the source query or table, which
       you can use to add new controls. To show or hide this list in Design view, click
       the Field List button or choose View, Field List.
      To adjust the appearance or behavior of a control, section, or the form itself, open
       the Properties dialog box. You can keep the Properties dialog box open while you
       work; as you select different objects, the properties displayed in this dialog box
       change to reflect the available choices.

TIP         Tabs in the Properties dialog box make it easier to find the exact function you are
            looking for. All the tabs that affect the appearance of a control, for example, are
            on the Format tab. Click the All tab to scroll through a list of all the perperties
            that apply to the selected object.



ADDING A NEW CONTROL TO A FORM OR REPORT

If you drag a field name from the Field list onto a form or report, Access automatically
creates a text box control bound to that filed. If you click another Toolbox button first,
and then drag a field onto the form, Access launches a wizard that create the control type
you selected. Figure 2, for example, show one step of the Option Group Wizard. Follow
the wizard‟s instructions to define the data source and behavior of the control.

Figure 55
                                                         If the Control Wizards button on
                                                         the Toolbox is selected when you
                                                         add a new control to the form or
                                                         report, Access lets you fill in the
                                                         control’s properties with a wizard




                                                      The Toolbox contains buttons for
                                                      common controls you might want
                                                      to add to a form or report. By
positioning the mouse pointer over a button in the Toolbox, you can view a ScreenTip
that show the name of the control itself. Some of the more common and useful controls
include the following:

                Check boxes, which let users enter data in a Yes/No field or an option
                 group.
                Combo boxes, which let the users enter data from a drop down data list
                List boxes, which let the users enter data from a data list.
                Labels, which add descriptive text to a control or a form or report.



                                         Page 48 of 53
                                  Access 2003 Tutorial


NOTE         Labels are always unbound, and they don‟t change as you move from one
             record to another. Access automatically adds labels to new fields you
             place on a form or report; you might also use labels for titles and
             instructions.



POSITION CONTROL AND LABLES

When you use the Form Wizard to build a form, the default type for all controls is a text
box with a label attached to its left. In some cases, however, you‟ll want the labels to
appear above the text box, and you might want to change the position, alignment, size, or
grouping of controls on the form. After you learn the secrets of working with Access
controls, you‟ll find it easy to position controls precisely where you want them. It does
take some practice.

Access display handles -- small black rectangles -- around the outside of a selected
control, as shown in Figure 3; These handles are a visual indication that you‟ve selected
                                                              the control. The eight
        Figure 56
                                                              small black squares are size
                                                              handles -- you‟ll find one
                                                              on each corner and one in
                                                              the middle of each side of
                                                              the selected control. When
                                                              you position the mouse
                                                              pointer over a size handle,
                                                              it takes the shape of a
                                                              double-headed arrow, at
                                                              which point you can drag
                                                              the handle in any direction
                                                              to change the size and
                                                              shape of the control itself.




Note that both the control and its label are selected. The “open hand” pointer means that
you can move both the control and the label at once.

The large black square at the upper-left corner of a selected control lets you move the
object to a new position within the form. When you drag this handle, the mouse pointer
takes the shape of a hand with one finger pointing up; as you move the selected control,
you see only its outline, making it easier to position on the form grid. When you release
the mouse button the control itself moves to the location you‟ve selected.




                                      Page 49 of 53
                                   Access 2003 Tutorial


For the most part, every control you add to a form or report actually consists of two
controls; the bound or unbound control (text box or combo box, for example), and the
matching label. If you know the techniques, it‟s easy to position these controls correctly.

      Use the large square in the upper-left corner of the control or the label to move
       either one independently. This technique is effective if you want to move a label
       from the left of a text box so that is sits above the control.
      To move both the control and its label at once, point to any border of the control
       or the label, until the mouse pointer takes the shape of an open hand. Drag to
       position the control-label combination in its new location.

   TIP         Do you want to position an object precisely on a form? If you plan to
               print out an Access form and use it as an invoice, you might want a
               graphic to appear in a fixed location at the top of the form. To add the
               graphic, choose the Image button from the Toolbox. Then click and
               drag to define a region on the form, in the general location where you
               want it to appear. Select the image file from the Open dialog box.
               Finally, click the Format tab on the Properties dialog box for the image
               you embedded, and set the Left, Top, Width, and Height properties to
               define the exact size and location on the page.

       Figure 58


                                                Special Note: The label associated with
                                                a control on a form or report will always
                                                be the FieldName from the underlying
                                                table or query unless you have defined a
                                                Caption name in the Field Properties
                                                section of the Field Name in the
                                                underlying table.
                                      Caption name




MAKING FORMS EASIER TO USE

A well-designed form makes data entry easier and more accurate, especially when you
want other people to enter data into a database. By limiting the data the user sees,
carefully arranging input boxes and providing explanatory text, you can guide the user


                                       Page 50 of 53
                                   Access 2003 Tutorial


through the data-entry process. Often the use of a combo box or list box using a lookup
table or predefine list will help maintain data integrity within a given field of data.

A form can include as many as five sections. The data itself typically appears in the
Details section; in addition, each form can have up to two headers and two footers, which
one header/footer combination for the form itself and another for individual pages. Use
the View menu to hide or show headers and footers.

Simple forms generally show the contents of one record at a time, but you can also design
a form that include a subform, which displays information from a related table or query.
If you choose a table that includes a subdatasheet and then create an AutoForm, Access
                                                                       adds a subform
        Figure 56
                                                                       automatically.
                                                                       Using this type of
                                                                       form, you can
                                                                       scroll through
                                                                       groups of records,
                                                                       or search for
                                                                       information by
                                                                       using filters and
                                                                       other search tools.
                                                                       Figure 4 shows a
                                                                       form and subform
                                                                       combination in
which the main forms is bound to the Customers table and the subform is bound to the
related Products table. Use the navigation buttons to move through the list of orders.


MAKING REPORTS EASIER TO UNDERSTAND

Access reports are organized into horizontal sections that are laid out in a specific order.
Understand how to work with each section is a crucial step in designing an effective
report. Figure 5 show a basic report that illustrates some commonly used sections.
       Figure 59
                                                                             Each section in
                                                                             an Access
                                                                             report contains
                                                                             a different type
                                                                             of data.
                                                                             Headers and
                                                                             footers set off
                                                                             groups of data
                                                                             and pages;
                                                                             items you place
                                                                             in Details



                                       Page 51 of 53
                                  Access 2003 Tutorial


section repeat as needed.




      The Report Header and Footer appear at the beginning and end of the report. A
       report header often include the title of the report and a calculated control that
       contains the expression =Date() to display the date the report was printed. Report
       footers often contain grand totals or averages for the data within the report. To
       hide either section, change its Visible property to No.


                With a modest amount of creativity, you can turn a report header into a
   TIP          dramatic title page for a report. If the report header isn’t visible in Design
                view, choose View, Report Header/Footer to make it appear. Next, drag the
                bottom border of the Report Header section to make it occupy as much of
                the page as you need. In the Properties dialog box for the section, set the
                Force New Page property to After Section. Finally, add any text labels and
                graphics you want, and set the background color if necessary.



      The page Header and Footer appear at the top and bottom of each page, even if
       the Detail section is a continuation of data from the previous page. Page headers
       are commonly used for column heading, so readers can follow a lengthy list, and
       pages footers are useful for dates, and page numbers.

                If you’ve grouped data using a field that contains date information, use the
   TIP          Group On option to arrange it by interval- month, quarter, or year. By
                combining this header with other groupings, you can see a list of all sales by
                customer by month, even if the data appears only by day.



      Group Header and Group Footer sections appear automatically when you define
       grouping and sorting options for a report. By placing calculated fields in either of
       these sections, you can display summaries of the data within each group.


                If you want to start a new page for each grouping, open the Properties
   TIP
                dialog box for the Group Footer section and set the Force New Page
                property to After Section. If this section is not visible, set this property for
                the Detail section instead.



                                      Page 52 of 53
                                Access 2003 Tutorial




Creating a Report with the Report Wizard
     Figure 57
                                                                1. Open the database
                                                                   that you want to use
                                                                   to create your report.
                                                                2. Select the Reports
                                                                   tab in the Database
                                                                   window.
                                                                3. Select New and then
                                                                   select Report
                                                                   Wizard
                                                                4. Select the type of
                                                                   report you want to
                                                                   generate.
                                                                5. Select the data
                                                                   source, either a table
         or a query
     6. Click the OK button to move to the next Report Wizard page which will be
         different depending on the report type you choose
     7. Respond to the questions For example the standard Report Wizard would ask
         you to respond to the following:
             Fields - Highlight the field and click > to move fields across (click >> to
             move all fields)
             Grouping levels – subtotals or subsets
             Sort order – if you want to sort on any fields
             Layout, Orientation, and Column Width setting
             Style – for the report
             Report title and print options
     6. When you are finished, the report will be displayed in Layout Preview.




                                    Page 53 of 53