An Introduction to MS Access 2007 by rjj75795


									An Introduction to
MS Access 2007
Olathe District Schools
Instructional Technology

Last Updated 8/1/2009
 Microsoft Access 2007 is a relational database application that serves
 as a container that can hold objects such as tables, records, forms,
 queries and reports. This tutorial will serve as an introduction to the
 basics of creating each of these database objects.

  In this tutorial, you'll learn how to:

        Create a new database

        Create tables

        Create records

        Create forms

        Create queries

        Create reports

Create a new database
    1. Open Microsoft Access.
       The resulting window should look similar to the window shown below.

    2. Click the “Blank Database” icon.

    3. Click the folder icon as shown below.

    4. Create a new folder on the Desktop or your H: Drive named: Access 2007 Training as
       shown below.

    5. In the File Name Box enter the following as the file name: Colleagues.accdb
       Click OK.

    6. Click the Create button. The window for the Colleagues database file will open.

    7. Before you enter entries into the database it is important that you understand the
       elements of a database file. They are illustrated below.

         A database stores information in an organized way, and makes it easy to access
         information in an organized manner. The components of a database are described and
         illustrated below.

         Tables – store data within the database
         Forms – makes it easy to put data into tables
         Queries – pulls out/retrieves specific data
         Reports – puts data in an easy-to-read format

    8. Click the Create Tab. Notice the 4 Groups in the Create Ribbon: Tables, Forms,
       Reports and Other.

Create tables
       1. Click the Datasheet Tab. In the Datasheet Ribbon, click the View command in the
          Views Group. Choose Design View.

       2. When the Save As dialog box appears, enter: My Colleagues as the table name.
          Click OK.

       3. You will now enter several field names in the database. Click in the 2nd row in the Field
          Name column; enter the following field names as shown below, leaving ID in the 1st row.
           First Name, Last Name, City, Zip, Phone Number

Note: Notice the key icon beside the ID field. This means the ID field is the table's "key" field.
A table's key field cannot contain any duplicates. Every table should have a key field. For example, if a school
keeps a database, each student can have a unique ID number in the key field. That way, if it has more than
one student named Connie Smith, it can easily distinguish Connie Smith, ID #32116 from Connie Smith, ID
#29008 keeping their school records separate and unique.
Create records
   1. In the Ribbon, click View, then Datasheet View.

   2. When asked if you wish to save the database file first, click Yes.

   3. Enter the following records in the first and second rows of the database as shown below.
      Press TAB to move from column to column.

Note: Notice how the cursor in the row selector has moved down to the second (new) record. When you
move on to a new record, Access automatically saves the previous record.

  4. You will now create a new field for the field that was left off previously: State.
     Right-click the Zip column heading. When the menu appears, click Insert Column.

  5. Right-click the column heading. When the menu appears, click Rename Column. Type
     State. Press the Enter Key.

  6. Click inside the new State field for the first record. Enter CA. Enter FL for the second

  7. Click on the Phone Number column heading one time. Click again on the Phone
     Number column heading and drag the column to the left until the cursor rests between the
     Last Name and City columns.

  8. The Phone Number field is now moved before the City field.

Create forms
  1. Click the Save icon in the Quick Access Toolbar.

  2. Click the Create tab. In the Ribbon, click Form.

  3.   The new form for the My Colleagues table should look like the one shown below.

  4. In the form window, click the New Blank Record button, at the bottom of the window.

    5. If you see a Security Warning at the top of the window at any time, click the Options

    6. Choose Enable this content. Click OK.

10 | P a g e
    7. A new form should display. Enter a 3rd record in the database form, as shown below.

    8. Press the TAB key when finished entering the new record. The form should progress to a
        new, blank record. The 3rd record is now saved.

    9. Click the Save icon in the Quick Access Toolbar.

    10. In the Navigation Pane, double-click the Colleagues Table.

    11. The Colleagues Table appears.

11 | P a g e
Create queries
What's a query?
A query is a way to get specific information from the database.
Essentially, it's a question. You use queries to ask the database things like, "Which students are in
10th grade?", or "How many students live in Overland Park versus Olathe?"

    1. Click the Create tab. In the Other Group, click Query Wizard.

    2. When the New Query window appears, make sure Simple Query Wizard is selected.
       Click OK.

    3. When the Simple Query Wizard opens, double-click First Name in the Available Fields list.

12 | P a g e
    4. First Name now appears in the Selected Fields column, indicating you have chosen that field
       to be part of your query.

    5. Click the      button. This should add the Last Name field to the Selected Fields list.

    6. Double-click Phone Number to add this field to the Selected Fields list as well.

    7. Click Next.

    8. When the final screen appears for the Simple Query Wizard, enter Names and Phone
        Numbers for the query name. Click Finish.

13 | P a g e
    9. The query is automatically saved and executed. It should look like this:

14 | P a g e
Create reports
    1.   In the Create Ribbon, click Report Wizard in the Reports Group.

    2.   When the Report Wizard window appears, click the Tables/Queries drop-down arrow.
         Choose Table: My Colleagues from the list.

    3. Click the      button to move all the fields into the Selected Fields list.

15 | P a g e
    4. In the Selected Fields list box, click ID, then click the     button.
        The ID field should be removed.

    5. The report wizard should now look like the following, choosing to print all fields except for
        the ID field in the report. Click Next.

    6. When the next screen appears, click the                 button, choosing not to add any
        grouping options to the report.

16 | P a g e
    7. When the next screen appears, click the first drop-down arrow, and then click State in the
        list to choose to sort the report by the State field. Click Next.

    8. Now that you have selected the contents of the report and that the report will sort by the
        State field, you will select the Layout and Style for the report.

    9. Choose Tabular in the Layout section. Click Next.

17 | P a g e
    10. When the next screen appears, click Equity in the list of available styles. Click Next.

    11. When the final screen appears, type My Colleagues in the title box, if not already there.

    12. Click the Preview the report radio button. Click Finish.

18 | P a g e
    13. The report is automatically created, saved and opened. It should look similar to the

    14. Close Access 2007 and, if prompted, save any changes to the database.

19 | P a g e
Practice with database basics
       1. Open Microsoft Access.

       2. Create a new Access 2007 database in the folder you created previously.
           Name the file: Music Library.

       3. Create a new table with these fields:
           TIP: Do this in Design View.

           CD Title
           Date Purchased
           Number of Songs

       4. Save the table as CDs.

           Allow Access to create a Primary Key.

       5. Rename the ID field (the Primary Key) Album ID.

       6. Enter the following data into the table to create the first record:

           CD Title: Pearls on a String
           Artist: Dusty Trails
           Genre: Folk
           Date purchased: 05/10/2009
           Number of Songs: 14
           TIP: Do this in Datasheet View.

20 | P a g e
       7. Close the CDs table.

       8. Create a form for inputting data into the CDs table.

       9. Save it as CDs Table Data Input.

       10. Use the form to enter information for four more CDs of your own choosing.

       11. Create a query that pulls out this information:

           CD Title
           Number of Songs

       12. Save the query as Quick CD Notes.

       13. Run the query.

       14. Create a report based on the CDs table.

       15. Include all the fields of the CDs table in the report, and sort the records based on
           TIP: Format the report using the Flow style.

       16. Name the report CD Inventory.

       17. Close the report.

       18. Close Access 2007.

21 | P a g e

To top