INTERMEDIATE ACCESS 2003

Table of Contents

Page

LESSON 1:IMPORTING AND COPYING DATA

Importing Information from Another Database

Copying a Table within the Existing Database

Converting Tables from Excel

Copying Data into an Existing Table

LESSON 2:FORM DESIGN VIEW

Creating Forms

Design View

Changing the AutoFormat

Changing Fonts

Adding Color

Arranging Form Controls

Changing the Order of Data Entry

LESSON 3:DATABASE RELATIONSHIPS

Decide on a Relationship Type

One-To-One

One-to-Many

Many-to-Many

Checking Properties and Creating the Join Table

The Relationships Window

LESSON 4:SUBFORMS

Creating Subforms Using the Wizard

LESSON 5:CUSTOM REPORTS

The Report Wizard

Publishing Access Reports in Word

Merging Access Data in Word

LESSON 6:COMPACTING AND REPAIRING YOUR DATABASE

Compact an Open Database

Compact Automatically

LESSON 7:GIVE IT A TRY!

LESSON 8:DELETING OBJECTS

Deleting Objects

Deleting Databases

NICE TO KNOW

Mouse Pointers

Toolbar Buttons

Keyboard Shortcuts

The Toolbox

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

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, IMPORT.

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

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

  1. 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 DEFINITIONAND 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 COP buttonon the toolbar.

  1. Click PASTE.

  1. Type the name of the table.
  1. 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).
  1. Click OK.

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 by clicking on CREATE A NEWFILE on the Task Pane. Then click BLANK DATABASE. Save it in C:\MY DOCUMENTS folderand name it Course Schedule.

2.Click FILE, GET EXTERNALDATA, IMPORT.

3.Select the Test database (your instructor will provide the location), then click IMPORT.

4.Click once on the Classes table.

5.Click the OPTIONS button and make sureDEFINITION ANDDATA is selected.

6.Click OK.

EXERCISE:

1) Right click the Classestable and selectCOPY.

2) Right click the white space in the window and selectPASTE.

3) Name the new table Classes This Year and paste the STRUCTURE ONLY.

4) Open the new table in Datasheet View.

5) Close the table.

6) On Your Own: Import the Teachers table from the Test database.

7) Right click the Teachers table and rename it Instructors.

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.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.

11. Because Excel stores numbers as ‘Double’, you may want to change the number fields, including the ID fields, to ‘Integer’ so your properties are consistent throughout the database.

Copying Data into an Existing Table

OVERVIEW

If you have two separate tables that have the same fields and field types, 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 first empty record of the table by clicking its row selector .
  1. Click the PASTE button in the toolbar.
  1. Click YES.

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.Click FILE, GET EXTERNAL DATA, 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. Click NEXT.

5.Select FIRST ROW CONTAINS COLUMN HEADINGS. Click NEXT.

6.Choose to have the data go IN A NEW TABLE. Click NEXT twice.

  1. Choose MY OWN PRIMARY KEYand select the StudentID. Click NEXT.
  1. Name your new table Students. Click FINISH.
  1. Click OK.
  1. Open the Students table and note how many records were imported.
  1. Go to Design View of the table and change the properties of the StudentID field from DOUBLEto LONG INTEGER.
  1. Save the table and click YES on the window. Close the table.

EXERCISE:

1)Copy the Classes table. Paste the STRUCTURE AND DATAinto a new table named Classes Next Year.

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

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

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

5)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.ClickFORMSon the Object bar, 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. Click NEXT.

5.Select any style for your form. Click NEXT.

6.Name your form Classes. 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 justified form for the Classes This Year table including all fields.

4)Name the form Classes This Yearand 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 header or 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 UNDERLINEbuttons.
  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.

  1. To change text color, click the TEXT button on the toolbar, or use the drop-down arrow to choose another color.
  1. To change a line color, click the LINE button on the toolbar, or use the drop-down arrow to choose another color.
  1. Save your changes and click the FORM VIEW button on the toolbar to see the form.

Lesson Example

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

STEPS

1.Open the Classes form in Design View.

2.Click the AUTOFORMAT button and select SANDSTONEand click OK.

3.Select all of your label boxes by either clicking on each while holding down the SHIFT key or by clicking and dragging to create a rectangle that touches all fields.

4.Change the font to TAHOMA, 12POINT, ITALICS.

5.In the FORMAT Menu, point to SIZE and selectTO FIT.

  1. Change the background color of the text boxes to yellow.
  2. Save your changes and go to FORM VIEWto see the finished product.
  3. Close the form.

EXERCISE:

1)Open the Students form in Design View and change the AUTOFORMATto Standard.

2) Change the font, color and background color of all of your fields' text boxes as you wish.

3)On Your Own: Change the AUTOFORMAT to one of your choosing. What happens to your previous formatting choices?

4)Save the form,look at it in FORM VIEWand close the form.

Arranging Form Controls

OVERVIEW

You may customize a form so that it is easier to enter information or change the order in which Access presents the information.

STEPS

1.To move a text box and its label, click and drag the control using the open hand mouse pointer .

2.To move a text box or its label, click the control once. Move your mouse pointer to the upper left corner of the item you wish to move. When you see a pointing hand , click and drag the control to the desired location.

3.To select several controls, hold down your SHIFT key and click on each control. Or, if your controls are near each other on the form, with the white, click and drag a rectangle around the group of controls you want. All controls touched or surrounded by the rectangle will be selected.

4.To resize a text box or its label, click the control once. Move your mouse pointer to any of the resizing handles (small black squares) around the border of the control until you see a two-sided arrow . Click and drag to expand or contract its size.

5.Click FORMAT in the Menu Bar, then point to ALIGN and click the appropriate alignment option.

Changing the Order of Data Entry

OVERVIEW

Once you have determined the appropriate locations for your controls, you may find that the order in which you Tab from one item to the next makes no sense.

STEPS

1.Be sure you are in Design View of the form.

2.Click VIEW on the Menu Bar.

3.Click TAB ORDER.

4.Be sure the DETAIL radio button is selected.

5.To change the tab order, click the gray box in front of the field to move, then click and drag it to a new place in the list. Click OK.

6.To quickly set a "logical" field order, i.e. from left to right/top to bottom of the form, click the AUTO ORDERbutton.

Lesson Example

You wish to rearrange the controls on your Classes form.

STEPS

1.Open the Classes form in Design View.

2.Move the Lab Fee field before the Material Cost field.

3.Save your changes and go to Form View.

4.Click the NEW RECORD button, and add the following information to the form. Pay attention to the order of entry.

TitleIntroduction to Access

Date12/12/01

InstructorID 1

LocationBangor

Max Capacity12

Material Cost$12.00

Lab Fee$2.00

5.Return to Design View. ClickVIEW on the menu and selectTAB ORDER.

6.Click the AUTOORDER buttonand click OK.

7.Save your changes and return to Form View.

8.Go to a new record and enter another class, making up all of the information.

9.Close the form.

EXERCISE:

1) In Design View of the Students form, move the Department and Classification fields after Zip field, rearranging surrounding fields.

2)View the TAB ORDERand click and drag the fields to rearrange them.

3)Make the State and Zip fields’ text boxes smaller.

4)Save your changes and go to Form View.

5)Using the form, enter yourself as a new student with a StudentID of 35.

6)Close the form.

7)On Your Own: Open the Classes This Year table in Design View and rearrange the fields to your liking.