Back to RacerNet

Access 2000 Introduction

Create a Database

  • Click on the Microsoft Access icon to open Access.
  • The Office Assistant may appear. If so, click on the Start using Microsoft Office option.
  • A dialog box is displayed giving you the choice of creating a new database or opening an existing database
  • Click on the Blank Database option
  • Click the OK button
  • You must save the database before you can begin working with it
  • Choose the folder where the database file will reside from the drop down box next to Save in:
  • Type in a name for your database next to File name: and click the Create button
  • Notice the name of the database file ends with .mdb

Database Window
Used to manage database objects; each object is on a different tab.

Access Objects

  • Table - stores data; all queries, forms, and reports get their data from tables
  • Query – used to analyze data, view data using criteria you specify, modify and delete data
  • Form - used to easily enter or view information
  • Report - used to print selected parts of information
  • Macro - set of actions to automate common tasks
  • Module - Visual Basic Applications Edition code to automate tasks

Create a Table

  1. Click on the tab labeled Tables in the database window
  2. Click the New button
    Notice the Import Table option - this is to import data into Access from another data source, such as dBASE, another Access database, text files, etc.
  3. Select Design view to create a customized table, and click the OK button
    A new table is displayed in Design view

Working with the Table in Design View
Field – stores a category of information, such as Name or State

Create table fields

  1. Enter fields at the top of the Design window
  2. Enter Field Name (spaces are allowed in field names, but not recommended if you plan to transport your Access data to another database management system)
  3. Tab to the Data Type column
  4. Click on the drop down arrow in the Data Type column and choose the data type for the field – notice the new hyperlink data type
    Note: The way abbreviated dates are interpreted by Access has been changed for the Year 2000:
    1/1/00 through 12/31/29 ? 1/1/2000 through 12/31/2029
    1/1/30 through 12/31/99 ? 1/1/1930 through 12/31/1999
  5. To get help on available data types, press the F1 button while the cursor is in the Data Type column
  6. Tab to the Description column
  7. Enter a description the field - this will be displayed in the status bar at the bottom of the Access window whenever the cursor is in that field
  8. Tab again and the cursor will be in the next row in the Field Name column
  9. Create a field for each category of data you want in this table by repeating the above process
  10. Use the F6 key (or your mouse) to switch to the bottom part of the Design window when you are done entering fields (be sure your cursor is somewhere in a field row before switching). The field properties at the bottom of the Design window correspond to the active field (marked by a triangular indicator) at the top.

Enter field properties
Field property – controls storage, handling and display of data

  1. Enter properties for each field at the bottom of the Design window
    Notice that clicking in a property box displays a description of that property at the right
    Field Size specifies the size of the data for text and number data types (Be careful if you are changing a field’s size - changing to smaller size may cause you to lose data!)
  2. Enter appropriate field sizes for FirstName, MiddleInitial, LastName, Phone, and SSN
  3. With your cursor in the entry area next to the Input Mask, click on the Build button to have the Input Mask Wizard help you create an input mask for Phone
    The Input Mask will determine how the data is displayed when entered
    If the Input Mask Wizard isn’t installed, with your cursor in the Input Mask property, press F1 to display the help page for Input Masks; click on Example at the top of the help page. This will display some common input masks for phone numbers and zip codes that you can copy and paste into the input mask field property
  4. Enter “Phone number” for the Phone field’s Caption property - this text will be used as the field label in place of the field name on a form or report
  5. For the Number of Children field, specify a Default value of 0; this will be the value of the field unless it is changed
  6. Select the Format you wish to use for Birth Date by clicking on the drop down box in the Format property entry area

Create a Primary Key
Primary key – field or group of fields that make each record unique;
makes record retrieval faster, prevents duplication of data, records are sorted by primary key

  1. Put your cursor in the row of the field you wish to make the primary key. (Social Security)
  2. Click on the primary key button on the toolbar to make the selected field the primary key

Close the table

  1. Select Close from the File menu
    The Office Assistant will ask if you want to save the table
  2. Click the Yes button to save the table
  3. Enter a table name and click OK (Spaces are allowed in table names, but not recommended if you plan to transport your Access data to another database management system)

Working with a Table in Datasheet View

  1. Select the table in the database window
  2. Click the Open button
    The table is displayed in Datasheet view - the active record has a triangular pointer next to it; you may modify or delete this record, or add new record data
    Navigate through records using buttons on the status bar at the bottom of the Datasheet window
  3. Begin entering data for the first record, tabbing between fields - records are saved when you move to another record and when you close the Datasheet
  4. Add another record by clicking on the naviagtion button to move your cursor to a field in the empty record ( this will be marked by a * after at least one record is entered in the table Modify a field’s data by clicking in the field you want to modify and typing in the new data
    To delete a record, make it the active record, and then click the Delete Record button (Access warns you before deleting the record)
  5. Select Close from the File menu

Modify a table in Design View

  1. Select the table you wish to modify from the Tables tab
  2. Click the Design button
  3. Click in the Field Name, Data Type, or Description entry areas and choose one of the available choices, or type something in the entry area if there are no choices
  4. Insert a new field by clicking on the Insert Rows button
  5. Delete a field by clicking on the Delete Rows button - the active field will be deleted (Deleting a field will result in any data in that field being lost)
  6. Select Close from the File menu when you are done making changes
    The Office Assistant will prompt you to save your changes

Create a Selection Query
Create a Query using the Simple Query Wizard

  1. Click on the tab labeled Queries in the database window
  2. Click on the New button
    To have some help creating a query select Simple Query Wizard, and then click OK
  3. Select the table or query you want to include fields from
  4. Use the arrow buttons to add or remove fields from the query (fields that will be included in the query are displayed in the Selected Fields box)
  5. Click the Next button to continue
    Leave Detail selected to have the query display individual records
  6. Click the Next button
  7. Type in a title for your query – this will be displayed on the Query tab
  8. Leave the Open the query to view information option selected and click the Finish button
    The query results will be displayed in Datasheet view
  9. Double-click on the left corner of the query window to close the query

Create a Customized Query in Design View
Click on the New button on the Queries tab

  1. Select Design view and click OK
  2. Select each table or query you wish to include in your query, and click the Add button after each selection
  3. Click the Close button when you are done
  4. Double-click on table fields to include them in the query; the field will display in the bottom window
    The Table: row indicates the table the field is from
    The Sort: row allows you to sort a field’s data in the query by choosing Ascending or Descending
    The Show: row specifies whether you want the field actually displayed when the query is run
    The Criteria: row specifies criteria for that field’s data; it can be a combination of constants, expressions, operators ( <, >, OR, etc. ), functions, and fields
    Enter >0 for the Number of Children criteria – this means you want to select all the records where the Children field is greater than 0
    The or: row specifies alternate criteria for the field’s data; for example, you can specify you want to select records where LastName is “Duck” or “Smith” by entering “Duck” in the Criteria: row for the LastName field, and “Smith” in the or: row
  5. Test the query by clicking on the Run button on the toolbar
  6. Return to Design view, by clicking on the View button ; notice the Design view icon changes to the Datasheet view icon
    Close the design view window by selecting Close from the File menu
  7. Click Yes to save the query changes when prompted by the Office Assistant
  8. Type in a query name and click OK
    Notice the new query name is displayed on the Queries tab

Run a Query

  1. Select the query in the database window under the Queries tab
  2. Click the Open button; the results will be displayed in Datasheet view
  3. Close the query by clicking on the in the right corner of the query window

Create a Form Using Wizard
Forms provide a nice format to enter data easily

  1. Click on the tab labeled Forms in the database window
  2. Click the New button
  3. Select Form Wizard and click OK.
  4. Use the arrows and drop down menu to select the fields you want contained in your form.
  5. Click next and select a layout for your form.
  6. Click next and select a style for your form.
  7. Click next and give the form a name.
  8. Click Finish

View the form in Form View and Enter Data in the Form

  1. Select the name of the form you want to display from the Forms tab
  2. Click the Open button
  3. Move to an empty record in the form by clicking on the at the bottom of the form
  4. Enter new data, tabbing between fields
  5. Tabbing from the last field takes you to the next empty record for easy data entry
  6. Close the form

Modify a Form in Design View
Design view displays form elements as labels and controls
label – field name or the field’s Caption field property, identifying data on form
control - displays the data from the field

  1. Select the form in the database window
  2. Click on the Design button
  3. Select a label or control
  4. Move a label and field control together when the hand cursor is displayed
  5. Move a label or field control by itself when the pointing hand cursor is displayed
  6. Resize a label or field control when the double arrow cursor is displayed – you will want to enlarge some controls where the data is not displayed completely in the size provided by AutoForm
  7. Select multiple labels and controls by holding down the shift key while clicking on each label or control
    If you want to view your changes before closing the form, click on the View button ; click on the View button again to get back to Design view
  8. When you are done making changes, select Close from the File menu
  9. Click Yes if you want to save your changes

Create a Tabular Report

  1. Click on the tab labeled Reports in the database window
  2. Click on the New button and select Report Wizard. Click OK
  3. Use the arrows and drop down menu to select the fields you want display in your report.
  4. Click next and make any sorting selections.
  5. Click next and choose a layout and orientation. Click next.
  6. Select a style for your report.
  7. Click next and give the report a name. Click finish to save your report.

View a Report in Preview

  1. Select the report you wish to preview
  2. Click on the Preview button
  3. Notice you can display 1 page, 2 pages, or up to 6 pages at a time in the Preview using the One Page , Two Pages , and Multiple Pages buttons, respectively
    Also, notice you can easily switch back to Design view using the View button
  4. Click the Close button to close Preview view or the Print button to print the report

Modify a Report

  1. Select the report you wish to modify from the Reports tab
  2. Click the Design button
    Making modifications to a report in Design view is analogous to modifying a form in Design view
  3. When you are done, close Design view and save your changes

Print a Report

  1. Select the report you wish to print on the Reports tab
  2. Click on the Print button on the toolbar or, to select a range of pages to print, select Print… from the File menu
  3. To change printing options, such as margin width and page layout, select Page Setup… from the File menu, make select options, and click OK
  4. Check Print Preview after making changes to be sure report data still fits on page(s) the way you want it to, and is otherwise displayed correctly

Getting Help

  1. Select Microsoft Access Help from the Help menu or click on the Office Assistant button on the toolbar to display the Office Assistant
  2. Select Contents and Index from the Help menu
  3. Click on the Contents tab and double-click on a topic to expand the topic
  4. Click on the Index tab to find topics associated with a key word
  5. Click on the Find tab to do a more in-depth search for help
  6. Select What’s This? from the Help menu to display a short help message or description when you point and click the mouse on anything in Access

*This tutorial was created by Heath Keller, Center for Teaching, Learning & Technology