Coming up in this Lesson

In this lesson students will learn the commands to create and modify a table and enter data. Students will also be taught how to search for and edit data and to sort their data into order. Commands to create reports and labels will also be covered.

TABLE OF CONTENTS

HELP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

QUIT ACCESS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

WHAT IS A DATABASE/TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

USING THE MOUSE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

USING ACCESS’S MENU. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6

USING DIALOG BOXES AND TOOLBARS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6

CREATE A DATABASE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

CREATE A NEW TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

EDIT A TABLE’S DESIGN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

SAVE A TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

PRINT A TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

ADD DATA TO A TABLE IN DATASHEET VIEW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

CREATE A DATA ENTRY FORM. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

VIEWING DATA IN A TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13

EDITING AND DELETING DATA IN A TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

MOVING AROUND THE TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

SELECTING RECORDS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

USING QUERIES TO SELECT DATA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

UPDATING DATA WITH QUERIES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

SORTING DATA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

WORKING WITH RELATIONAL DATABASES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

SETTING RELATIONSHIPS BETWEEN TABLES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

REPORTS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

LABELS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27

1. STARTING UP MICROSOFT ACCESS

a. To start Microsoft Access

1. Click on the Start button

2. Choose Program

3. Choose Microsoft Access

4. Specify whether you want to:

a. Create a new blank database

b. Create a new database with a wizard

c. Open an existing database (select the one you wish to open)

5. Choose OK

2. EXITING MICROSOFT ACCESS

a.Using the mouse

1. Choose File, Exit

2. Choose Yes to save document, then name or rename the file, if needed, or choose Yes to save changes to an existing document.

(OR)

3. Choose No to close any open documents without saving changes and to exit Microsoft Access.

3. GETTING HELP

a. Starting Help

1. Click on Help with mouse*

2. Select item desired from menu

b. Exiting Help

1. Choose the Cancel button

c. Help Options

Microsoft Access offers a full range of help topics, each one offers it’s own type of help

*The Microsoft Assistant is another method that you have to get help. It ‘watches’ the way you work and a will suggest better ways to work from time to time. You can also click on it at any time to get help. You may close it if you wish. To reopen it, click on the question mark icon on the toolbar.

4. WHAT IS A DATABASE/TABLE?

A Database is a collection of organized information. A database may contain several ‘Tables.’ Usually, each table will hold different types of information, much as we have separate folders in a filing cabinet for different forms. We often put several related folders in our filing cabinets in the same drawer--with Access you’ll be putting several related tables in a database. With a table you can search for particular pieces of information or sort your data into alphabetical or numerical order. Access also lets you ‘pull’ information from more than one table at once.

Title bar Menu bar Toolbar Field Record

5. USING THE MOUSE

While this course guide assumes the user has a basic knowledge of Microsoft Windows, below is review of mouse terminology.

1. Mouse button Buttons on top of mouse (the left button is used unless otherwise specified)

2. Pointer A cursor representing the mouse's position on the screen

3. Insertion point A blinking line cursor that appears when you are typing

4. Point To move the mouse cursor onto an object

5. Click To press and release the mouse button once

6. Double click To press and release the mouse button, twice rapidly

7. Drag To hold the mouse button down, "drag" or slide the mouse until the pointer is in the desired location and release the mouse button

8. Scroll Bars Scroll bars provide a means to move your insertion point through a document or screen quickly.

9. Scroll Arrow Use to scroll in small increments, click up or down arrow

10. Scroll Box Use to move in large increments, click and drag scroll box

6. USING MENUS

a. Pull Down Menus

1. Click on the menu item you want, the menu will drop down

2. Click on the desired option

b. Shortcut Menus

Shortcut Menus (accessed with the right mouse button) provide easy access for specific features from various locations.

1. Place the mouse pointer in the document window, then click the right mouse button.

2. Click the left mouse button in another area of the document window to close the menu without selecting an option.

3. Experiment with Shortcut Menus by clicking the right mouse button in other areas.

7. USING DIALOG BOXES

Microsoft Access's dialog boxes include preview windows that show how changes will look before they are applied to a document. Notice that as you make changes the preview window changes as well.

a. For example, choose Format, Autoformat from the menu

b. Select options, such as effect, speed, etc.

c. Choose Cancel to close the Transitions dialog box without applying the changes to your slide show.

1. TAB Moves the insertion point or cursor from box to box

2. Check boxes May choose several

3. Option buttons May choose only one from each set,

4. Command buttons Square, carry out actions

5. Drop down list presents a list of selections when arrow is selected

6. Text box a box to type text in

7. Tab dialog box Overlapped dialog boxes - click on the tab of box to be used

Menu items that are followed by an ellipses (...) lead to a dialog box menu (and will prompt you for more information). Menu items followed by a triangle lead to a submenu.

You will find many menu choices are grey (and unselectable) these options do not apply to the current function you are working with.

8. USING THE TOOL-BAR

The Tool-bar gives you quick access to editing features as well as lets you automate menu items, features, executable files, macros, and other tool-bars you use most often. There are several tool-bars which ship with Access or you may define your own as you advance in your use of Access.

a. Position the pointer over a button on the tool-bar, a help prompt will describe the feature at the top of your screen.

b. You can also change the position of the tool-bar on your screen.

1. Place the pointer on the vertical double-lines at the far left of the tool-bar (not on a button)

2. Drag the tool-bar into the bottom right inside the document window. An outline will indicate the new position of the tool-bar before it actually moves, or drag the tool-bar anywhere in the document window.

c. To return the tool-bar to the original position near the top of your screen

1. Place the pointer on a space or blank area of the tool-bar, drag the tool-bar back to the top edge of the document window, release the mouse button when the tool-bar outlines a horizontal box.

9. DATABASE TERMS

a. FIELDS -- a field is a category of information

b. RECORDS -- A record is a collection of information about a person/thing; a full set of answers to the field "questions."

c. TABLES -- A collection of records

d. DATABASE -- A collection of tables that are usually related to each other through common fields

e. SORTING -- Rearranging table information into alphabetical or numerical order

f. QUERY -- A request for a selection of information from a table, usually you provide an example of what you are looking for

g. NORMALIZATION - A database design theory that includes standards for the creation of tables. The process of organizing data to meet these standards is called normalizing data, or normalization. In order to create valid table relationships, a table needs to be at least meeting the first three normal forms.*

h. LNC - The Leszynski Naming Convention. A suggested style for naming objects, fields, tables, etc. that is designed to help users and programmers follow a recognizable pattern. Typically a prefix (in lower case) is followed by the name of the item, with the first letter capitalized, and the rest lower case.

Example:

1. LNC for objects - Table: tbl tblAttendees

Form: frm frmAttendees

Query: qry qryExtractLastName

Report: rpt rptByArea

2. LNC for fields - Text: str strFirstName

Number: (long integer) lng lngQuantity

Number: (single) sng sngTaxRate

Autonumber: lng - cnt lngOrderIdcnt

Currency: cur curPayrate

Date/time: dtm dtmBirthDate

Yes/no: ysn ysnBonded

10. TABLE DESIGN CONSIDERATIONS

a. Steps in designing a new table

1. Gather the information which needs to be organized

2. Determine the purpose of your database

3. Decide what tables you’ll want in your database (observing data normalization)

4. List on paper all the ‘fields’ or data categories that you’ll want in each table

5. Determine what the relationships (if any) will be between the tables*

6. Create the table structure, Enter a few records

7. Design and use reports

8. Discuss your data and tables with everyone who will be using the database to get their ideas

Normalizing data - When your data is normalized, there is the least amount of repetition necessary in your tables, and the data in each table makes “sense” there. There are five levels of normalization, each more stringent than the last. For more information on this, see section 30.

11. THE DATABASE

The Database is generally used as a ‘center of operations.’ You can use it to quickly open or modify tables, reports, etc., and to browse through your data. It provides an excellent way to organize your different tables into related groups.

a. Create a new blank database

1. Choose File, New Database

2. Choose Blank Database and choose OK

3. Type a name for the new database

b. Create a database from the database wizard

The database wizard will help you create common types of databases, with their associated tables, forms, reports etc.

1. Choose File, New Database

2. Choose Wizards tab

3. Select desired database wizard

4. Choose Next to move to next step in wizard (Back to return to a previous step)

c.Open an existing database

1. Choose File, Open Database

2. Specify database you wish to open

When you create a database with one of Access’s wizards, it will create a special menu called the “Switchboard” that you can use as a center of operations as well. You can use this if you wish, but if you need to get to the database Window at any point you can do so by pressing F11.

12. CREATE A NEW TABLE IN A DATABASE

a. Open the Database Manager Window, if necessary, with the File, Open Database command

b. Click on the Tables tab in the Database Window

c. Chose the New button

d. Select Design View and choose OK

e. Type in a name for the first field in the field name column (you may wish to use the LNC)

f. Tab to the data type column and choose desired type:

1. Text - allows use of all numbers, letters and symbols

2. Memo - a field of variable length, can hold up to 65,535 characters

3. Number - allows use of numbers, decimal point and a leading + or - sign

a. Long integer - an integer, or whole number

b. Single - a number that can be a fraction or have a decimal portion

4. Date/Time - date and time from the years 100 to 9999

5. Currency - currency values and numeric data

6. Autonumber - a unique sequential number (incremented by 1), or a random number

7. Yes/No - one of two values: yes or no

8. OLE object - a Windows object like an Excel spreadsheet, graphic, sound etc.

9. Hyperlink - text used as a hyperlink address to a URL

10. Lookup wizard - a wizard that sets up your data so that you can choose a value from another table

c. Select other options as desired from the General tab at the bottom of the screen‡

d. Click in the Description area and type a description for the field

e. Tab to the next field

f. Continue adding fields as needed

g. Click on the field that you wish to be your Primary Key* and choose Edit, Primary Key

h. Choose File, Save As and provide a name for the new table (you may wish to use the LNC)

13. CREATE A TABLE WITH A WIZARD

The database wizard will help you create common types of databases, with their associated tables, forms, reports etc.

a. In the Database Window (press F11 to view if necessary), select the Tables tab

b. Select the New button

c. Choose Table Wizard and the OK button

d. Select the type of table you wish to create (Access will provide you with a selection of common table-types)

e. Place your cursor on the first field from the Sample Fields list that you want in your new table and press the  to move that field over to the Fields in my Table list

f. Continue adding fields as needed

g. Choose Next to move to next step in wizard (Back to return to a previous step)

LNC - the Leszynski Naming Convention suggests that you name all tables with the prefix tbl (e.g. tblInventory). Forms are named with the prefix frm, queries with the prefix qry and reports with the prefix rpt.

*Primary key - a field with values that uniquely identify each record in a table. A primary key is necessary if you wish to relate a table to another table. Access will not let you save or exit a table without creating a primary key.

‡Field Properties

Format - a method to format the way the data will look regardless of how it is typed (e.g. > will change data to uppercase)

Input Mask - provides a ‘template’ or style for typing (e.g. (999) 000-0000 for a phone number, where 0 represents a number that must be typed. 9 would represent a number that can optionally be typed. Additionally, L represents a letter that must be typed and ? A letter that can optionally be typed.

Default Value - a ‘starting’or default value that most times will be used. It can be typed over. (e.g. NY for State field)

Caption - A more descriptive title for the field (e.g. Student I.D. vs. lngStudentIDcnt)

14. EDIT A TABLE’S DESIGN (STRUCTURE)

a. In the Database Window, select the Tables tab, and highlight the table you wish to edit

b. Choose the Design button

c. Make changes as needed

1. Insert a new field

a. Move cursor to the field you wish new field to appear above

b. Select Insert, Rows

c. Add Name, Type and Width information for that field

2. Delete a field

a. Move cursor to field you wish to delete

b. Select Edit, Delete Rows

d. Save and close the table (File, Save - File, Close)

15. OPEN A TABLE (VIEW THE DATA)

a. In the Database Window, select the Tables tab, and highlight the table you wish to view

b. Choose the Open button

c. Choose File, New

16. SAVE A TABLE

a. Save and name a table

1. Choose File, Save As

2. Provide name and specify directory for new table

3. Choose OK

b. Save previously named table

1. Choose File, Save

17. CLOSE A TABLE

a. Choose File, Close

18. PRINT THE ENTIRE TABLE*

a. Choose File, Print

*To print a customized report, see section 32.

19. ADD DATA TO A TABLE IN DATASHEET VIEW

a. In the Database Window, select the Tables tab, and highlight the table you wish to add data to

b. Choose the Open button

c. Place cursor on record with the * at the bottom of the table, type data in first column

d. Press Tab to move to next column and type data

20. CREATING A DATA ENTRY FORM

a. In the Database Window (press F11 to view it, if necessary), select the Form tab

b. Choose the New button

c. Select the Form Wizard option