INTERMEDIATE ACCESS 2000

Table of Contents

Page

LESSON 1: IMPORTING AND COPYING DATA 1

¨ Importing Information from Another Database 1

¨ Copying a Table within the Existing Database 2

¨ Converting Tables from Excel 5

¨ Copying Data into an Existing Table 9

LESSON 2: FORM DESIGN VIEW 11

¨ Creating Forms 11

¨ Design View 14

¨ Changing the AutoFormat 15

¨ Changing Fonts 16

¨ Adding Color 16

¨ Arranging Form Controls 18

¨ Changing the Order of Data Entry 19

LESSON 3: DATABASE RELATIONSHIPS 22

¨ Relationship Types 23

¨ Building Relationships Using the Table Wizard 24

¨ Joining Tables in a Query 29

¨ The Relationships Window 32

LESSON 4: SUBFORMS 37

¨ Creating Subforms Using the Wizard 37

LESSON 5: CUSTOM REPORTS 42

¨ The Report Wizard 42

¨ Label Reports 47

¨ Publishing Access Reports in Word 52

¨ Merging Access Data in Word 52

LESSON 6: COMPACTING AND REPAIRING YOUR DATABASE 56

¨ Compact an Open Database 56

¨ Compact Automatically 56

LESSON 7: GIVE IT A TRY! 59

LESSON 8: DELETING OBJECTS 61

¨ Deleting Objects 61

¨ Deleting Databases 61

NICE TO KNOW 63

¨ Mouse Pointers 63

¨ Toolbar Buttons 64

¨ Keyboard Shortcuts 66

¨ The Toolbox 67

This workbook may be reproduced in whole or in part by an employee of the Department of Human Services or Department of Mental Health, Mental Retardation and Substance Abuse Services. All other reproduction is prohibited unless written permission is obtained from the Training Institute.

Last Updated: April 17, 2001

LESSON 1:   IMPORTING AND COPYING DATA

OBJECTIVE: Use information from other sources in your database.

¨  Importing Information from Another Database

OVERVIEW

If a database object containing the structure and/or data you require already exists, you may import it from another Access Database, or convert it from a different database file format - such as dBase, Paradox, etc.

STEPS

1. With the database you are bringing data into open, click FILE, GET EXTERNAL DATA, then IMPORT.

2.  If needed, change the file format in the FILES OF TYPE area.

3.  Select the database containing the information to be copied, then click IMPORT.

4.  Click once on the object to import.

5. Click on OPTIONS and make the appropriate selections. If copying a table, in the IMPORT TABLES section, select either DEFINITION AND DATA (to copy the table including the data) or DEFINITION ONLY (to just copy the field names and properties of the table). Queries may be copied as queries or tables.

6. Click OK.

7. If desired, right click on the object name and select RENAME. Type in a new name and press ENTER.

¨  Copying a Table within the Existing Database

OVERVIEW

You may wish to duplicate a table for another use within the same database. For example, you may have several regions whose information you would like to track identically, but in separate tables.

STEPS

1. Click the TABLE to copy. Click the COPY button on the toolbar.

2. Click PASTE and select either STRUCTURE AND DATA (to copy the table as is) or STRUCTURE ONLY (to just copy the table's field names, data types, and properties). Click OK.

3. Right click on the table and select RENAME. Type in a new name and press ENTER on your keyboard.

ü  Lesson Example

You have a database named Test that includes a table you would like to use in a new database. Your instructor will give you the location of this file and any other files you will need throughout the class.

STEPS

1.  Open Microsoft Access, and create a new database named Course Schedule in the C:\MY DOCUMENTS folder.

2.  With your Course Schedule database open, click FILE, GET EXTERNAL DATA, then IMPORT.

3.  Select the Test database, then click IMPORT.

4.  Click once on the Classes table.

5.  Click the OPTIONS button and select DEFINITION AND DATA.

6.  Click OK.

EXERCISE:

1) Copy the structure only of Classes table that you just imported.

2) Name the new table Classes This Year.

3) Open the new table in Datasheet View.

4) Close the table.

5) On Your Own: Copy the structure and data of the Classes table.

6) Name the table Classes Next Year.

7) Open this new table in Datasheet View.

8) How is this table different from the original Classes table? From the imported Classes This Year table?

9) Close the table.

¨  Converting Tables from Excel

OVERVIEW

Microsoft Excel can perform many of the functions that Access can with a table of data. However, you may find that Excel's limitations restrict you from doing some of the things you would like to do with your data. In this case, you can convert an Excel spreadsheet into an Access table.

STEPS

1. With a database open, click FILE, GET EXTERNAL DATA, then IMPORT.

2. If needed, change the file format in the FILES OF TYPE area to Microsoft Excel (.xls).

3. Select the file containing the information to be converted, then click IMPORT.


4. Select the worksheet to be imported, and click NEXT.

5. Indicate if the first row of information contains labels for column headings by clicking the check box to insert or remove the checkmark. Click NEXT.


6. Choose whether you wish the data to go into a new table or into an existing table. Click NEXT.

7. Click on any fields you do not wish to import and click the DO NOT IMPORT FIELD (SKIP) check box. Click NEXT.

8. Make the appropriate selection regarding a primary key. Let Access create one, choose from existing fields yourself, or do not assign a key at this time. Click NEXT.

9. If necessary, type an appropriate name for your new table, and click FINISH.


10. Access will display a dialog box indicating that it has completed the import process. Click OK.

¨  Copying Data into an Existing Table

OVERVIEW

If you have two separate tables that have the same fields, and you would like to combine the data, you may bring the records from one table into the other.

STEPS

  1. Open the table containing the data to be imported.
  1. Click and drag your mouse pointer over the record selectors (gray boxes to the left of a record) of the records you wish to copy. Please note: The records must be next to one another - you may need to sort specifically for this purpose.
  1. Click the COPY button on the toolbar, and close the table.
  1. Open the table into which you would like to put the records.
  1. Select the entire empty record at the bottom of the table by clicking its row selector.
  1. Click the PASTE button in the toolbar.

ü  Lesson Example

You have created an Excel spreadsheet to store student information. You realize now that this data should be stored in an Access table.

STEPS

1. With your Course Schedule database open, click FILE, GET EXTERNAL DATA, then IMPORT.

2. Change the file format in the FILES OF TYPE area to Microsoft Excel (.xls).

3. Select the Student file, then click IMPORT.

4. Be sure the Students worksheet is selected, and click NEXT.

5. Indicate that the first row of information contains labels for column headings (be sure the check box is checked). Click NEXT.

6. Choose to have the data go into a new table. Click NEXT twice.

7. Choose the StudentID field as the primary key. Click NEXT.

8. Name your new table Students, and click FINISH.

9. Open the Students table and note how many records were imported.

10. Close the Students table.

EXERCISE:

1) Copy the records from the Classes Next Year table into the Classes This Year table.

2) Import the data from the Students worksheet of the People.xls file into the Students table.

3) On Your Own: Copy the student data from the Attendees table of the Test database into the Students table of your Course Schedule database.

4) How many records are now in the Students table?

LESSON 2:   FORM DESIGN VIEW

OBJECTIVES: Modify form design.

Correct tab order.

¨  Creating Forms

OVERVIEW

The Form Wizard allows you to quickly create a data entry form for a table.

STEPS

  1. From the Forms window, double-click CREATE FORM BY USING WIZARD.
  1. Select the table for which the data entry form will be created from the Tables/Queries drop-down list.
  1. Send the fields you wish to appear in the form to the Selected Fields box by clicking the name of the field in the Available Fields box, then clicking the button. Or, double-click each field name to be sent. If you wish to select all fields, click the button. Click NEXT.
  1. Select a layout for your form, and click NEXT.
  1. Select a style for your form, and click NEXT.

  1. Name your form, and click FINISH.

ü  Lesson Example

You wish to create a data entry form for your Classes table.

STEPS

1. In the FORMS window, double-click CREATE FORM BY USING WIZARD.

2. Select the Classes table from the TABLES/QUERIES drop-down list.

3. Click the button to select all fields. Click NEXT.

4. Select the COLUMNAR layout for your form, and click NEXT.

5. Select any style for your form, and click NEXT.

6. Name your form Classes, and click FINISH.

7. Close the form.


EXERCISE:

1) Create a Columnar data entry form for the Students table including all fields by using the Wizard.

2) Name the form Students, and close the form.

3) On Your Own: Using the Wizard, create a columnar form for the Classes This Year table including all fields.

4) Name the form Classes This Year, and close the form.

¨  Design View

OVERVIEW

Just as Design View of a Table or Query allows you to customize the object, Design View of a Form provides you with an opportunity to fine-tune your data entry device. You will generally notice three parts to Form Design View:

·  Form Header - appears at the top of each form.

·  Detail - this is the area where the majority of your objects are placed where the data fields from the Table will appear.

·  Form Footer - appears at the bottom of the form.

Each of these areas may be selected by clicking the gray bar indicating the section. To select the entire form, click the FORM SELECTOR button in the upper left corner of the report. The Object box in your toolbar will indicate that the report is selected. You may expand or contract these areas to accommodate existing or additional fields, labels, and other controls.

STEPS

  1. Click the DESIGN VIEW button in your toolbar.
  1. To enlarge the form area, move the mouse pointer to the right or bottom edge of the DETAIL section until you see a mouse pointer that is a line with a double-sided arrow through it. Click and drag to the right or down.
  1. To enlarge the header or footer area, move the mouse to the bottom border of the footer line until you see a horizontal line with a double-sided arrow through it. Click and drag down.
  1. Click SAVE.

¨  Changing the AutoFormat

OVERVIEW

Access contains several pre-designed formats that combine background color and pattern, as well as font color, size and style. When using AutoForm, the last AutoFormat chosen will apply to the new form. This may be easily changed.


STEPS

1. In Design View of the form, be certain the entire form is selected by clicking the FORM SELECTOR in the upper left corner.

2. Click FORMAT on the Menu bar, then AUTOFORMAT. Or, click the AUTOFORMAT button on the Toolbar.

3. Select the new background that you want. Click OK.

¨  Changing Fonts

OVERVIEW

AutoFormat determines font sizes, colors and styles. However, these may be changed for individual controls as needed.

STEPS

  1. In Design view of the form, select the control(s) you wish to modify.
  1. To change the font type, select a new font from the drop-down list in the toolbar.
  1. To change the font size, select a different number from the size drop-down in the toolbar.
  1. To change the style, click the BOLD , ITALIC , and/or UNDERLINE buttons.
  1. To change text alignment, click the appropriate alignment button in the toolbar.

¨  Adding Color

STEPS

1. From Design view of the form, click the control(s) you want to change.

2. To color the background fill of an object, click the FILL button on the toolbar, or use the drop-down arrow to choose another color.

3.  To change text color, click the TEXT button on the toolbar, or use the drop-down arrow to choose another color.

4.  To change a line color, click the LINE button on the toolbar, or use the drop-down arrow to choose another color.

5.  Save your changes.

6. Click the FORM VIEW button on the toolbar to see the form.

ü  Lesson Example

You would like to make some formatting changes to your Instructor form.

STEPS

1. Open the Classes form in Design View.

2. Click the AUTOFORMAT button and select Sandstone.