A. Introduction

B. Creating

• Tables are created in an existing database. Here is how to create a database:
  Invoking Access
  Start >> All Programs >> Microsoft Office >> Microsoft Access.

  Creating a New Database from Access
  1. Invoke Access.

  2. Select the Blank Database button under Getting Started with Microsoft Office Access and New Blank Database.

  3. At the middle right, under Blank Database, enter the database file name, for example, persons.accdb. Click the Create button.

  4. You can simply start entering data into a new table. If you enter numbers the column type will be Number; if you enter anything else, the column type will be Text. Alternatively, go to the next section to create a new table using the Design View.

  Creating a New Database using the Windows Explorer.
  1. Invoke Windows Explorer. Make sure that Windows Explorer is set to show file extensions. (See the very bottom item on the announcements page.

  2. Navigate to the folder that will contain the new Access database.

  3. Right click >> New >> Microsoft Access 2007 Database.

  4. Rename the new Access file from
     New Microsoft Office Application.mdb to test.mdb (or any other filename).

  Creating a Table using the Design View
  1. Select Create >> Table Design (4th item from left in toolbar).

  2. For each table column that you wish to define, enter the Field Name and the Data Type. (The most common datatypes are Text, Number, Currency, Date/Time and Yes/No.)

  3. Go to the DataSheet View (dropdown menu on the first item on the left in toolbar).
     1. Answer Yes to "Do you want to save the table now?"

     2. Enter the table name, for example, PersonData.

     3. Answer No to the question "Do you want to add a primary key now?"

  4. Enter data into the table row by row.

  5. To delete a row, move the cursor to the left of the row so that it becomes a black arrow. Right click and choose Delete Record.

  6. When you close the table or database, so save is necessary because the data is saved automatically.

C. Opening

• If a database with tables has already been created and saved, it must be opened for further modifications.
  Opening an Existing Database
  Invoke Access >> Click the Office Button in the upper left >> Open >> Select the name of the database to open.

  Opening an Existing Table
  Double click on the table name on the left.

  Modifying Rows in a Table
  Select the View dropdown menu >> Datasheet View. Add or modify rows as desired.

  Modifying the Columns in a Table
  Select the View dropdown menu >> Design View. Add or modify fields as desired.

D. Searching

• A small database can be searched visually without computer aid. However, searching a large database probably requires the aid of software tools.
  Searching for a Field Value
  1. Select the column containing the field value to find.

  2. Select Home >> Find (Binoculars)

  3. Enter what you want to find in the Find What box. Check that the Look In, Match, and Search boxes are set correctly.

E. Filtering

• Use filtering to extract some rows or columns from a database table. There are four types of filtering: by form, by selection, by excluding selection and by advanced filtering. This document will only discuss advanced filters
  By Advanced Filter
  1. Select Home >> Advanced (3rd column from right in toolbox) >> Advanced Filter/Sort.

  2. Enter the fields on which to filter (double click on the column name) and the filter criteria.

     Here are some filter criteria examples: <1, <2, ="M", <<>>"M", >=#1/1/2002# (Dates are delimited by #.)

     When multiple filter criteria are supplied, the filtered records must match all of the criteria. This is equivalent to a logical and operation.

  3. Right click above the filter criteria. Select Apply Filter/Sort.

  Removing a Filter
  1. Select Records >> Remove Filter/Sort.

F. Sorting

• The rows of a table can be sorted by one or more fields.

• If the rows are sorted by two fields, say Field1 and Field2, the rows are sorted first by Field1. Then all the rows having the same value of Field1 are sorted by Field2. Here is the StudentData table sorted by Year and Major.

  StudentData
  Name	Year	Major	FullTime	Email
  Carolyn	1	Accounting	Yes	carolynw@supermail.com
  Arthur	2	Mathematics	Yes	arthurj@supermail.com
  Bill	3	Biology	No	billk@supermail.com
  Julie	3	Sociology	Yes	julieh@supermail.com
  David	4	Art	Yes	davidr@supermail.com
  Julie	4	Sociology	No	nancya@supermail.com

  
   

Sorting on a Single Column
1. Select a column header.

2. Then click on the Sort Ascending button (AZ with a down arrow, 5th column to the right in the toolbar).

Sorting on Multiple Columns
1. Select Advanced (3rd column from the right in toolbox) >> Advanced Filter/Sort.

2. Enter the columns on which to sort with Sort Ascending or Sort Descending for each column.

3. Right click >> Apply Filter/Sort.

Removing a Sort
1. Select Advanced (3rd column from the right in toolbox) >> Advanced Filter/Sort.

2. Right click >> Clear Grid.

3. Right click >> Apply Filter/Sort.

G. Primary Keys

• A primary key is a field of combination of fields that uniquely identifies a row in a table.

• Primary keys are unique, which means that no two primary keys can be identical.

• Sometimes, a primary key consists of a combination of two or more fields.
  Setting a Key as the Primary Key in a Table
  1. Open the table.

  2. Right click on the table tab >> Design View.

  3. Right click on the desired field name >> Primary Key. A small key icon should appear to the left of the field.

• If the primary key consists of more than one field, repeat the preceding step for each field in the primary key while holding down the control key. (If you don't hold down the control key, the primary key is merely removed.)

H. Importing

• Data for a table can be imported from existing files of various formats, for example, text or Excel.
  Importing Table Data
  1. Create a new database or open an existing database that is to contain the imported table.

  2. Select the External Data tab >> Click Excel button (3rd from right in the Import group of toolbar buttons).

  3. Specify the source of the data (Excel file name) in the File name box.

  4. Make sure that the Import the source data into a new table in the current database.

  5. Click OK.

  6. Follow the directions of the Import Data Wizard.

I. Exporting

• Access tables (including tables produced from queries) can be exported in various file formats, including fixed width text, comma separated value and Excel.
  Exporting Table Data
  1. Select the External Data tab.

  2. Choose the Excel, Word, or Text File button in the toolbar.

  3. Follow the directions of the Export Wizard.

J. Normalization

K. Relationships

L. Queries

• A query is information extracted from portions of one or more tables in a database and displayed as a table. This table produced by a query is sometimes called a view.

• Update query types exist that can modify the data in existing tables.
  Creating a Query
  1. Select the main menu Create tab.

  2. In the Other group on the right, select the Query Design button.

  3. Show all tables that will be involved in your query.

  4. Fill out the grid to define the query like you did to define a filter. The complication is that queries can define involve more than one table and you can hide columns in a query.

  5. When you have finished defining the query grid, click the Run button (red exclamation mark). The view that shows the result of the table will be displayed.

  6. To make modifications to the query grid, select the Design View dropdown menu on the very left.

  7. Close the query and give it a name to save it.

  8. To open a saved query, click on the down arrow in the Navigation Pane panel on the left >> Select Queries to all all of the saved queries.

• An aggregate query is a query with summaries and/or groupings.
  Creating an Aggregate Query
  1. Select the main menu Create tab.

  2. In the Other group on the right, select the Query Design button.

  3. To modify to grid to allow a Total row, use this trick: right click on the Relationship window outside of the grid or tables >> Select Query type >> Crosstabs Query. Then right click again on the Relationships window >> Select Query Type >> Select Query. A Total row has been added to the grid.

  4. Fill in the grid to define the query. Enter Count, Sum, Ave, or one of the other choices in the Total row to get a summary for that column. Enter Group by for column A of you want the summary variable for another column grouped by column A.

  5. Run the query by clicking the Run button.

• Parameter Queries

• Sometimes you want to select certain values from a table, but you don't want to choose the value ahead of time. You want the user of the database to enter the selected value interactively.

• To do this, enter the expression [Text of Prompt] in the Criteria row for the field for which you are selecting a value interactively. For example, an interactive query expression might be [Enter the Realtor Last Name:].

M. Reports

• The easiest way to design a report is to use the Report Wizard.

• An access report can show detailed records and also summary information.
  Using the Report Wizard
  • Select Reports from the list of Objects on the Database Dialog.

  • Double click on Create report by using wizard.

  • This brings up Page 1 of the Report Wizard.
    1. Select the table or query that you want to use for the report.

    2. Move the fields that you want in the report to the right.

    3. Select Next.

    4. Move the fields that you wish to appear from Available Fields on the left to Selected Fields on the right.

    5. Click Next to move to Page 2.

  • Page 2 asks "Do you want to add any grouping levels?"
    1. Move any fields you want to use for grouping levels to the left.

    2. Click Next to move to Page 3.

  • Page 3 asks "What summary and sort order information do you want for detail records?"
    1. Enter sort information. You can enter sort information for up to four fields.

    2. Click the Summary Options button.

    3. Indicate what summary information you would like calculated.

    4. Choose to show detail and summary or summary only.

    5. Click Next to go to Page 4.

  • Page 4 asks "How would you like to layout your report?"
    1. Choose the layout and orientation. Stepped is a good layout, unless you have reason to choose another one.

    2. Click Next to move to Page 5.

  • Page 5 asks "What style would you like?".
    1. Choose the style.

    2. Click Next to move to Page 6.

  • Page 6 asks "What title do you want for your report?"
    1. Enter the title.

    2. Choose whether you want to preview the report or modify the design.

    3. Click the Finish button.

• Right click on the report and select Report Design to modify the report.

• Modify the fields on the Report Design until the report looks good to you.