Adding a summary to a database by Cindy O'Hora

Many people know they can use database to make mailing labels and to sort data.
There is another even more powerful feature that is often missed. That feature is using formulas to summarize data.

In the first database lesson, I looked at creating a database that organized all the computer CDs in our school.
I told you that I had planned to use the number of records to keep track of how many CDs we had. I had not
taken into account that we had multiple copies of many CDs to facilitate classroom use. Some of you would
suggest that we make one entry for each individual cd. This would mean that some titles would be entered 10 times.

Using a Summary field and a formula, I can tract the total collection by the number of titles I entered in the "Number" field.

To accomplish this you must first have a field for calculating the data. Let's use my CD database problem.

Add a Summary field

1. Add a field by going Layout...Define fields. Use the Field Type pull down menu to select Summary. I want to add up the amount entered into the number field across all my records. So I choose Summary. (I use calculation fields to calculate data within a single record.) I call my field Total CDs. When you click Create, a formula box will open.

2. In the Formula box, create the formula that will do the appropriate calculation. In my case, I entered =SUM('number'). 'number' is the name of the field I want to total.

Important: Field names entered in a formula must be enclosed by single quotes.

A wise formula creator uses the boxes above the Formula box to create the formula. I clicked on the equal sign (operator box), then on the SUM formula (found in the Function box), and finally I highlighted the number1, number2 within the SUM formula and clicked on my field name number to be totaled in the Fields box. I did this to avoid entering a bad formula caused by typing errors. For example: if you forget a single quote, you'll get a bad formula message and you might fail to see that missing single quote.

Note: ClarisWorks dropped the equal sign from my formula above when I saved.

 

Getting the result to show in the database.

1. Go Layout...Layout.

2. Go Layout... Insert part. Choose Trailing Grand Summary. Trailing will show the Summary field at the end of my database records. 3. Go Layout... Insert field. You will see a box presenting the summary fields you have created in your database. Choose the field that you created to report the calculation. When it appears in the database drag it and its title into the area between the Body and the Grand Summary area. 4. Return to Browse by going Layout...Browse. Finally, go View...Page View. Go to the end of your database and you should see your total(s) reported there.

Want to track the value of your book/music/dvd collection? Here is a great way!

1. Add a field to the database (if you don't already have it) of the cost of each book. I'll call it price.

2. Go Layout...Layout. 3. Double click on the blank price field. You should see a Number format dialog box. 4. Choose Currency. Now just enter a number with a decimal point. AppleWorks will put the $ sign. (Makes you want to smile doesn't it?) 5. Enter the cost of each item. (This is a great summer project.)

6. Define a new summary field. I'll call it summary cost. The formula is SUM('price'). Again, double click on the field and make it a currency result.

7. Finally, add the Trailing Grand Summary, insert the summary cost field into the database, as above.

Drag it into the Grand Summary area and return to browse.

Go View...Page view. Voilà! Whew, I bet you had no idea it was that much.

Private summaries:

Sometimes you don't want the whole world to see the total value of your collection. For example: My friends have a Hummel collection. They have many pieces and it has considerable value. They sometimes print the list to take to auctions and meetings. They would prefer to keep the total value to themselves.

To guard their privacy, we'll make a new layout called Summary. (Aren't my titles creative?) Then we'll add the Grand Summary and totaling fields only to that layout of the database. Their usual view of their database does not include their total values.

What is another time I would use a Summary field?

To track the total amount of money earned, where each record is a single job with a calculated charge.

To total the different meals selected by guests at a wedding or conference.

To track the number of students in a classroom, grade, or school

To track the total playing time of a cd where I have entered the time for each track/song

To identify the winner of favorite book or author when using a database to run an election or favorite book survey.

To identify the amount of fees collected in a tournament or science fair.

Database tips index