Access Tutorial 3 Maintaining and Querying a Database - PowerPoint - PowerPoint

Document Sample
Access Tutorial 3 Maintaining and Querying a Database - PowerPoint - PowerPoint Powered By Docstoc
					 Access Tutorial 3

 Maintaining and
Querying a Database



                      COMPREHENSIVE
Objectives                                         XP


• Find, modify, and delete records in a table
• Learn how to use the Query window in Design
  view
• Create, run, and save queries
• Update data using a query datasheet
• Create a query based on multiple tables
• Sort data in a query
• Filter data in a query

New Perspectives on Microsoft Office Access 2007        2
Objectives                                            XP

• Specify an exact match condition in a query
• Change the font size and alternating row color in a
  datasheet
• Use a comparison operator in a query to match a range
  of values
• Use the And and Or logical operators in queries
• Create and format a calculated field in a query
• Perform calculations in a query using aggregate
  functions and record group calculations
• Change the display of database objects in the
  Navigation Pane

New Perspectives on Microsoft Office Access 2007           3
Updating a Database                                                     XP

• Updating, or maintaining, a database is the process of
  adding, modifying, and deleting records in database
  tables to keep them current and accurate
       Navigation mode – Access selects the entire field value.
       Editing mode – You can insert or delete characters in a field
        based on the location of the insertion point.




New Perspectives on Microsoft Office Access 2007                             4
Open Belmont and Modify a Record                          XP

• Open the Customer table in Datasheet view.
• The record you need to modify is near the end of the
  table and has a Customer ID field value of 11087.
• Enter the following values for fields in this record:
   Phone field – 269-963-0190
   Address – 1752 S Main St.
   City – Battle Creek
   Zip – 49014
• Close the Customer table.


New Perspectives on Microsoft Office Access 2007               5
Finding Data in a Table                                XP

• The Find command allows you to search a table or
  query datasheet, or a form, to locate a specific field
  value or part of a field value.
• Open the Contract table and use the Find command in
  the Find group of the Home tab’s Ribbon to find the
  record with Contract Num = 3101




New Perspectives on Microsoft Office Access 2007            6
Deleting a Record                                  XP


• With the table in Datasheet view, click the row
  selector for the record you want to delete
• In the Records group on the Home tab, click the
  Delete button (or right-click the row selector for
  the record, and then click Delete Record on the
  shortcut menu)
• In the dialog box asking you to confirm the
  deletion, click the Yes button


New Perspectives on Microsoft Office Access 2007        7
Deleting a Record                                  XP




New Perspectives on Microsoft Office Access 2007        8
Introduction to Queries                                       XP

• Access provides powerful query capabilities that allow
  you to do the following:
       Display selected fields and records from a table
       Sort records
       Perform calculations
       Generate data for forms, reports, and other queries
       Update data in the tables in a database
       Find and display data from two or more tables
• A Query Wizard prompts you for information by asking
  a series of questions and then creates the appropriate
  query based on your answers


New Perspectives on Microsoft Office Access 2007                   9
Query Design                                       XP


• When you use query by example (QBE), you give
  Access an example of the information you are
  requesting
• Click the Create tab on the Ribbon
• In the Other group on the Create tab, click the
  Query Design button




New Perspectives on Microsoft Office Access 2007        10
Query Design                                       XP


• The Show Table dialog box lists all the tables in
  the Belmont database.
• Click Customer in the Tables list box, click the
  Add button, and then click the Close button.
• Access places the Customer table’s field list in
  the Query window and closes the Show Tables
  dialog box, as shown on the next slide (Figure 3-
  6).


New Perspectives on Microsoft Office Access 2007        11
Query Design                                       XP




New Perspectives on Microsoft Office Access 2007        12
Creating and Running a Query                       XP




New Perspectives on Microsoft Office Access 2007        13
Creating and Running a Query                                        XP

• Position the mouse pointer on the bottom border of the
  Customer field list until the pointer changes to a different shape.
  Drag the pointer down until you can see all fields.
• Double click or drag the following fields to the columns in the
  query:
    Customer ID
    Company
    First Name
    Last Name
    E-mail Address
• Click the Run button and Access displays the results in Datasheet
  view.
New Perspectives on Microsoft Office Access 2007                         14
Creating and Running a Query                       XP


• Click the Save button on the Quick Access
  toolbar. The Save as dialog box opens.
• Type Customer E-mail in the Query Name text
  box and then press the Enter key.
• Access saves the query and displays the name on
  the tab for the query.




New Perspectives on Microsoft Office Access 2007        15
Updating Data Using a Query                              XP

• You can update the data in a table using a query
  datasheet.
• To update data using the Customer E-mail query
  datasheet:
   Locate the record with Customer ID 11040.
   In the First Name field, change Charles to Susan
   In the Last Name field, change the name to Darcy
   In the E-mail Address field, change the address to
     sdarcy33@rvdc3.com.


New Perspectives on Microsoft Office Access 2007              16
Updating Data Using a Query                        XP


• After updating the data with the query, close the
  query.
• Open the Customer table in Datasheet view and
  close the Navigation pane.
• View the record with Customer ID 11040. Notice
  that the changes that were made in the query
  datasheet were made to the record in the
  Customer table.
• Close the Customer table.

New Perspectives on Microsoft Office Access 2007        17
Creating a Multitable Query                        XP


• A multitable query is a query based on more
  than one table
• If you want to create a query that retrieves data
  from multiple tables, the tables must have a
  common field




New Perspectives on Microsoft Office Access 2007        18
To Create a Query using the Customer and                 XP
Contract Tables
• Click Customer in the Show Tables list box, click the Add
  button, click Contract, click the Add button, and then
  click the Close button.
• Resize the Customer field list so that all fields are
  displayed.
• In the Customer field list, place the following fields in
  columns 1 to 4:
       City
       Company
       First Name
       Last Name
New Perspectives on Microsoft Office Access 2007              19
To Create a Query using the Customer and                   XP
Contract Tables
• From the Contract field list, place the following fields in
  columns 5 and 6 of the query:
   Signing Date
   Contract Amt
• Click the Run button to run the query and display the
  results in Datasheet view.
• The records are displayed in order according to the
  values in the Customer ID field, because it is the primary
  key field in the primary table, even though this field is
  not included in the query datasheet.

New Perspectives on Microsoft Office Access 2007                20
To Create a Query using the Customer and                   XP
Contract Tables
• Click the Save button on the Quick Access toolbar. The
  Save As dialog box appears.
• Type Customer Contracts in the Query Name text box.
• Access saves the query and displays its name on the
  query tab.




New Perspectives on Microsoft Office Access 2007                21
Sorting Data in a Query                            XP


• Sorting is the process of rearranging records in a
  specified order or sequence
• To sort records, you must select the sort field,
  which is the field used to determine the order of
  records in the datasheet




New Perspectives on Microsoft Office Access 2007        22
Using AutoFilter to Sort Data                       XP


• The AutoFilter feature enables you to quickly
  sort and display field values in various ways
• Clicking the arrow in a column heading displays
  the AutoFilter menu




New Perspectives on Microsoft Office Access 2007         23
Sorting a Query Datasheet                                           XP

• In the query datasheet, click the arrow on the column heading for
   the field you want to sort
• In the menu that opens, click Sort A to Z for an ascending sort, or
   click Sort Z to A for a descending sort
or
• In the query datasheet, select the column or adjacent columns on
   which you want to sort
• In the Sort & Filter group on the Home tab, click the Ascending
   button or the Descending button
or
• In Design view, position the fields serving as sort fields from left
   to right
• Click the right side of the Sort text box for the field you want to
   sort, and then click Ascending or Descending for the sort order

New Perspectives on Microsoft Office Access 2007                         24
Sorting Multiple Fields in Design View                   XP

• Access lets you select up to 10 different sort fields.
• In the Views group on the Home tab’s Ribbon, click the
  View button to open the query in Design view.
• Click the right side of the City Sort text box and click
  Ascending.
• Click the right side of the Contract Amt Sort text box
  and click on descending.
• Click the Run button to display the Query’s Datasheet
  view.
• Click the Save button on the Quick Access toolbar to
  save the revised Customer Contracts query.
New Perspectives on Microsoft Office Access 2007              25
Sorting a Query Datasheet                          XP




New Perspectives on Microsoft Office Access 2007        26
Using Filter By Selection                                       XP

• A filter is a set of restrictions you place on the records in
  an open datasheet or form to temporarily isolate a
  subset of the records
• In the datasheet or form, select part of the field value
  that will be the basis for the filter; or, if the filter will be
  based on the entire field value, click anywhere within
  the field value. For example, click anywhere on the
  Grand Rapids field in the City field.
• In the Sort & Filter group on the Home tab, click the
  Selection button, and then click the type of filter you
  want to apply. For example, click on Equals “Grand
  Rapids.”

New Perspectives on Microsoft Office Access 2007                     27
Using Filter By Selection                          XP


• Access displays the filtered records.
• Only the 13 records that have a City field value of
  “Grand Rapids” appear in the Datasheet, as
  shown in the next slide.




New Perspectives on Microsoft Office Access 2007        28
Using Filter By Selection                          XP




New Perspectives on Microsoft Office Access 2007        29
Add a Second Filter to the Query                           XP

• Click anywhere on a 3 (for March) in the Signing Date
  field.
• In the Sort & Filter group on the Home tab, click the
  Selection button, and then click the type of filter you
  want to apply. For example, click on Begins with 3.
• Access now displays 4 records that have Grand Rapids in
  the City field and begins with 3 in the Signing Date field.
• Click on the Toggle Filter button. Access redisplays all
  records in the query Datasheet.
• Close the query and click on No to close it without
  saving it.

New Perspectives on Microsoft Office Access 2007                30
Defining Record Selection Criteria                 XP
for Queries
• Just as you can display selected fields from a
  database in a query datasheet, you can display
  selected records
• To tell Access which records you want to select,
  you must specify a condition as part of the query
• A comparison operator asks Access to compare
  the value in a database field to the condition
  value and to select all the records for which the
  relationship is true

New Perspectives on Microsoft Office Access 2007        31
Defining Record Selection Criteria                 XP
for Queries




New Perspectives on Microsoft Office Access 2007        32
Specifying an Exact Match                          XP


• With an exact match, the value in the specified
  field must match the condition exactly in order
  for the record to be included in the query results




New Perspectives on Microsoft Office Access 2007        33
Defining Record Selection Criteria                        XP
for Queries
• For example, create a query using the Customer and
  Contact tables.
• Place the following fields from the Customer table in
  columns 1 to 7 in the design grid:
       Company
       First Name
       Last Name
       Phone
       Address
       City
       Email Address

New Perspectives on Microsoft Office Access 2007               34
Defining Record Selection Criteria                       XP
for Queries
• Next, place the following fields from the Contract table
  in columns 8 to 11 of the design grid:
       Contract Num
       Contract Amt
       Signing Date
       Contract Type
• For example, click the City Criterion text box, enter the
  criterion Holland and press the Enter key. The condition
  changes to “Holland”.
• Click the Run button to run the query. A total of 12
  records are displayed in the datasheet.
New Perspectives on Microsoft Office Access 2007              35
Defining Record Selection Criteria                       XP
for Queries
• Click the Save button on the Quick Access toolbar to
  open the Save As dialog box.
• Type Holland Customers in the Query Name text box,
  and then click on OK.
• Access saves the query and displays its name on the
  query tab.




New Perspectives on Microsoft Office Access 2007              36
Modifying the Holland Customers Query                      XP

• Enter Design view and remove the display of the City
  field values:
   You need to keep the City field as part of the query
     design in order to display on Holland customers.
   However, remove its display from the datasheet by
     clicking the City Show check box to remove the
     check.
• Move the Contract Amt, Signing Date, and Contract
  Type fields to columns 2 through 4.


New Perspectives on Microsoft Office Access 2007                37
Modifying the Holland Customers Query              XP


• In the Results group of the Query Tools Design
  tab, click Run.
• Access displays the results of the modified query.




New Perspectives on Microsoft Office Access 2007        38
Changing a Datasheet’s Appearance                  XP


• You can change the characteristics of a
  datasheet, including the font type and size of
  text in the datasheet, to improve its appearance
  or readability
• By default, the rows in a datasheet are displayed
  with alternating background colors of white and
  light gray



New Perspectives on Microsoft Office Access 2007        39
Changing the Font Size and Resizing Columns
                                          XP

• Changing the font size:
   Select any cell in the datasheet
   Click on the Font Size arrow in the Font group of the
    Home tab and click on 9. All cells change to a width
    of 9 in the query’s datasheet
• Changing the column widths:
   Click on the database selector, which is immediately
    to the left of the Contract Num field name.
   Position the pointer at the right of any column and
    double-click. All column widths change to best fit.

New Perspectives on Microsoft Office Access 2007            40
Changing the Background Color of                         XP
Datasheet Rows
• In the Font group of the Home tab’s Ribbon, click the
  arrow of the Alternate Fill/Back Color button to display
  a gallery of color choices, as shown in the next slide.
• In the Access Theme Colors gallery, click on the second
  color box of the second row.
• Save and close the Holland Customers query.




New Perspectives on Microsoft Office Access 2007              41
Changing a Datasheet’s Appearance                  XP




New Perspectives on Microsoft Office Access 2007        42
Create a New Query by Copying and                           XP
Changing the Name of an Existing Query
• Right-click on the Holland Customers query in the
  Customer group.
• Click on the Copy command in the shortcut menu.
• Right-click an empty area of the Navigation Pane and
  select the Paste command in the shortcut menu.
• Type Large Contract Amounts in the Query Name text
  box and then either click on OK or press the Enter key.
• The new query appears in both the Contract and
  Customer groups of the Navigation Pane.


New Perspectives on Microsoft Office Access 2007                 43
Modifying the Design of the New Query                               XP

• In the Views group of the Home tab, click on the arrow at the
  bottom of the View button and select the Design View command
  from the drop-down list.
• Click the Contract Amt Criteria text box, enter >=25000, and then
  press the Tab key. This is shown on the next slide.
• Click the City Criteria text box and remove that criterion.
• Click the City Show text box so that the field is redisplayed in the
  query results
• Drag the City field so that it is immediately to the left of the
  Address field
• Click the Run button. Access displays a total of 23 records with a
  Contract Amt that is greater than or equal of $25,000.
• Save the Large Contracts Amounts query and then close it.
New Perspectives on Microsoft Office Access 2007                         44
Using a Comparison Operator to Match               XP
a Range of Values




New Perspectives on Microsoft Office Access 2007        45
Defining Multiple Selection Criteria                               XP
for Queries
• Multiple conditions require you to use logical
  operators to combine two or more conditions
       Use the And logical operator when you want a record
        selected only if two or more conditions are met
       Use the Or logical operator when you place conditions in
        different Criteria rows




New Perspectives on Microsoft Office Access 2007                        46
Defining Multiple Selection Criteria               XP
for Queries




New Perspectives on Microsoft Office Access 2007        47
Creating a Query with the And Logical                        XP
Operator
• Click the Create tab on the Ribbon and then click the Query
  Design button in the Other Group of the Create tab.
• Add the Customer and Contract tables to the Query window and
  then close the Show Table dialog box.
• Add the following fields to columns 1 to 5 from the Customer
  field list:
    Company
    First Name
    Last Name
    Phone
    City


New Perspectives on Microsoft Office Access 2007                  48
Creating a Query with the And Logical                  XP
Operator
• Add the Contract Amt and Signing Date fields from the
  Contract table to columns 6 and 7.
• In the City Criteria text box, enter Lansing and press
  the Tab key.
• In the Contract Amt Criteria text box, enter >25000
  and press the Tab key.
• Click on the Run button. Access displays three records
  for two different customers.
• Click the Save button on the Quick Access toolbar and
  enter the name Key Lansing Customers in the Save As
  dialog box and then close the query.
New Perspectives on Microsoft Office Access 2007            49
Creating a Query with the Or Logical                         XP
Operator
• Click the Create tab on the Ribbon and then click the Query
  Design button in the Other Group of the Create tab.
• Add the Customer and Contract tables to the Query window and
  then close the Show Table dialog box.
• Add the following fields to columns 1 to 4 from the Customer
  field list:
    First Name
    Last Name
    Company
    City



New Perspectives on Microsoft Office Access 2007                  50
Creating a Query with the Or Logical                         XP
Operator
• Add the Contract Amt, Signing Date, and Contract
  Type fields from the Contract table to columns 5 to 7.
• Click the Contract Amt Criteria text box, enter
  < 10000, and the press the Tab key.
• Go down one row, type Between 1/1/2011 and
  3/1/2011 in the “or” Signing Date Criteria text box,
  and then press the Tab key.
• Double-click the right side of the Signing Date field to
  widen it so that the entire criterion can be seen.


New Perspectives on Microsoft Office Access 2007                  51
Creating a Query with the Or Logical                      XP
Operator
• Run the query. Access displays only those records that
  meet at least one of the two criteria and displays a total
  of 29 records. The records appear in descending order
  based on the dates in the Signing Date field.
• Save the query as Small Contracts Or Winter Signings
  and then close it.




New Perspectives on Microsoft Office Access 2007               52
Creating a Calculated Field                                         XP

• In addition to using queries to retrieve, sort, and filter data
  in a database, you can use a query to perform calculations
   Expression Builder is an Access tool that makes it easy
      for you to create an expression
• Open the query in Design view
• In the design grid, position the insertion point in the Field
  text box of the field for which you want to create an
  expression
• In the Query Setup group on the Query Tools Design tab,
  click the Builder button
• Use the expression elements and common operators to
  build the expression, or type the expression directly
• Click the OK button
New Perspectives on Microsoft Office Access 2007                         53
Creating a New Query That Will Inclue a                  XP
Calculated Field
• Click the Create tab on the Ribbon and then click the
  Query Design button in the Other Group.
• Add the Contract and Invoice tables to the Query
  Window and then close the Show Tables dialog box.
• Add the Contract Num and Contract Amt fields from
  the Contract field list to columns 1 and 2.
• Add Invoice Item, Invoice Paid, and Invoice Amt fields
  from the Invoice table to columns 3 to 5.
• In the Invoice Paid Criteria text box, enter No, and then
  press the Tab key.

New Perspectives on Microsoft Office Access 2007              54
Creating a Calculated Field                        XP


• Click the Invoice Paid Show check box and
  remove the check.
• Save the query with the name Unpaid Invoices
  With Late Fees.




New Perspectives on Microsoft Office Access 2007        55
Creating a Calculated Field                                 XP

• Click the blank Field text box to the right of the Invoice
  Amt field. The calculated field will be placed in this box.
• In the Query Setup group on the Query Tools Design
  tab, click the Building button. Access opens the
  Expression Builder dialog box.
• Enter the Invoice Amt field in the expression either by
  double-clicking or clicking once and then clicking the
  Paste button.
• Click the * button in the row of common operators and
  then enter .03.

New Perspectives on Microsoft Office Access 2007                 56
Creating a Calculated Field                             XP

• You have now completed the expression. It should read
  Invoice Amt * .03, as shown on the next slide.
• Click the OK button to close the Expression Builder
  dialog box and add the expression in the design grid of
  the Field text box.
• Press the Home key to position the insertion point to
  the left of the expression.
• Enter “Late Fee”:
• Run the query. Access displays the query datasheet,
  which includes the calculated field with the name “Late
  Fee.”
New Perspectives on Microsoft Office Access 2007             57
Creating a Calculated Field                        XP




New Perspectives on Microsoft Office Access 2007        58
Formatting a Calculated Field                                       XP

• Switch to Design view.
• Right-click the Late Fee calculated field in the design grid to open
  the shortcut menu and then click on Properties in the menu.
• In the Property Sheet for the calculated field, as shown on the
  next slide:
    Click the right side of the Format text box to display a list of
      formats and then click on Currency.
    Click the right side of the Decimal Places text box and then
      click on 2.
• Close the Property Sheet and run the query. The amounts in the
  calculated field are displayed with dollar signs and two decimal
  places.
• Save the query and then close it.
New Perspectives on Microsoft Office Access 2007                         59
Formatting a Calculated Field                           XP


• You can specify a particular format for a
  calculated field, just as you can for any field, by
  modifying its properties




New Perspectives on Microsoft Office Access 2007             60
Using Aggregate Functions                          XP


• Aggregate functions perform arithmetic
  operations on selected records in a database
• If you want to quickly perform a calculation using
  an aggregate function in a table or query
  datasheet, you can use the Totals button on the
  Home tab




New Perspectives on Microsoft Office Access 2007        61
Working with Aggregate Functions Using                      XP
the Totals Row
• Open the Contract table in Datasheet view and then
  close the Navigation pane.
• In the Records group of the Home tab, click the Totals
  button. Access adds a row with the label “Total” to the
  end of the datasheet.
• Scroll to the end of the datasheet.
• Click the Contract Amt field in the Total row. An arrow
  appears on the left side of the field.




New Perspectives on Microsoft Office Access 2007                 62
Working with Aggregate Functions Using                   XP
the Totals Row
• Click Sum in the menu, as shown in the next slide.
  Access adds all the values in the Contract Amt field and
  displays a total of $4,690,050.00.
• To remove the totals, click the Total button again.
• Close the Contract table without saving it.




New Perspectives on Microsoft Office Access 2007              63
Using Aggregate Functions                          XP




New Perspectives on Microsoft Office Access 2007        64
Creating Queries
                                                             XP
with Aggregate Functions
• Create a new query in Design view, add the Contract table
  to the Query window, and then close the Show Table dialog
  box.
• Add the field Contract Amt three times to the design grid.
• Click the Totals button in the Show/Hide group on the Query
  Tools Design tab. Access places a Total row between the
  Table and Sort rows.
• Click the right side of the first column’s Total text box and
  then click Min.
• Click to the left of Contract Amt in the first column’s Field
  text box and enter Minimum Contract Amt:

New Perspectives on Microsoft Office Access 2007                  65
Creating Queries with Aggregate Functions XP
• Click the right side of the second column’s Total text box
  and then click Avg.
• Click to the left of Contract Amt in the second column’s Field
  text box and enter Average Contract Amt:
• Click the right side of the third column’s Total text box and
  then click Max.
• Click to the left of Contract Amt in the third column’s Field
  text box and enter Maximum Contract Amt:
• Run the query. Access displays one record, as shown in the
  next slide, based on its calculations of all 64 records.
• Resize all columns to their best fit.
• Save the query as Contract Amt Specifications.
New Perspectives on Microsoft Office Access 2007                   66
Creating Queries with Aggregate Functions XP
• Aggregate functions operate on the records that
  meet a query’s selection criteria




New Perspectives on Microsoft Office Access 2007    67
Using Record Group Calculations                           XP

• Display the Contract Amt Statistics query in Design
  view.
• Click the Office Button, Point to Save As, and then click
  Save Object As.
• Enter Contract Amt Statistics By City to replace the
  highlighted name. Access saves the query with the new
  name.
• In the Query Setup group on the Query Tools Design
  tab, click the Show Table button to display the Show
  Table dialog box.

New Perspectives on Microsoft Office Access 2007               68
Using Record Group Calculations                          XP

• Add the Customer table to the Query window, close the
  Show Table dialog box, and resize the Customer field
  list.
• Drag the City field from the Customer field list to the
  first column in the design grid. The existing fields will
  shift to the right. Group By, the default option, appears
  for the City field.
• Run the query. Access displays 12 records – one for
  each City group.
• Save the query and then close it.
• Open the Navigation pane.
New Perspectives on Microsoft Office Access 2007              69
Using Record Group Calculations                    XP


• The Group By operator divides the selected
  records into groups based on the values in the
  specified field




New Perspectives on Microsoft Office Access 2007        70
Working with the Navigation Pane                                   XP

• At the top of the Navigation Pane, click the All Tables bar. A menu
  is displayed, as shown in the next slide.
• In the top section of the menu, click Object Type. The Navigation
  Pane is now grouped into categories of object types – tables,
  queries, forms, and reports.
• Click the All Objects bar to display the Navigation Pane menu, and
  then click Queries. The Navigation Pane now shows only the
  query objects in the database.
• Click the Queries bar, and then click Tables and Related Views to
  return to the default display of the Navigation Pane.
• Compact and repair the Belmont database, and then close
  Access.

New Perspectives on Microsoft Office Access 2007                        71
Working with the Navigation Pane                   XP


• The Navigation Pane divides database objects
  into categories, and each category contains
  groups
      Tables and Related Views
      All Tables




New Perspectives on Microsoft Office Access 2007        72

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:33
posted:12/9/2011
language:English
pages:72