AppNote XE0186: Database Tips

The information in this article applies to:

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a
• Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0

SUMMARY

The application note XE0186, titled Database Tips, discusses how to maintain database information, sort a database, analyze database information, set ranges to extract data and summarize a database with a table.

MORE INFORMATION

The full text is contained in this article.

  Microsoft(R) Product Support Services Application Note (Text File)
                         XE0186: DATABASE TIPS

                                              Revision Date: 5/93

 --------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY    |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an            |
| Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY      |
| KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO    |
| THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A     |
| PARTICULAR PURPOSE. The user assumes the entire risk as to the     |
| accuracy and the use of this Application Note. This Application    |
| Note may be copied and distributed subject to the following        |
| conditions: 1) All text must be copied without modification and    |
| all pages must be included; 2) If software is included, all files  |
| on the disk(s) must be copied without modification [the MS-DOS(R)  |
| utility DISKCOPY is appropriate for this purpose]; 3) All          |
| components of this Application Note must be distributed together;  |
| and 4) This Application Note may not be distributed for profit.    |
|                                                                    |
| Copyright 1988-1993 Microsoft Corporation. All Rights Reserved.    |
| Microsoft and MS-DOS are registered trademarks and Windows         |
| is a trademark of Microsoft Corporation.                           |
 --------------------------------------------------------------------

                          GENERAL INFORMATION
                          ===================

                          SORTING A DATABASE
                          ==================

In Microsoft Excel versions 2.x, if you sort your database frequently, define a separate name (for example, Data_Sort) to refer to the records alone, omitting the field names. After you define Data_Sort, you can quickly select the records you want to sort by choosing Go To from the Formula menu and typing "DATA_SORT" (without the quotation marks). As you add new records to your database, you will need to redefine Data_Sort to include additional rows.

You can also use this method in Microsoft Excel versions 3.0 and 4.0. However, there is another method that is more efficient. When you define Data_Sort, type the following formula in the Refers To box in the Define Name dialog box:

   =OFFSET(Database,1,0,ROWS(Database)-1)

Sort Order

Microsoft Excel always places any blank cells at the bottom of a selection, regardless of whether Ascending or Descending sort order is selected. The sort order for text entries, from left to right and top to bottom, is as follows:

   Microsoft Excel versions 2.2, 3.0, and 4.0
   for the Macintosh and versions 2.x and 3.0 for Windows
   ------------------------------------------------------

   0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ' ( ) * + , - . / : ; < = >
   ? @ A B C D E F G H I J K L M N O P Q R S T W U V W X Y Z [ \ ] ^ _
   ` { | } ~


   Microsoft Excel version 4.0 for Windows:
   ----------------------------------------

   0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ' ( ) * + , - . / : ; < = >
   ? @ [ \ ] ^ _ ` { | } ~ A B C D E F G H I J K L M N O P Q R S T W U
   V W X Y Z

  NOTE: The sort order is not case sensitive. (For example, "A" and
  "a" are treated the same and will appear in the same order as they
  did in the original selection. If "a", "A", and "a" appear in three
  separate cells, choosing Sort will have no effect.)

Sorting on More Than Three Fields

To perform a sort on more than three fields (keys) at one time, you must do multiple sorts, working backwards from the least-significant sort key. For example, assume you want to sort the range A2:E25 with the data in column E sorted within the data in column D, which is sorted within the data in column C and so on, back to column A. To perform this type of sort, do the following:

1. Select cells A2:E25.

2. From the Data menu, choose Sort. For the 1st Key, enter $D$2, and for the 2nd Key, enter $E$2. Choose OK. The data in column E will now be sorted within the data in column D.

3. With the range still selected, from the Data menu, choose Sort. For the 1st Key, enter $A$2, enter $B$2 for the 2nd Key, and $C$2 for the 3rd Key. Choose OK.

                   MAINTAINING DATABASE INFORMATION
                   ================================

Inserting Records

If you are not using a data form (which automatically redefines the database range when a new record of information is entered), you should define the database to include a blank row at the bottom of your data. Then, before you add a new record to the database, insert a new row by selecting the blank row after your last record and choosing Insert from the Edit menu. Using this method, you will not have to redefine the database every time a record is added. Note that if you have data to the right or the left of your database range, this procedure may displace it. Alternatively, rather than select the entire row, you can limit your selection to only those cells below your last record. When you choose Insert from the Edit menu, select the Shift Cells Down option.

Deleting Records

To delete the records that match your criteria, choose Delete from the Data menu (there is no keyboard command equivalent for this command). Microsoft Excel will erase the entries in every field of each record that matches the criteria.

  Warning: Using the Delete command when your criteria range contains
  a totally blank row will erase the entire database because a blank
  criterion matches every record.


             ANALYZING AND REPORTING DATABASE INFORMATION
             ============================================

Setting Your Criteria Range

A valid criteria range must contain at least two cells (two rows by one column):

• If you are using comparison criteria, the top row will contain your database field labels. The field labels used in your criteria range must be identical to the labels in your database range. To ensure that these labels are identical, copy the desired field labels from your database range and paste them in your criteria range.
• If you are using computed criteria, the top row must contain a name other than a database field label.

Entering Multiple Criteria

You can specify "and" and "or" relationships based on where you position different criteria within a criteria range. You can also use the AND() and OR() functions for computed criteria.

Finding Records That Match Criteria X and Criteria Y

When two or more entries are on the same row of a criteria range, Microsoft Excel selects the records that meet all the criteria. To specify two criteria for the same field, duplicate the field label in the criteria range. For example, suppose you want to select those records with entries in the Income field that fall between $18,000 and $32,000. To do this, you must specify two criteria that relate to the Income field, one to look for data that is greater than $18,000 and the other to look for data that is less than $32,000. In this case, two criteria are placed under separate instances of the Income field heading. Since both entries are on the same row of the criteria range and both are beneath an Income field heading, Microsoft Excel combines them and finds only those records with an income that is both greater than $18,000 and less than $32,000.

In the following worksheet, the database is defined as A1:C5, the criteria range is defined as E1:H2, and the extract range is defined as E4:G4. When you choose Find from the Data menu, the records for Green and Simpson will be highlighted because each has an income greater than $18,000 but less than $32,000. Similarly, when you choose Extract from the Data menu, the records for Green and Simpson will be extracted to the extract range.

Alternatively, you can use a computed criterion to find and extract the records. In cell D1, type the name SALARY. In cell D2, enter the formula:

   =AND(B2>18000,B2<32000)

   A         B        C            D   E     F       G      H

1  Name      Income   Department       Name  Income  Income Department
2  Green     $23,000  Programming            >18000  <32000
3  Black     $8,000   Tech Support
4  Simpson   $25,000  Programming      Name  Income  Department

Finding Records That Match Criteria X or Criteria Y

When you want to find records that meet one of two or more criteria, place your criterion entries into separate rows and then include those rows in the criteria range. Microsoft Excel treats entries in separate rows as an "or" clause, finding or extracting records that match one condition or another.

Using the following sample database (the database is defined as A1:C5, the criteria range is defined as E1:G3, and the extract range is E4:G4), suppose you want to select those records where either the Income field is greater than $24,000 or the Department field is equal to Tech Support. The criteria range is set up such that you will find or extract the records Black, Simpson, and Thompson; the latter two have an income greater than $24,000 and Black's Department is Tech Support.

Alternatively, you can use a computed criterion to find and extract the records. In cell D1, enter the name INCDEPT. In cell D2, enter the formula:

   =OR(B2>24000,C2="Tech Support")

  A        B        C             D  E     F       G

1 Name     Income   Department       Name  Income  Department
2 Green    $23,000  Programming            >24000
3 Black    $8,000   Tech Support                   Tech Support
4 Simpson  $25,000  Programming      Name  Income  Department

Specifying Dates as Criteria

If you want to extract all records in a database that match a specific date, enter that date in the criteria range under the appropriate field label. If you want to extract all records that are earlier than or later than a specific date, or if you want to extract all records that fall within a specified date range, the method to use varies according to the version of Microsoft Excel you are using.

Microsoft Excel Version 4.0

In Microsoft Excel version 4.0, use the comparison operators such as the less than (<) and greater than (>) symbols, followed by the date. For example, the following criteria in cells F1:F2 will extract all records in a database with an entry in the Date field earlier than 3/1/93:

     F
   1 Date
   2 <3/1/93

Microsoft Excel Versions 3.0 and Earlier

In Microsoft Excel versions 3.0 and earlier, when you enter a date with a comparison operator, the date is evaluated as text rather than as its underlying serial value. As a result, an attempt to find or extract records that are greater than or less than a specified date will result in no records being found or extracted. To use a comparison operator with a date, the date must either be the serial value,

     F

a formula that evaluates to a serial value,

     F

or a cell reference that contains a date:

     F         G
   1 Date      3/1/93
   2 ="<"&G1

If you are using the 1900 date system (the default date system for Microsoft Excel for Windows), when you enter the formula and cell references, <34029 will be displayed in the cell. The formula, DATE(93,3,1) evaluates to 34029, as does the cell reference, G1. If you are using the 1904 date system (the default date system for Microsoft Excel for the Macintosh), the serial value for 3/1/93 is 32567. In each of these cases, the criteria range is defined as F1:F2.

Setting Your Extract Range

When you extract information from a database, your extract range must be separate from your database and criteria ranges. The field names in the extract range must be in a single row (as must the field names at the top of the database and criteria ranges). To ensure that field names are identical, use the Copy and Paste commands. Microsoft Excel will not recognize the extract range if a field name does not exactly match a field name in the database.

When you extract information from a database in Microsoft Excel versions 2.x, in your extract range, select the field names for the fields you want to extract, and choose Extract from the Data menu.

You can also use this method in Microsoft Excel versions 3.0 and 4.0, although in these versions of Microsoft Excel you have the option of setting the extract range from the Data menu. Because this method creates the reserved defined name "Extract" on the spreadsheet, you do not have to select the extract field names prior to extracting records. If you select only the field names when you extract information, or if you set an extract range in version 3.0 or 4.0 that refers to the field names alone, each record that matches the current criteria will be extracted and displayed on the document after you choose Extract from the Data menu.

Caution: When you select the field names and choose Extract from the Data menu or when you've set your extract range by selecting only the field names and choosing Set Extract from the Data menu, any data between the field names in your extract range and the last row in your worksheet will be cleared. This will occur even when no data records are extracted. To avoid erasing your data, either create your extract range below or to the right of your data, or restrict the size of your extract range.

Restricting the Extract Range

You can restrict the amount of extracted information in several ways.

In Microsoft Excel versions 2.x, selecting a range that extends below the extract field names will restrict the possible number of extracted records to the selected area. Selecting the field names and the five rows beneath them, for example, will restrict any extraction to five records.

NOTE: If more records meet the criteria but cannot be displayed due to this restriction, Microsoft Excel will display an alert message stating that the extract range is full.

In Microsoft Excel versions 3.0 and 4.0, you can use the method above to select the extract range or, once you select your restricted range, you can choose Set Extract from the Data menu. This command will define the selected range with the reserved name "Extract." When you define the extract range with Set Extract, you can extract records without first having to select an extract range (because the extract range is already defined).

NOTE: If you set an extract range that includes, for example, five additional rows beneath the field names, an extraction will produce a maximum of five retrieved records. If more records meet the criteria than fit in the selected area, Microsoft Excel will display an alert message stating that the extract range is full.

Setting an External Extract Range

In Microsoft Excel versions 2.x, 3.0, and 4.0, to extract database information to an external worksheet, do the following:

1. Select your database and choose the Set Database command from the Data menu.

2. Copy the field names from the database, and paste them in the desired external worksheet.

3. On this external sheet, select the field names and one row beneath the field names.

4. From the Data menu, choose Set Criteria.

5. Copy the field names again and paste them in another location in the external worksheet. This area will be called the extract range and is where the extracted information will be returned. If you are using Microsoft Excel version 3.0 or 4.0, select these field names and choose Set Extract from the Data menu.

6. From the Formula menu, choose Define Name. In the Name box, type DATABASE. In the Refers To box, type the name of the worksheet within single quotation marks, and type an exclamation point followed by the word "Database." For example:

         ='Worksheet1'!Database
   

7. When you are ready to extract the information:

   and choose Extract from the Data menu.

   from the Data menu.

       NOTE: You do not need to select the field names if you have set
       them as the extract range as mentioned in step 5 above.

  Important: When you extract information to an external worksheet,
  both the worksheet containing the database and the external
  worksheet must be open. If the worksheet containing the database is
  not open, an error message will appear stating that the database
  range is not valid.

Database Functions

When you use database functions such as DSUM() and DCOUNT(), you can use any range that has a defined name for the criteria argument. To specify a range other than the defined criteria range, follow the same rules for setting the criteria range in a database (as specified in the "Setting Your Criteria Range" section in this Application Note). Instead of choosing Set Criteria from the Data menu to define the criteria, choose Define Name from the Formula menu. Assign a unique name to the range and use this name for the criteria argument in your database function.

The second argument in a database function, the field argument, can be an index number. For example, if you want to find the sum of the entries in the Salary field, and if this field is the second field or column in your database, you could use either of the following formulas:

=DSUM(database,"salary",criteria)

-or-

=DSUM(database,2,criteria)

NOTE: For the Find and Extract commands to work correctly, you must choose Set Criteria to set a criteria range.

Summarizing a Database with a Table

You can use a table to create a summary of totals for a database on a worksheet. Microsoft Excel includes functions that operate on a database and restricts their operation to the records that meet the criteria you enter in the worksheet. To use the same database functions repeatedly on a database, but with different criteria each time, use the Table command on the Data menu to create a data table. The data table input values are substituted into the criteria range, and the results of performing the database function with the different criteria are displayed in the table.

The following sample worksheet uses the DSUM() function in a two-input table. In the worksheet below, the range A1:C2 is defined as "Criteria", A4:C15 is defined as "Database", and E4:H8 is the area occupied by the table. The purpose of this table is to simultaneously determine gross sales by sales-person and by region.

   A      B           C       D    E       F       G       H

4  Region Salesperson Sales        618     MW      NW      SW
5  NW     Jones       $50.00       Brown   $80.00  $75.00  $45.00
6  SW     Smith       $75.00       Jones   $25.00  $50.00  $65.00
7  SW     Smith       $33.00       Owen    $70.00  $0.00   $90.00
8  SW     Brown       $45.00       Smith   $10.00  $0.00   $108.00
9  NW     Brown       $75.00
10 MW     Brown       $80.00
11 MW     Jones       $25.00
12 SW     Jones       $65.00
13 MW     Smith       $10.00
14 SW     Owen        $90.00
15 MW     Owen        $70.00

1. Type the data in columns A, B, and C.

2. Set the database to the range A4:C15, and set the criteria to the range A1:C2.

3. To create the table, in cell E4, type the formula =DSUM(Database,3,Criteria).

4. In cells E5:E8, type the names, and type the regions in cells F4:H4.

5. Select the range of your table, E4:H8, and choose Table from the Data menu.

   This will bring up a dialog box asking for the Row and Column Input Cell. Because the different regions are listed in the top row of the table, and the cell in the criteria range in which these regions should be entered is $A$2, $A$2 must be the Row Input Cell. Following the same reasoning, the Column Input Cell must be $B$2. Enter these cell references in the appropriate boxes in the Table dialog box and choose OK.

       NOTE: If you do not want the value of the formula (618) in cell E4
       to show, select cell E4, choose Number from the Format menu, and
       type the format ;;; (this will hide all values returned by the
       DSUM() function in that cell).