Microsoft Access 97
Windows 98

Microsoft Access 97 Reference

 

Where to find this software

Microsoft Access 97 for Windows is standard software for Windows 98 faculty and staff computers.

Getting Started

Run Microsoft Access 97 by clicking on Start, then drag to Programs, Office 97, then the Microsoft Access application icon. When it asks if you would like to create a new database or to open an existing database, click Cancel.

How to Quit

  1. From the File menu choose Close when you are done with the database.
  2. From the File menu choose Exit when you are done with Microsoft Access.

This document is intended as a reference for those who have some experience with Microsoft Access. You may want to use the links below to move to the area where you need help. If you are new to Microsoft Access you will probably find it more useful to get started by completing the Microsoft Access Tutorial.

 

Basics

Queries

Tables

Reports

Forms

Basics

Creating a new database
  1. From the File menu, choose New Database...
  2. Select Blank Database under the General tab and click OK.
  3. Give the database a name and save to desired directory by clicking Create.

Opening an existing database

  1. From the File menu, choose Open Database...
  2. Find the location of your file in the Look in: field.
  3. Highlight the file you want to open and click Open.

Navigating in a database

  • To get to the Main Database window, go to the Window menu and select <database name>: database.
  • You can go to different parts of a database by going into the Main Database window and selecting the type of object it is (form, table, etc.), then the name of the object, and clicking Open.

 Printing

  1. To print a table, form, query, or report, go to the Main Database window and select the tab of the object (i.e. table, form, etc.)
  2. Highlight the name of the table, form, query, or report you want to print.
  3. From the File menu, choose Print...
  4. Check that it is set to the correct printer, the number of copies to be printed, etc., and click OK.

Tables

A table holds a collection of information stored on one topic (in the form of fields and records) and is the foundation of an Access database. All the information necessary for the queries, forms, and reports in the database will be based on the data in the table. You can sort and filter the information in many different ways. A table is where you can add, delete, or edit records, fields and their field properties.

Creating a table

  1. When you are in the Main Database window, choose Table from the Insert menu.
  2. Select a method to create the table and click OK. The two main methods of creating a table are Design View and Table Wizard.
    • When you create a table in Design View, you are given a blank list to be filled in with field names and the type of information it holds (i.e., text, integer, date, etc.).
    • Table Wizard helps you create a table by stepping through its process with you.

Different views

  • Datasheet view is used to view, add, delete and edit data in a table. It is similar to an Excel spreadsheet.
  • Design view is used to create and change the structure of a table, i.e., the length of a field, removal of a field, etc.

Adding a field

  1. Go to the View menu and select Design View.
  2. Put in the name for the field under Field Name in the first empty row.
  3. Select the data type from the drop-down list in the next column called Data Type.
  4. You can also add a description that will appear in the status bar when you select this field in a form under Description.

Changing a field's properties

  1. Go to the View menu and select Design View.
  2. You can change the size of the field, default value, whether or not it is required, and other properties of the field in the bottom half of the window (under Field Properties). If you click on any of the white boxes, a description of what it does appears in the gray area next to it.

Deleting a field

  1. Go to the View menu and select Design View.
  2. Locate the name of the field under Field Name and click on the gray area to its left when the cursor looks like this: so that the entire row is selected.
  3. Hit DELETE on the keyboard and select Yes when it asks if you are sure you want to delete the field.

Adding data to the table

  1. Go to the View menu and select Datasheet View.
  2. Enter the data into the table and go from field to field by hitting TAB, RETURN, or ENTER or by using the mouse and the navigator buttons provided at the bottom of the table window. You may also use the arrow keys to move around the table.

Forms

A form is an object in Access that allows you to edit and view information one record at a time. All the information you put in a form will then be inserted into the correct table. You may customize your form by having all or only some of the fields in a table displayed in the form. An example of a real-life form and table relationship would be an application form from a credit card company that you receive and return with all your information which they would put in a table with everyone else's data.

Creating a form

  1. When you are in the Main Database window, select Form from the Insert menu.
  2. Select a method for creating the new form. You have five main methods to create a new form.
    • Autoformat: Columnar will display the field names in one column and the fields in the column next to it, showing one record at a time. It will display all the fields from a table or query and will create the form in one step.
    • Autoformat: Tabular will display all the fields of a record on one line with all the records displayed at the same time. It will display all the fields from a table or query and will create the form in one step.
    • Autoformat: Datasheet will display all the data as they would appear in Datasheet View in a table. It will display all the fields from a table or query and will create the form in one step.
    • Form Wizard will give you a list of the tables and queries with their fields which you can add to your form. You can create a form with fields from multiple tables or queries. If you choose to do so, you will be able to choose how you would like all the information to be organized. Form Wizard will then step you through multiple steps regarding style and presentation and help you create the form.
    • Design View will give you a blank form to work with.
  3. Select where the data would come from for this form in the drop-down list and click OK.

Different views

  • Form view is used to view, add, delete and edit data in a form as you created it.
  • Datasheet view can also be used to view, add, delete and edit data in a form. It is different from form view only in the sense that it looks like an Excel spreadsheet.
  • Design view is used to create and change the structure of a form, i.e., the background of the form, the length of a field box, removal of a field, etc.

To change the background of the form:

  1. Go to the View menu and select Design View.
  2. Go into the Edit menu and choose Select Form.  
  3. Go into the Format menu and choose Autoformat...
  4. Select the new background and click OK.

To add a field into the form:

  1. Go to the View menu and select Design View.
  2. Go to the View menu again and choose Field List.
  3. Select the field that you want to add from the list of field names and drag it to where you want it on the form.

NOTE: If you did not select a table or query as the data source for this form in the New Form window, Field List will be grayed out.

To delete a field from the form:

  1. Go to the View menu and select Design View.
  2. Click on the field box (both field name and field box will be highlighted) and hit the DELETE key.

To change the size of the box that holds the field name or field:

  1. Go to the View menu and select Design View.
  2. Select the box and put your mouse cursor over the the little black boxes on the edges of the box until you see it turn into a black line with arrows on both ends .
  3. Drag the mouse until you have the box at the desired size.

To move the field name and field around the form:

  1. Go to the View menu and select Design View.
  2. Select either the field name or field box and put your mouse cursor over the edges of the box without little black boxes until you see it turn into a black hand with five fingers sticking up .
  3. Drag the cursor with the field name and field boxes to the desired location.

To move the field or field name around the form:

  1. Go to the View menu and select Design View.
  2. Select the box and put your mouse cursor at the top left edge of the box with the little black box until you see it turn into a black hand with one finger sticking up .
  3. Drag the cursor with the box to the desired location.

Queries

A query allows you to get information from multiple tables and put it together for viewing and analysis. These tables are related to each other in the sense that there is a common field between two tables. You can select fields or records from different tables; you can also sort, summarize, and perform calculations with this data. A query is a convenient way of gathering relevant data together and performing an analysis (i.e. A household inventory database can hold information about the various items in a household on a table. You can use a query to calculate the amount of money spent in each section of the house and compare it with the relative size of the section to the house).

Creating a query

  1. When you are in the Main Database window, select Query from the Insert menu.
  2. Select a method for creating the new query and click OK. There are two main methods of creating a query, Design View and Simple Query Wizard.
    • Design View will give you a list of table and queries to use as a data source and a blank query to be filled in with fields and filters.
    • Simple Query Wizard will step you through the process of creating a data source.

Different views

  • Design view is used to create and change the structure of a query, i.e., the addition or removal of a field, the order of a field, etc.
  • Datasheet view is used to view, add, delete and edit data in a query. It is similar to an Excel spreadsheet.

Adding a table or query as data source

  1. Go to the View menu and select Design View.
  2. Go to the Query menu and select Show Table... Select the desired table or query and click Add. When you have finished adding all the tables and queries that you need, click Close.

Adding a field

  1. Highlight the field you want to add to the query.
  2. Drag it to the bottom half of the application window into the first empty Field column so that the mouse cursor looks like a box with writing in it .

Sorting a query

  1. Go to the View menu and select Design View.
  2. Locate the name of the field you would like to sort and click on the white box for Sort: (second box under the field name).
  3. Click on the black triangle that appears in the box and select Ascending or Descending. If you change your mind, you can unsort the query by selecting (not sorted) here.
  4. Click on this icon at the top of the window to run the query with the new sort.

Filtering a query (by criteria)

  1. Go to the View menu and select Design View.
  2. Locate the name of the field you would like to filter and click on the white box for Criteria: (third box under the field name).
  3. Below are examples of what you can put in the Criteria: boxes and their results.

    "London"

    Displays those records with London in specific field.

    "London" Or "Hedge End"

    Displays those records with London or Hedge End in specific field.

    Not "USA"

    Displays those records without USA in specific field.

    Like "S*"

    Displays those records that starts with the letter S.

    >="N"

    Displays those records that starts with the letters N through Z.

    Between #1/5/95# And #1/10/95#

    Displays those records between 5-Jan-95 and 10-Jan-95 in specific field.

    #2/2/95#

    Displays those records with 2-Feb-95 in specific field.

    >0

    Displays those records that has a number greater than 0 in specific field.

    <=25

    Displays those records that has a number less than or equal to 25 in specific field.
  4. Click on this icon at the top of the window to run the query with the new filter.

Reports

When you are ready to display the information from the tables and queries, you may format them with reports. A report gives you total control of the presentation of the data. You can add colors, put in summaries and calculations, add in appropriate titles, only show certain fields, and perform many other formatting details.

Creating a report

  1. When you are in the Main Database window, select Report from the Insert menu.
  2. Select a method for creating the new report.
    • Autoformat: Columnar will display the field names in one column and the field names in the column next to it. It will display the next record after all the fields in the current record have been displayed. It will display all the fields from the data source. Autoformat: Columnar will create the report in one step.
    • Autoformat: Tabular will display all the fields of a record on one line, one record after another. All the records will be displayed at the same time. Autoformat: Tabular will create the report in one step.
    • Report Wizard gives you a list of the tables and queries with their fields which you can add to your report. You can create a report with fields from multiple tables or queries. If you choose to do so, you will be able to select how you would like all the information to be organized. Report Wizard will then step you through multiple steps regarding style and presentation and help you create the report.
    • Design View will give you a blank report divided into three sections, Page Header, Detail, and Page Footer. All your records and fields should only be in the Detail section.
  3. Select the data source for this report by choosing the desired table or query in the drop-down list and clicking OK.

Different views

  • Design view is used to create and change the structure of a report, i.e., the length of a field box, the addition or removal of a field, etc.
  • Print preview is used to view the data of a report as it will appear on every page.
  • Layout preview can also be used to view the data of a report. It is different than print preview only in the sense that it only uses a sample of the data as opposed to all of it.

To change the format of the report:

  1. Go on the View menu and select Design View.
  2. Go into the Edit menu and choose Select Report.  
  3. Go into the Format menu and choose Autoformat...
  4. Select the new format from the list and click OK.

To add a field into the report:

  1. Go on the View menu and select Design View.
  2. Go into the View menu and choose Field List.
  3. Select the field that you want to add from the list of field names and drag it to where you want in on the report.

To delete a field from the report:

  1. Go on the View menu and select Design View.
  2. Click on the field name (usually in bold) and the field next to it and hit the Delete key.

To change the size of the boxes that hold the field names or field:

  1. Go on the View menu and select Design View.
  2. Select the box and go to the little black boxes on the edges of the box until you see the mouse cursor turn into a black line with arrows on both ends .
  3. Drag the mouse until you have the box at the desired size.

To move the field name and field around the report:

  1. Go on the View menu and select Design View.
  2. Select either the field name or field box and go to the edges of the box without little black boxes until you see the mouse cursor turn into a black hand with five fingers sticking up .
  3. Drag the cursor and the field name and field boxes to the desired location.

To move the field or field name around the report:

  1. Go on the View menu and select Design View.
  2. Select the box and go to the top left edge of the box with the little black box until you see the mouse cursor turn into a black hand with one finger sticking up .
  3. Drag the cursor and the box to the desired location.