Sparks Fly
[Home] [PBS VideoDatabase] [Social Studies] [TSU] [UVA]

AppleWorks/ClarisWorks

Databases

Database is a file that houses multiple sets of information called records.  Each item in a record (piece of information) is called a field.  Most databases contain many records.  Database programs allow the user to manipulate the data.

Creating a Database

Open AppleWorks, choose New from the File menu, Select Database from the Open File menu.

You can select the Use Assistant or Stationary button and select from the database templates provided by AppleWorks or you can select New Document and you create it all.

Assistant

The Assistant is an automated wizard that guides you through the steps of creating a database for a rolodex type address book.  As you answer the questions you create the database you desire.

Entering Data

The Assistant provides sample data already entered.  With the address list open, select Delete Record from the Edit menu.  Delete the sample data and begin to add your own.  To create new records select New Record from the Edit menu or (Control + R in Windows) (Open Apple+R Mac).  An empty address list record will appear.  Click in the blank boxes and begin typing.  Move using Tabor the mouse to move between fields.  The enter key will create another line within the field where you are typing.

Moving Around

Move around using either the Rolodex, Record number field, or GoToRecord command. Rolodex Select the top page of the Rolodex to move back one and the bottom page to move forward one Record number field Click the record number field at the bottom of the Rolodex and type the number to which you wish to go Go To Record command Choose GoToRecord from the Organize menu (Control+G Windows) Open Apple+ G).  In the dialog box type the number of the record to which you wish to go and press OK.

Changing the Layout

If you want to change something in the layout, i.e., the colors, type size, or eliminate fields or add fields, here is how to do it.

• Changing area colors
  • The template has several areas of color.  These are simply large square boxes that contain color.  (The original layout was created with the drawing tools).
  • Select Layout from the Layout menu (Shift+Control+L Windows) (Shift+Open Apple+L)--the layout appears and you cannot see the data.
  • Click on the object you want to change--the handles appear around the object.
  • Click the color palette on the left side of the layout under the bucket.
  • Select the color you wish and the change occurs.
• Changing type styles
  • There are two kinds of type in this template--text that goes in the records and text that labels the layout of the template.  You can change both.
  • Click the field of the typed text you want to change
  • Select Font, Size, Style, or Text Color from the Format menu and make the changes you want.  Continue to change as you desire
• Changing Field Labels
  • Change or erase field labels.  To erase, chick on it and press delete or clear key
  • To change text in a field label, select the "A" tool (text tool) from the tool palette
  • Highlight the text you want to change
  • Type the new text
• Locking and unlocking objects
  • Objects in a database layout may be locked.  When you click on them the handles are gray instead of black.  You can't do anything until you unlock the object.
  • Click on the object, Choose Unlock from the Arrange menu (Shift+Control+H Windows) (Shift +Open Apple+H)
  • To lock it back and prevent accidents, click on the object, choose Lock under the Arrange menu (Control+H Windows) (Open Apple+H)
• Removing a field from a layout
  • Removing a field does NOT remove the field's data from the database.  The data still exists but you don't have to use it in every layout for the database.
  • To remove a field, click on it, unlock it if it is locked, then press the delete or clear key.

Fields

Removing a field AND its data

• Sometimes you may want to eliminate a field and it's data from a database
• Select Define Fields from the Layout menu (Shift+Control+D Windows) (Shift+Open Apple+D) the Define Fields dialog box appears
• Scroll down until you find the fields you wish to remove, click on it then click the delete button
• A confirmation box will appear, click OK then Done and the field and it's data is gone.

Creating New Fields

• To add a field, with the database open, select Layout from the Layout menu and the Layout appears
• Select Define Fields from the Layout menu (Shift+Control+D Windows) (Shift+ Open Apple + D) the Define Fields dialog box appears
• Type a name for the new field in the Field Name box and click the Field Type and select the type you want

Field Types

Text, Number, Date, Time and Name Fields

• Use the Cannot be Empty options if you wish a field the always contain information
• Use Must Be Unique if no field can contain the same information
• Use Automatically Enter option to provide an default value to a field.
• Click options in the Database Define Field dialog box
• Click the choice you want then OK and Done

Popup, Radio Buttons, or value List fields

• You create menus that can be used to make data entry choices within the document
• Select Pop-up, Radio button or Value List from the Field Type list in the Define Database Fields dialog box
• Click the Item Label field box.
• Enter the name of the first item in the Label field
• Click the create button
• Click the Label field again and enter the second item, then click Create.  Continue until all options are entered then
• Click OK

Checkbox or serial Number fields

• Checkbox fields allow you to create a single checkbox which can then be checked or not, i.e., member--non-member
• Serial numbers are automatically entered into the Serial field with subsequent numbers increasing by the increment that you choose

Record Info files allow you to enter:

• Date Created
• Time Created
• Name of Creator
• Date Last Modified
• Time Last Modified
• Name of Modifier

You many enter only one of the choices in the record Info field.  If you need more info, make more fields

Calculation and Summary Fields

• Select calculation or summary from the Field type pop-up menu in the Define Database Fields pop-up box.  The Enter Formula For dialog box appears
• Select the formula from the list
• Insert the name of fields in the database into the formulas
• Click the Format Result As pop-up menu and choose either Text, Number, Date, or Time
• Click OK

A spreadsheet function will be performed.  Summary fields allow you to add up all the records for one field in the database.  Calculation fields add up information for one record.

Changing field Types

• This is possible but should be done with care.  sometimes it results in erasing all information in the field
• Select Field Name you want to change from the Define Database field dialog box
• Select the field type you want to use by scrolling down
• Click the Modify button (a warning dialog box appears to tell you the you may permanently erase your data)
• If certain, proceed and click OK then Done (there is no going back)

Layouts

Tab order--determines which field the cursor moves to when you press the tab key.  You can change the order at any time.

• Choose Tab Order on the Layout menu (dialog box appears)
• Click any fields that are in the incorrect order then click the move button
• In the Tab Order list on the right select the field just below where you want your field to appear
• Select the field you want to insert the click Move and the field is rearranged.  When finished click OK

Positioning Information

• You may move fields and labels around and you should move both at the same time
• Click Layout under the Layout menu
• Click the field you want to move
• Hold down the Shift key
• Click the field label associated with the field you want to move
• Drag the field and label to the new position

Creating Sliding Fields

Sliding fields don't move on the database screen but do move when printed.  They slide up or sideways to fill gaps left by fields that contain no information.

• In Layout view, press the Shift key an click each field in a row or column that you want to slide if necessary.
• Choose Edit Layout from the Layout menu
• In the dialog box, click the name of the layout and then the Modify button
• In the dialog box click the checkboxes labeled "Slide objects left" to close horizontal gaps and "Slide objects up" to fill vertical gaps
• Click OK twice to return to the database

Data Entry Tricks

Name fields sort text according to the last word in the field.  That's great except for names like Ponce de Leon or Mary Smith Jones.  To avoid these problems:

• Make sure the field type is Name Field
• For last names with more than one word enter a hard space between each work instead of the normal space bar (Control+Space Windows) (Option-Space Mac)

Duplicating Information

If entering a series of records containing duplicate info, duplicate the record so the information is carried over.

• Click anywhere in a record that contains information you want to duplicate
• Select the Duplicate Record item on the Edit menu (Control+D Windows) (Open Apple + D)
• Triple click any field you wish to change and type in the new information

Special Characters

Certain key combinations will result in special results in the database in Windows    

Database Views--found in Layout menu

• Browse--view used to enter data
  • Select Browse from the Layout menu
  • To see one record at a time make sure the Show Multiple from the Layout menu is not check marked
• Find--view used to look for information
  • Match or not match criteria
  • Select find from the Layout menu (Shift+Control+F Windows) (Shift+ Open Apple+ F)
  • Type the info you want to find in the proper field
  • Click the find button or press the Enter key
• Layout--view used to create a database
• List--view that resembles a spreadsheet
  • Choose List from the Layout menu (Shift+Control+L Windows) (Shift + Open Apple + L)
  • Use the tab key to move right or use Shift+tab to move left between fields
  • Use the up or down arrows to move between rows
  • To drag columns left or right
    • move the cursor close but not on top of the line between column headings
    • double-sided arrow appears
    • Drag columns to the desired location

Using Find

Locate records using match or do not match criteria

• Select Find from Layout menu (Shift+Control+F Windows) (Shift+ Open Apple+ F) Find screen appears--looks like blank record but has different toolbox on left
• Type the info you want to find in the proper field.
• Click the Find button on the left side of screen or press Enter.  All information that matches will appear on the screen.

Tips

• To tell the number of records found, look at the Rolodex, the first number tells you how many records were found, the number in parenthesis tells you how many records in the database.
• Find feature is not case sensitive
• You can find using partial information.  You can type part of a name and it will locate all the records with that letter combo.

Find and Hide Selected

Sometimes it is best to find the information you know you don't want.  If you wanted a list of Freshmen, Sophomores, and Juniors, it would be easiest to look for all the Seniors, then hide the information, leaving what you were looking for--Freshmen, Sophomores, and Juniors.  If you need to look at all the information the was not found use the Hide Selected from the Organize menu.

To use Hide Selected:

• Perform a Find operation
• Choose Select All form the Edit menu
• Choose Hide Selected from the Organize menu (Control+Hyphen Windows) (Open Apple-Hyphen)
• To reveal the hidden records:
  • Choose Show All Records from the Organize menu (Shift+Control+A) (Shift+ Open Apple+ A)

Using Omit

Another way to locate records that don't match criteria is to use the Omit checkbox in the Find Screen's toolbox.

Deleting a group of Found records

• Once you find the data you may delete it
• Find it
• Choose Select All (Control+A Windows) (Open Apple + A)
• Select Cut on the Edit menu

Finding within a Found Set

• If you perform a Find and you want to search that set of data
• Perform a second Find
• When the dialog box appears click the Visible radio button on the left side of the page
• Fill in the criteria
• Click on Find button

Two-part Find

• Suppose you want to find all the people who live in Chesapeake then also the people who live in Virginia Beach
• Fill out  the first set of information in the Find layout
• Select New Request under Edit and fill in the blank Find layout with the new request.
• Press the Find button

Saving a Search

• You can save the criteria in the Find form
• After performing a Find do not press the Find button instead click the spyglass on the left side of the page.  The Search pop-up sub-menu appears
• Select New Search
• Type in the name for the Search
• Enter the criteria and click the Store button

Using the Saved Search

• To use a saved search select the Search pop-up from the left side of the screen
• Select the named search and it is performed automatically.

Sorting Data

• Arrange the information in alphabetical or numerical order
• Choose Sort Records from the Organize menu (Control+J Windows) (Open Apple + J)
• Fields List on left click once on the field you want to sort
• Click the move button in the center of the Sort Records dialog box.  The field appears in the list on the right side of the dialog box
• Click Ascending or Descending
• Click OK and the sort is performed

Multiple Sorts

• Sort on more than one field
• Click the firs field in the Sort Records dialog box and do the above steps--move button and order
• Select the next field and do the same, continue until you have all the fields you want to sort by selected
• Click OK and the sort is performed

Changing the Sort

• Select Sort Records from Organize menu
• Click any field from the list on the right then the clear button.  The field now appears on the left side
• Make you new selection the OK to run the sort

Creating a New Layout

• Select New Layout from the Layout menu--dialog box appears
• Type name for the new layout
• Select the type of layout you want to create from the radio button--five options
  • Standard--blank report containing all fields
  • Duplicate--duplicate of previous report you created
  • Blank--blank report with no fields
  • Columnar--report that displays fields in columns
  • Labels--creates labels using Avery sizes
• Click OK
• Once you have the new layout you can add fields and labels, change fonts, type size, text color, tab order of fields, add graphics, create summary fields.

Parts of a New Layout

• Layout parts control how the information is calculated and its appearance on he screen or in print
• Parts include:
  • Header--top of page
  • Leading grand summary--report sections
  • Sub summary when sorted by--adds up subtotals
  • Trailing grand summary--adds up calculation fields for individual items
  • Footer--bottom of the page info

Creating Parts

• Layout view, choose Insert Part from the Layout menu--dialog box appears
• Select the radio button that describes the type of part you want to use
• Click OK
• To resize a part, select the solid line that defines the bottom of the part and that has the part label on the left side of the layout
• Drag up or down until the part is the size you want

Creating a Summary Report

• Add up data
• Use number, calculation, and summary fields and places summary information in a Grand summary section
• Create new report with fields that need to be input
• Create a calculation field
• Create a summary field that adds up all of the calculation fields
• Create a Grand Summary at the bottom of the report

Printing a Database

• You can print the entire database, a few records, or just one record
• Choose Print from the File menu
• Click either the Current Record (record that is selected) or Visible Records (all records that are visible Find)
• Click Print

Importing and Exporting Data

Export

• Export data--use Save As from the File menu and save the work as a Comma Values DOS file (Windows)--the file will then transfer to other database programs
• If you export to a word processing program the columns usually will not line up.  You need to highlight and insert Tabs to have this import work properly

Import

• To import from another program, check the exporting procedures of that program.  If no directions try save as Comma Values DOS or Comma Separated Values file
• In AppleWorks first create a new database file that contains the fields and field names.  Create as many fields as you need.
• To bring in a Comma Values (CSV file) in:
  • Choose insert from the File menu--Open dialog box appears
  • Select the file you want to use and it opens in the AppleWorks Database.

Return to Database Handouts