CSCI A114 / INFO I111
Lab Notes One

First semester 2000-2001

Project One: Creating a database using design and datasheet views. The Bavant Marine Services database. Starting Access and creating a new database. The Access desktop and the database window. Creating a table. Defining the fields. Correcting errors in the structure. Saving a table. Adding records to a table. Closing a table and a database and quitting Access. Opening a database. Adding additional records. Correcting errors in the data. Previewing and printing the contents of a table. Creating additional tables. Adding records to the additional table. A1 and THQ1 posted.
Perform the following steps to start Access, create a new database, and save the database on a floppy disk. Place a formatted floppy disk in drive A:, click the Start button, then choose Programs, Databases, and then click on Microsoft Access.
Microsoft Access starts. Click "Create a new database using: Blank Access database."
Then click OK. The "File New Database" dialog box displays.
Look at the "File New Database" dialog box (move it around, etc). Locate the "Save in:" box, and the "Save in:" box arrow.
Only locate them. Locate the "File name:" text box and the Create button.
Now point your mouse to the "Save in:" box arrow. Click the "Save in:" box arrow.
The "Save in:" list displays. Locate the "31/2 Floppy A:" then click on it.
Now click inside the "File name:" textbox at the end of the text that appears inside it. Repeatedly press the Backspace key to delete "db1" (your number may be different).
Then type: ... as the file name. 
Bavant Marine Services
The file name is changed to "Bavant Marine Services". This will be the name of the database that you will be creating. Now point to the Create button, and click on it to create the database.
The "Bavant Marine Services" database is created. The "Bavant Marine Services: Database" window displays on the desktop. The Office Assistant, a tool you can use to obtain help while working with Microsoft Access may display.
If the office assistant displays, right-click the office assistant and then point to Hide on the shortcut menu. You should take some time to familiarize yourself with the Access desktop and the database window.
Yes, locate the following: ... and describe their functions.
  1. the Access window title bar
  2. the " :Database" window Close Window button
  3. the Access window menu bar
  4. the database window toolbar
  5. the Windows taskbar
  6. the status bar (and the message in it: Ready)
  7. the " :Database" window
Maximize the Access window and the "Bavant Marine Services: Database" window. Now you're ready for the next step.
Creating a table. Right-click "Create table in Design view".
The shortcut menu for creating a table in design view displays. Point to Open on the shortcut menu, then click on it.
The "Table1: Table" window displays. Click the Maximize button for the "Table1: Table" window.
A maximized "Table1: Table" window displays. As expected.
Now locate the following: ... and describe their functions.
  1. the status bar
  2. the upper pane (in which fields are created)
  3. the row selector (it's a small box or bar)
  4. the "Field Name" column
  5. the "Data Type" column
  6. the Description column
  7. the lower pane (in which field properties are specified)
Pressing F6 switches between the two panes.
Perform the steps below to define the fields of the table. The insertion point should be in the "Field Name" column.
Type
Marina Number
 (which is the name of the first field)
... in the "Field Name" column ... and then press the Tab key.
The words Marina Number display in the "Field Name" column ... and the insertion point advances to the "Data Type" column
... indicating that you can enter the data type. The word, Text, one of the possible data types, currently displays.
The arrow in the "Data Type" column indicates that a list of data types is available by clicking the arrow. Because Text is the correct data type, press the Tab key to move the insertion point to the Description column.
Type as the description.
Marina Number (Primary Key)
Now locate the "Primary Key" button on the database window toolbar, and point to it. A "Screen Tip", which is a description of the button, displays partially obscuring the description of the first field.
Click the "Primary Key" button to make the "Marina Number" the primary key. The "Marina Number" field is now the primary key as indicated by the key symbol that displays in the row selector.
Now press the F6 key to move the insertion point to the "Field Size" text box. The current entry in the "Field Size" textbox (50) is selected.
50 is the "Field Size" property. We need to make it 4.
Type 4 as the size of the "Marina Number" field. Press the F6 key to return to the Description column for the "Marina Number" field
... then press the tab key to move to the "Field Name" column in the second row. Field properties for the first field no longer display and the row selector moves to the second row just below the field name "Marina Number".

Correcting Errors in the Structure

When creating a table, check the entries carefully to ensure they are correct. If you make a mistake and discover it before you press the Tab key, you can correct the error by repeatedly pressing the Backspace key until the incorrect characters are removed. Then, type the correct characters. If you do not discover a mistake until later, you can click the entry, type the correct value, and then press the Enter key.

If you accidentally add an extra field to the structure, select the field by clicking the row selector (the leftmost column on the row that contains the field to be deleted). Once you have selected the field, press the Delete key. This will remove the field from the structure.

If you forget a field, select the field that will follow the field you wish to add by clicking the row selector and then press the Insert key. The remaining fields move down one row, making room for the missing field. Make the entries for the missing field in the usual manner.

If you made the wrong field a primary key field, click the correct primary key entry for the field, and then click the "Primary Key" button on the database window toolbar.

As an alternative to these steps, you may want to start over. To do so, click the "Close" button for the "Table1: Table" window and then click No. The original desktop displays and you can repeat the process you used earlier.

Use the techniques illustrated above to make the entries from the "Marina" table structure shown below ... up to and including the name of the Warranty field (which is the seventh field). 
Marina Number      Text         4    Marina Number (Primary Key)
Name               Text        20    Marina Name 
Address            Text        15    Street Address 
City               Text        15    City 
State              Text         2    State (Two-Character Abbreviation) 
Zip Code           Text         5    Zip Code (Five-Character Version)
Warranty
The additional fields are entered. A list of available data types displays in the "Data Type" column for the Warranty field.
Click the "Data Type" column arrow and then point to the Currency data type. Click "Currency" and then press the Tab key.
Make the remaining entries from the Marina table structure as detailed below. The fields should now be all entered, and the Marina table structure should now be complete. 
Marina Number      Text         4    Marina Number (Primary Key)
Name               Text        20    Marina Name 
Address            Text        15    Street Address 
City               Text        15    City 
State              Text         2    State (Two-Character Abbreviation) 
Zip Code           Text         5    Zip Code (Five-Character Version)
Warranty           Currency          Current Warranty Amount 
Non-warranty       Currency          Current Non-warranty Amount 
Tech Number        Text         2    Number of Marina's Technician
The final step is to save the table within the database. At this time you should also give the table a name.
To save the table, complete the following steps. Locate the Save button on the database window toolbar (it has a floppy disk icon on it). Click the Save button.
The "Save As" dialog box displays. Type Marina as the name of the table in the "Table Name: " text box,
... and then point to the OK button. The name of the table (Marina) displays in the "Table Name:" text box.
Click the OK button. The table is saved on the floppy disk in drive A:
The name of the table is now Marina as indicated on the title bar. Then point to the "Close Window" button for the "Marina: Table" window.
Be sure not to click the Close button on the Microsoft Access title bar, because this would close Microsoft Access). The "Marina: Table" window no longer displays. Now that you have created the table by building its structure and saving the table you're ready to add records to it.
Adding records to a table To open the Marina table and then add records to it, use the following steps.
Right-click Marina in the "Bavant Marine Services: Database" window (where the newly created table appears listed)
... and after the shortcut menu for the Marina table displays, point to Open on the shortcut menu. At this time "Bavant Marine Services: Database" window is maximized because the previous window (the "Marina: Table" window) was maximized.
If you wanted to restore the database window to its original size, you would click the "Restore Window" button. Now click Open on the shortcut menu.
The "Marina: Table" window displays. The window contains the Datasheet view for the Marina
The record selector is positioned on the first record. The record selector is the small box or bar to the left of the record.
The status bar at the bottom of the window also indicates that the record selector is positioned on record 1. Right above the status bar, near the lower left corner of the screen you will find the navigation buttons that we'll be using shortly.
Please locate the following: ... and describe their functionality.
  1. the field names in the datasheet view
  2. the space (row) for the first record
  3. the record selector
  4. the message on the status bar
  5. the navigation buttons
If your window is not already maximized, click the Maximize button to maximize the window containing the table. Type AD57 as the first marina number (as detailed below).
You often add records in phases. You may, for example, not have enough time to add all records in one session. To illustrate this process, this project begins by adding the first two records in the Marina table:

AD57 Alan's Docks 314 Central Burton MI 49611 $1,248.00 $597.75 23
AN75 Afton's Marina 21 West 8th Glenview MI 48121 $1,906.50 $831.25 36

The remaining records are added later. Be sure you type the letters in uppercase, because that is the way they are to be entered in the database.

The marina number should now be entered, with the insertion point still in the "Marina Number" field. Press the Tab key to complete the entry for the "Marina Number" field.
Then type the following entries, ... pressing the Tab key after each one.
The Name, Address, City, State and "Zip Code" fields are entered. Type 1248 as the warranty amount and then press the Tab key.
You do not need to type dollar signs or commas. In addition, because the digits to the right of the decimal point are both zeros, we do not need to type the decimal point. Type 597.75 as the non-warranty amount and then press the Tab key. Type 23 as the tech number to complete this record.
The fields may shift to the left if your screen is not wide enough. The Warranty and Non-warranty values display with dollar signs and decimal points.
The insertion point is positioned in the "Tech Number" field. Now press the Tab key. The fields will probably shift back to the right on your screen.
The first record has now been entered, and saved. The insertion point moves to the marina number on the second row, and the second record can now be entered.
Record 2 is the current record as indicated by the record selector between the navigation buttons at the bottom (located in the lower left corner, just above the status bar). Use the techniques illustrated above to add the data for the second row to the table.
The second record is added. The insertion point moves to the marina number on the third row. When you're done, you should have two records in the Marina table.
Closing a table and database. Quitting Access It is a good idea to close a table as soon as you have finished working with it.
This keeps the screen from getting cluttered and prevents you from making accidental changes to the data in the table. Perform the following steps to close the table and the database and quit Access:
Click the "Close Window" button for the "Marina: Table" window. The datasheet for the Marina table no longer displays.
Click the "Close Window" button for the "Bavant Marine Services: Database" window. The "Bavant Marine Services: Database" window no longer displays.
Click the Close button for the Microsoft Access window. The Microsoft Access window no longer displays.
Now you have a database, and a table with data inside it. To work with any of the tables, reports, or forms in a database, the database must be open.
Opening a database To open a database from the Windows desktop, click Start, Programs, Databases, and then choose "Microsoft Access".
Choose "Open an existing file". If not already selected, click the Bavant Marine Services database name (from your floppy).
The "Bavant Marine Services" database name is selected. Then point to the OK button, and click on it.
The database opens and the "Bavant Marine Services: Database" window displays. You're now ready to add additional records to its Marina table.
Adding additional records You can add records to a table that already contains data using a process almost identical to that used to add records to an empty table.
The only difference is that you place the insertion point after the last data record before you enter the additional data. To do so, use the navigation buttons found near the lower left corner of the screen.
Locate the following navigation buttons ... in the datasheet view:
  1. First Record button
  2. Previous Record button
  3. Next Record button
  4. Last Record button
  5. New Record button
The last one moves the cursor to the end of the table to a position for entering a new record.
Now you're ready to add to the remaining records to the Marina table. The records that are still to be added are listed below:

BL72 Brite's Landing 281 Robin Burton MI 49611 $217.00 $0.00 36
EL25 Elend Marina 462 River Torino MI 48268 $413.50 $678.75 49
FB96 Fenton's Boats 36 Bayview Cavela MI 47926 $923.20 $657.50 23
FM22 Fedder Marina 283 Waterfront Burton MI 49611 $432.00 $0.00 36
JB92 JT Boat Club 28 Causeway Torino MI 48268 $0.00 $0.00 36
NW72 Nelson's Wharf 27 Lake Masondale MI 49832 $608.50 $520.00 23
SM72 Solton's Marine 867 Bay Ridge Glenview MI 48121 $462.50 $295.00 49
TR72 The Reef 92 East Bay Woodview MI 47212 $219.00 $0.00 36

Complete the following steps to add to the remaining records to the Marina table. Right-click Marina in the "Bavant Marine Services: Database" window and then click Open on the shortcut menu.

The datasheet displays. Two records are currently in the table. When the Marina table displays, maximize the window by clicking the Maximize button.
Then point to the "New Record" button ... which is the rightmost navigation button.
Click the "New Record" button. Access places the insertion point in position to enter a new record.
Add the remaining records listed above to the table, using the same techniques you used to enter the first two records. All ten records are entered.
Then point to the "Close Window" button. The window containing the table closes.

Correcting errors in the data

Check your entries carefully to ensure they are correct. If you make a mistake and discover it before you press the Tab key, correct it by pressing the Backspace key until the incorrect characters are removed and then typing the correct characters.

If you discover an incorrect entry later, correct the error by clicking the incorrect entry and then making the appropriate correction. If the record you must correct is not on the screen, use the navigation buttons (Next Record, Previous Record, and so on) to move to it. If the field you want to correct is not visible on the screen, use the horizontal scroll bar along the bottom of the screen to shift all the fields until the one you want displays. Then make the correction.

If you add an extra record accidentally, select the record by clicking the record selector that immediately precedes the record. Then press the Delete key. This will remove the record from the table. If you forget a record, add it using the same procedure as for all the other records. Access will place it in the correct location in the table automatically.

If you cannot determine how to correct the data, you are, in effect, stuck on the record. Access neither allows you to move to any other record until you have made the correction, nor allows you to close the table. If you encounter this situation, simply press the Esc key. Pressing the Esc key will remove from the screen the record you are trying to add. You then can move to any other record, close the table, or take any other action you desire.

When working with a database, you often will need to print a copy of the table contents. Because the Marina table is wider substantially than the screen, it will also be wider than the normal printed page in portrait orientation.

Portrait orientation means the printout is across the width of the page. Thus, to print the wide database table, you should landscape orientation.
Landscape orientation means that that the printout is across the length of the page. A convenient way to change to landscape orientation is to preview what the printed copy will look like by using "Print Preview".
This allows you to determine whether landscape orientation is necessary and, if it is, to change easily the orientation to landscape. In addition you can also use "Print Preview" to determine whether any adjustments are necessary to the page margins.
Previewing and printing the contents of a table Perform the following steps to use "Print Preview" to preview and print the Marina table.
Right click Marina. The shortcut menu for the Marina table displays.
Point to "Print Preview" on the shortcut menu and click on it. The preview of the report displays.
Point anywhere in the upper right portion of the report. The mouse has a magnifying-glass pointer.
Click the magnifying-glass mouse-pointer somewhere in the upper right corner of the report. The portion surrounding the mouse pointer is magnified.
Not all fields display. The last field that displays is the "Zip Code" field. The Warranty, Non-warranty, and "Tech Number" fields do not display.
To display the additional fields, you will need to switch to landscape orientation. Click File on the menu bar, and then point to "Page Setup".
You might have to wait a few seconds for the entire menu to display. The File menu displays.
Click "Page Setup". The "Page Setup" dialog box displays.
Locate the Page tab and point the mouse to it. Click the Page tab.
The Page sheet displays. The Portrait option button currently is selected.
Option button refers to the round button that indicates choices in a dialog box. When the corresponding option is selected, the button contains within it a solid circle.
Clicking an option button selects it, ... and deselects all others.
Click Landscape and then click the OK button. Click the mouse pointer anywhere within the report to view the entire report.
The orientation is changed to landscape as shown by the report that displays on the screen. The characters in the report are so small that it is difficult to determine whether all fields currently display.
To zoom in on a portion of the report, click the desired portion of the report. Click the magnifying-glass mouse-pointer somewhere in the upper right corner of the screen.
The portion surrounding the mouse pointer is magnified. The last field that displays is the "Tech Number" field, so all fields currently display.
If they did not, you could decrease the left and right margins; that is, the amount of space left by Access on the left and right edges of the report.
Locate the Print button. Click the Print button to print the report.
Locate the Close button. Click the Close button when the report has been printed to close the "Print Preview" window.
The "Print Preview" window no longer displays. You can now create the second table, Technician.
Creating additional tables A database typically consists of more than one table.
The sample database contains two: the Marina table and the Technician table. You need to repeat the process of creating a table and adding records for each table in the database.
In the sample database, you need to create and add records to the Technician table. The structure and data are given below:
First the structure: 
Tech Number   Text         2     Technician Numner (Primary Key) 
Last Name     Text        10     Last Name of Technician 
First Name    Text         8     First Name of Technician 
Address       Text        15     Street Address 
City          Text        15     City 
State         Text         2     State (Two-Character Abbreviation) 
Zip Code      Text         5     Zip Code (Five-Character Version)
Hourly Rate   Currency           Hourly Rate of Technician 
YTD Earnings  Currency           YTD Earnings of Technician

Then the data:

23 Anderson Trista 283 Belton Port Anton MI 47989 $24.00 $17,862.00
36 Nichols Ashton 978 Richmond Hewitt MI 47618 $21.00 $19,560.00
49 Gomez Teresa 2855 Parry Ashley MI 47711 $22.00 $21,211.50

The steps to create the table follow. Make sure the Bavant Marine Services database is open.

Right click "Create table in Design view" and then click Open on the shortcut menu. Enter the data for the fields for the Technician table form.
Be sure to click the "Primary Key" button when you enter the "Tech Number" field. Locate the Save button on the database window toolbar after you have entered all the fields.
The entries display. Click the Save button.
Type Technician as the name of the table. Then click OK.
Click the "Close Window" button. The table is saved in the Bavant Marine Services database.
The "Technician: Table" window no longer displays. Adding records to the additional table
Now that you have created the Technician table, use the steps on the next page to add records to it. Right-click on the newly created Technician table.
The shortcut menu for the Technician table displays. Point to Open on the shortcut menu.
Click Open on the shortcut menu. The datasheet displays with three records entered.
Enter the Technician data from above into the Technician table. Click the "Close Window" button for the "Technician: Table" window.
Access closes the table and removes the datasheet from the screen. You are now ready to start assignment #1 and THQ1.
Last updated: August 28, 2000 by Adrian German for A114/I111