Winter 1998 Computers and Society
GST 2710, Section 90571
AGS 3360, Section 95198
Last updated: 4/14/98
Link back to course Welcome...

In-class Database Worksheet

1. List (table)
   1. Start a new Works database. Use the View / List menu item to view the database as a grid of cells in rows and columns. This is the basic structure of a database; everything else, such as a form or form view, is an add-on.
   2. Entering data in cells. For now, leave the right of the table blank, as shown.

      Henry	Jones	$9.25	80
      Marvin	Wolinsky	$11.65	40
      Edith	Hope	$13.51	80
      Janet	Earl	$12.38	80
      William	Young	$9.75	62
      Ernestine	Richards	$14.92	80

   3. Naming fields
      1. Select whoe column or any cell in the column
      2. Choose menu item Edit / Filed Name
      3. Type field name
      4. Click OK

         First

         Last

         PayRate

         Hours

         GrossPay

         Benefits

         Taxes

         NetPay

         Cost

   4. Entering formulae - the same formula will be used in all records, for the field it is entered in
      1. For the field on the left side of the equal sign in the formula, select the whole column or any cell in the column. for example, in listing formula a below as GrossPay = PayRate * Hours, select the field GrossPay.
      2. Type "=" (do not inlcude the quotation marks). This tells Works that what follows is a formula
      3. Arrow over to or click on the first field in the formula (same record)
      4. Type the mathematical operator (* for multiplication, / for divide, + and -)
      5. Type the number or select the second field, depending on the formula
      6. Tap <Return> or <Enter>. NOTICE that the formula is copied to all records
      7. Enter the following formulae
         1. GrossPay = PayRate * Hours
         2. Benefits = GrossPay * .2
         3. Taxes = GrossPay * .22
         4. NetPay = GrossPay - Taxes
         5. Cost = GrossPay + Benefits
         6. Full = First & " " & Last (creates a single field with the full name. The quotation marks enclose a space, the space between the first and last names).
   5. Formatting numbers in fields
      1. Select fields to be formatted
      2. Select menu item Format / Number...
      3. In "Number" dialog, select desired Format and any Options. Check that Sample is as desired, then click OK.
      4. Format the fields PayRate, GrossPay, Benefits, Taxes, NetPay and Cost as currency with 2 decimal places
   6. Save this database as "PAYROLL.WDB", in a location where you can find it again.
2. Sorting
   1. Many database functions, especially for smaller databases, can be performed by sorting the data on various fields. In Works, sorting (Sort Records...) is under the Tools menu item. Sort this database as follows:
      1. Sort by Last
      2. Sort by Hours
      3. Sort by Hours, then by Last as the second field
3. Forms
   1. In the real world, forms are mainly used for entering and reviewing data. If you work with a database, you are probably used to "screens". These are forms. A form displays data for a single record, and optionally allows the data to be entered and re-entered. One important function of real-world forms is to validate data; that is, to check that data entry has a valid form, for example that a Zip code has five numeric characters, that a telephone number has an Area Code included, and so on. In Works, forms mainly position fields on the screen and format them, functions that are also performed by real-world forms. In the real world, there can be many forms for a single database, each looking at different fields. Database workers will be used to going between the screens to carry out a given change.
   2. Use the View / Form menu item to go to form view. Works allows only a single form per database. Note that form view uses the field to label the line, which underlines the field contents. Drag the fields around to different parts on the screen. Notice that the field name and field contents move together. Select a few fields and format them. Notice that you format the field name and the field contents separately.
   3. Notice the record control in the bottom left corner of the screen, showing the current record displayed on the screen. Click on the arrows to see what they do.
4. Report
   1. In real-world databases, there can be many reports for a single database. Works also allows several reports per database. Reports are used to display and summarize the records. No data entry is permitted in a report. For a payroll database, one report is the report that prints out the paychecks, one page (check) per record. Other reports will summarize the total payroll for the administration, summarize the taxes owed and perhaps even write the check for the taxes, etc.
   2. Use the Tools / Create New Report... menu item to create a new report. Select the fields that would be needed to print out a paycheck and include them in the report. NOTE: A check includes the amount both in numerical and word form. We do not have enough time to go through the manipulations to create the word form.
   3. Create a second report to show the total cost to the company for these employees.
5. Query
   1. A query is a question, and the same idea works with databases. "Who worked 80 hours, and what did they get paid?" To ask the database this question, set up a query.
      1. Choose the menu item Tools / Create New Query...
      2. Give the query a name, say "FullTimers"
      3. In the top "Choose a field to compare:" text box (A), select Hours.
      4. In the top "How to compare the field:" (B), select "is equal to"
      5. In the top "Value to compare the field to:" (C), type 80.
      6. Click "Apply Now". What has changed in the table?
      7. Remove the effect of the query by choosing the menu item View / Show All Records
   2. Make a second query to show everyone whose net pay is at least $600.