lesson 7: Opening a Table and Learning to Navigate Records

Opening an Existing Table in Datasheet View

Tables are created and maintained in the Tables tab of the Objects palette in Microsoft Access. The Contact Management database contains four tables: Calls, Contact Types, Contacts, and Switchboard Items. We will discuss the Contacts table in this lesson.

A table opened in Datasheet View resembles a Microsoft Excel spreadsheet. It is used to input records into the database and will be discussed in this lesson.

To Open an Existing Table (Contacts) from the Tables Object:

• Open the Contact Management database.
• In the database window, click on the Tables tab from the Object palette. 
  
  
  (If the database window is not displayed, then choose WindowContact Management: Database from the menu bar.)
  
  
  
  
  
• To open the table in Datasheet View, double-click the Contacts table in the right window pane.
  
  
  (You could also either click once on the Contacts form and then click the Open button, or you could right-click on the Contacts form and select Open from the shortcut menu. 
  
  

  Using the Status Area to Navigate through the Records in a Table

  The Datasheet View looks like an Excel spreadsheet. It consists of columns and rows. Field names -- Contact ID, First Name, Last Name, Dear, Address, City, State, etc. -- appear as column headings. Records represent rows with data input into the fields. The number of rows that appear in the table will equal the number of records that have been added to the table. One blank row always appears as the last row of the database. This blank row is used to add a new record the table.
  
  The status area in the lower left area of the window indicates the number of records in the table as well as the record number displayed in the window.
  
  

  To Navigate Using the Status Area:

  • Click the arrows in the status area to move to the first record, previous record, next record, or last record in the table.
  • The new record button displays a blank row into which information for a new record can be added to the table.
  • 
    

  • Displaying Records by Scrolling Through the Table

    The vertical scroll bar is located along the right edge of the screen and can be used to move up or down the table if the total number of records exceed the records shown on the screen. Similarly, the horizontal scroll bar located at the bottom of the screen can be used to move left or right across the window if the fields in a table exceed the number of fields displayed on the screen.
    
    

    Using the Vertical Scroll Bar:

    • Click the up or down arrows in the scroll bar to move up or down through the table records.

    Using the Horizontal Scroll Bar:

    • Click the left or right arrows in the scroll bar to move left or right through the fields in a table.
    • 
      

Using the Keyboard to Navigate Table Records

Another way to navigate records in a table is to scroll up and down one screen at a time using the PageUp and PageDown keys on the keyboard. Other keys also provide shortcuts to move through fields and records, including:

Key:	Selects the:
Tab	Next field to the right
Shift + Tab	Next field to the left
End	Last field of the record
Home	First field of the record
Down Arrow	Same field in the next record
Up Arrow	Same field in the previous record
Page Down	Next screen of table records
Page Up	Previous screen of table records
Ctrl + End	Last field of the last record in the table
Ctrl + Home	First field in the first record in the table

The Record Selector

The Record Selector located to the left of each record in Datasheet View identifies the operation being performed against a selected record at any given time.

The Record Selector can display any of the following symbols:

• indicates the current record. The record is saved with the information displayed on the screen.
• indicates the selected record is being edited and the changes have not yet been saved.
• indicates a blank record into which information for a new record can be added to the table.

A blank record row always appears as the last row in a table. You must add new table records using this 

row

Read More

lesson 6: Using a Wizard to Create the Contact Management Database

Launching the Database Wizard

The Contact Management database was created using the Microsoft Access Database Wizard. We will briefly review the steps involved in creating this database.
Microsoft Access 2003 is equipped with a database wizard that can be used to create any of ten simple databases: Asset Tracking, Contact Management, Event Management, Expenses, Inventory Control, Ledger, Order Entry, Resource Scheduling, Service Call Management, and Time and Billing.
In this lesson we will briefly review the steps involved in creating the Contact Management database.

How the Contact Management Database was Created Using the Database Wizard:

• Choose FileNew from the menu bar.
• Click once on the Databases tab near the top of the New window.
  
  
  
• If the Templates window does not show, you may need to select On my computer from the task pane on the right side of your screen.

• Click once to select on the Contact Management icon.
• Click the OK button to launch the wizard.
  
  
  
• Specify the location where the database will be saved.

Verifying the Intent of the Contact Management Database

The first screen of the wizard verifies the type of information that will be stored in the Contact Management database: Contact information and Call information.

• Click the Next button to continue.

Selecting the Tables and Fields to be included in the Contact Management Database

The second screen of the wizard identifies the tables to be created. Three tables were created: Contact information, Call information, and Contact Types.
Click on any table to display the fields contained to it in the Fields in the table column. Click on a different table and other fields used by that table will replace the ones displayed for the table selected.

• Accept all other default field selections by leaving those checkboxes marked by a check.
• Click the Next button to continue.

Defining the Format of the Screen Display

The third screen asks the question, What style would you like for screen displays? Microsoft Access provides ten different screen displays for your use. A thumbnail picture is also provided for each format listed.

• Click through the different format options displayed on the screen -- Blends, Blueprint, Expedition, etc. -- to display a picture of each format on the left side of the wizard screen. Highlight the desired format -- the Contact Management database used the Standard format -- to be used.
• Click the Next button to continue.

Defining the Format of the Report Display

The third screen asks the question, What style would you like for printed reports? Microsoft Access provides six different report displays for your use. A thumbnail picture is also provided for each format listed.

• Click through the different format options displayed on the screen -- Bold, Casual, Compact, etc. -- to display a picture of each format on the left side of the wizard screen. Highlight the desired format -- the Contact Management database used the Corporate format -- to be used.
• Click the Next button to continue.

Naming the Database

The next step is to assign a name to the database.

• Assign a name to the database by typing a file name in the What would you like the title of the database to be field.
• Click the Next button to continue.

Finishing the Wizard

The final step asks whether the database being created is to be opened after it is built by the wizard? It has no bearing whatsoever on the actual building of the database. If you don't want to open the new database at this point then you can always return to it later and open it in Microsoft Access.

• Leave or remove the checkmark that appears in the checkbox associated with the Yes, start the database prompt.
• Click the Finish button to initiate the database build.
  
  

Read More

lesson 5:Getting to know the Contact Management database

Download the Contact Management Database

The Contact Management database used in this course can be downloaded from the GCFLearnFree.org® Web site and installed on your computer.
GCFLearnFree.org's Access 2003 lessons all use examples from the Contact Management Database. However, if you'd rather work with another existing database, you should have little problem following our lessons.

To Download the Contact Management Database:

• Click the link, and download and save the Contact Management database (992k).
  
  (Note, you must have an open connection to the Internet in order to download the database).
  
• The following dialog box appears:
  
  
  
• Click the Save button.
• You are prompted to save the database in some location on your computer. You will want to save the database in the c:\My Documents folder.
  
  
  
• Click the Save button to download the database.

Open the Contact Management database

Now that you've downloaded the Contact Management database, let us open and become familiar with it. We will briefly discuss how it was created and look at some of its database components.
If properly downloaded, the Contact Management database will be present in the c:\My Documents folder on your computer. It is recommended that you do not move the database from this location until after you have completed the Access 2003 course.

To Open the Contact Management database:

• Open Microsoft Access by selecting its associated icon on the Windows desktop.
• Choose File Open from the menu bar.
  
  
  
• The Look In box in Microsoft Access 2003 defaults to the c:\My Documents when the application is first opened.
  
  If the My Documents folder does not appear in the Look In box, then click in the Look In drop-down box, and locate and select the My Documents folder.
• Select the Contact Management.mdb file.
  
  
  
• Click the Open button to open the Contact Management database.

The main Contact Management database screen appears on your computer screen:

Reviewing the Contact Management Tables

Tables are found in the database Object called Tables. The Contact Management database contains four tables: Calls, Contact Types, Contacts, and Switchboard Items.

Reviewing the Contact Management Forms

Forms are found in the database Object called Forms. The Contact Management database has seven forms that are used as both menus and data entry screens. These forms are Call Details Subform, Call Listing Subform, Calls, Contact Types, Contacts, Report Date Range, and Switchboard. You can define additional forms to meet your data entry needs.

Reviewing the Contact Management Reports

Reports are found in the database Object called Reports. The Contact Management database has two reports defined to it: Alphabetical Contact Listing and Weekly Call Summary reports. You can define additional reports in the database depending on your reporting needs.

Read More

lesson 4:Database Tables

The core component of a database is a table. Data is defined and stored in a table. Multiple tables -- each consisting of different types of data -- can be created in a database.

Each row in the database is called a record. The entry for John Smith is called a record. The entry for Martha Tompkins is also a record. Each row or record is made up of columns or fields -- L.Name, F.Name, Phone, Address, City, State, Zip -- which contain a particular piece of information. 

L.Name F.Name Phone Address City State Zip 

Smith John 919.555.6320 100 Paramount Parkway Morrisville NC 27560 

Tompkins Martha 919.555.6427 97 Hummingbird Court Cary NC 27513 

Field Properties

Every table contains a number of columns called fields or datatypes. Fields are unique pieces of information that make up the information in a table. Tables usually contain multiple fields.
In a previous example we mentioned that a table might consist of the fields: Last Name, First Name, Phone, Address, City, State, and Zip. Each field has unique properties. Some contain characters. Others contain numbers. These Field Properties are defined when the table is created.

In a Contact Management database, a list of names -- those contacts to whom you have sent resumes or have met through your personal network -- might be maintained in a table, along with address, phone number, and other personal information.

Understanding Table Relationships

Databases can be simple -- consisting of a single table -- or made up of many different tables. If you were to convert your resume into a database, for example, you might have a table that contains your name and personal mailing address. We might call this the Contact_Information table.

Your work experience is a different kind of information. Instead of identifying who you are or where you live, it identifies the companies you worked for, their addresses, your job title, and responsibilities. Because this set of information is independent from the contact information, we might instead create a second table called the Work_Experience table.
The same is true of your educational background. It has no direct bearing on your contact information or the companies where you worked. A third table might be created called the Education table to save this kind of data.
The database contains three tables, each independent of the other, and all containing different types of information. The database needs a way to connect these three tables.

Primary Keys

Every table in Microsoft Access must have at least one field that uniquely identifies each record in the table. This field is known as a primary key. This primary key essentially opens the door to the table and allows you to retrieve information from the table.
The primary key is the mechanism by which you relate different tables and combine information for viewing (query) or printing (report).

Challenge!

Let us pretend that you are going to start your own home business. You are going to need to prepare a database for your business and the first step is to design a table that will contain a list of all your contacts that may one day be your clients or suppliers.
Let us assume that a database table called Business Contacts contains all the fields listed in the first column of the following chart. The chart also contains four other columns:

• Text, Numbers or Both -- should the field accept data entry input in the form of text, numbers, or both?
• Field Size -- the number of spaces you think the field should have.
• Required Entry -- should the field be required entry (the record cannot be added to the database without the information) or optional entry (a record can be added without entry in the field)
• Allow Duplicates -- should the database allow duplicate entries for the field (e.g., can several people in the database share the same zip code)?

Practice designing fields by filling out the following table.

	Text, Numbers, or Both?	Field Size	Required Entry?	Allow Duplicates?
First Name
Last Name
Company
Company Type
Address
City
State
Zip Code
Contact Id
Title
Work phone
Fax Number

Read More

lesson 3: Identifying the basic parts of the Access window

Launching Microsoft Access from the Windows Desktop

Microsoft Access is launched from the Windows desktop in a manner similar to that used to launch Microsoft Word, Excel, or PowerPoint. You can launch Microsoft Access either from the desktop shortcut or from the Start program.

To open Microsoft Access using the desktop shortcut:

• Double-click the Microsoft Access shortcut icon on the Windows desktop.
  
  
  

To open Microsoft Access using the Start program:

• Click the Start button located in the lower left corner of the Windows screen.
• Click the Programs option on the Start menu.
• Click the Microsoft Access selection.
  
  
  (If Access does not appear, then click the double down areas immediately below the Microsoft PowerPoint entry and locate Access from a complete list of software installed on your computer.
  
  
  
  
  

  The Main Access Window

  When you open Microsoft Access, many items you see are standard in most Microsoft software programs like Word, Excel, and PowerPoint.
  
  

  Menu bar

  
  The Menu bar displays all the menus available for use in Access 2003. The contents of any menu can be displayed by clicking on the menu with the left mouse button.
  

  The Main Access Window (continued)

  Toolbar

  The pictured buttons in the toolbar are quick and easy shortcuts to specific actions. For example, if you want to save a spreadsheet using the menus then you would first click File and then click Save. Using the toolbar to perform this save operation actually saves you a click. Click the save button once to save the spreadsheet.
  
  Some commands in the menus have pictures or icons associated with them. These pictures may also appear as shortcuts in the Toolbar.
  

  
  

  The Database window

  Database Toolbar

  
  The Database toolbar presents operations that can be performed against different database objects, including buttons to open an object, design an object, create a new object and delete an object.
  

  Left Pane

  
  Database objects are created and opened by choosing any of the buttons listed in the left pane of the database window.
  

  Right Pane

  
  Any of the first four options -- Tables, Queries, Forms, or Reports -- opens additional choices related to that selection in the right pane.
  

  Choosing the Tables button, for example, displays at least three options in the right pane: 1) Create a table in Design view, 2) Create a table by using wizard, and 3) Create table by entering data. It also shows any objects that you created in the database.
  

  Challenge!

  • Display the contents of the File, Edit, View, Insert, Tools, Window, and Help menus in the main Access menu bar. As each menu is displayed, you may want to write down all the contents listed under each.
    
    
    
  • Identify the operation performed by each button in the main Access toolbar.
    
    
    
  • Some buttons in the toolbar also appear alongside options listed in a menu. For each button in the main Access toolbar, identify the menu where that operation can be performed.

Read More