Project 1: Intro
Flat File Databases

A database program that uses only one table for the fields is a flat file database, also called just a flat database. In the previous lesson, you saw how a spreadsheet can work as this type of database, IF the fields for one record are put in a single row. A spreadsheet is not the best tool for databases with a large number of records or when you want to do queries and reports.

A database program is designed to make it easy to:

• Enter and view data
• Keep data consistent
• Use queries to group, sort, and filter data
• Create reports

Most databases that have been around a while have evolved into relational databases but there are still some old-fashioned flat file databases out there.

Microsoft Works includes a simple database feature that works as a flat file database. We won't hang around long in Works. It is a very simple database and is rather awkward to use.

Example of datasheet view in Microsoft Works

Microsoft Works:  A Simple Flat File Database

The illustration above is of datasheet view,  called a list in MS Works, where the records are in rows and columns. It looks very much like a spreadsheet. Microsoft Works is very limited in what it allows you to do with a database.

In many ways Excel actually is more user-friendly in its database features than Microsoft Works, but not in everything.

In Excel there was data entry form, but you could not modify the form.  Even in a basic database like Microsoft Works, you can rearrange and format the form view to suit yourself. Color is nice, but even nicer is being able to see all the fields in the window at once.
 

Reports in Microsoft Works can give you a basic print-out. Some of the fields in the Star Wars Collectibles database need more room than the default formatting gives.
The Report Design View lets you change a lot about the formatting of the report.

Clearly the report is based on a spreadsheet layout.

You can move fields to a second row for the report and group the records.

The example at the right groups the records by YearMade and puts a dividing line between the groups. It also formats the fields to wrap the text.

The text in a field will be cut off if you did not leave enough space in the report design view.

Do It!

If you have Microsoft Works installed on your computer, you can open the sample database and play. Just click the image below.

Star Wars Collection as a Microsoft Works database

Experiment! Play around with it.
Try the following activities in the database:

• Scroll to view all of the fields.
   
• Switch views: from form view to list view (called datasheet view in other programs).
  [Use the View menu or the View buttons ]
   
• Sort using various columns: menu Record | Sort... and select up to three columns
  [Excel was friendlier for sorting!]
   
• Return to Original order: Sort Ascending on first column only, ItemID.
   
• Create a filter to show only items made in 1999: menu Tools | Filter...  Name the filter YearMade 1999.
  [Row numbers show that some rows are hidden. This was much easier in Excel.]
   
• Add a filter to the previous filter to show items that were a Gift.
  [There are now 5 items.]
   
• Remove all the filters: menu Record | Show | Show All.
   
• Add new record with form: From the menu select View | Form....  Go to the last record by clicking the navigation button . Enter some data for a new record.
   
• Change to design view: Click the Form Design button on the toolbar.
   
• Make changes to the form: Change the layout or formatting of the form using the toolbar and menu dialogs and by dragging the fields around on the form.
   
• Display a saved report: View | Report. With the only saved report, StarWarsCltn, selected, click Preview. View all pages. Zoom In and Zoom Out. Some fields are on a separate page. Close the preview by clicking the Cancel button. The window shows the Report Design view. Unexpected!
   
• Make changes to the report in design view: Change the layout or formatting of the report, using the toolbar and menu dialogs.
   
• Print the modified report: File | Print...
   
• Close the window without saving your changes.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012