NOS : Certificate in Computer Applications
| Home | Table of Contents |

Up: Online Course Material

Many times we have to combine data from more than one file. In such situations we have to handle multiple databases. Another point is that the data contained in a database file is not suitable for presentation. It has to be put in a presentable format. This aspect of dBASE III Plus is possible through the report form facilities of a database.

After going through this lesson you should be in a position to

• explain the concept of multiple database
• understand work area
• relate more than two databases
• create a report file from a database
• print a report file

In the previous two lessons we have discussed about the commands to manipulate and retrieve data from a single database file. But at times we may need to combine data from different database files for purpose of manipulation and reporting. dBASE provides scope for handling up to 10 database files open at the same time and access data from them. This is called Multiple File Handling.

22.3.1 Characteristics of Multiple Files

Before we discuss the procedure of handling more than one-database files let us look into the characteristics of multiple database files. Let us start with two files (dB1 and dB2). We may come across the following situations.

Situation 1: The number of records in both database files dB1 and dB2 is the same. The number of fields and the type of fields are the same.

Situation 2: The number of records in both dB1 and dB2 is same. The number of fields is same but type of fields is different.

Situation 3: The number of records is different. But number of fields and type of fields is the same.

Situation 4: The number of records and number of fields are different. But there is at least one field which is common to both dB1 and dB2. This common field is called the Key Field.

Let us look in to the type of work that we may require while handling two database files:

• We want to access data from both dB1 and dB2 at the same time.
• We want to create a database file dB3 by combining i) all the fields in the records of dB1 and dB2, ii) selected fields from the records of dB1 and dB2.
• We may require updating of one or more fields in a file called the master file taking the data from another file called the transaction file. Let us take dB1 as the master file and dB2 as the transaction file. So we take data from dB2 to update dB1.

When a database file is opened by the USE command, dBASE allots a work area for that file. When we use only one file, we do not need to give any reference to the work area. But when we use multiple files (up to 10 files), we have to specify the areas in which the different files will work. The SELECT command is used to allot work areas for different files in dBASE. dBASE assigns numbers 1 to 10 or characters A to J to these work areas. The general form of the SELECT command is

.SELECT workarea

.USE filename

Suppose we have two database files, dB1 and dB2. For allotting the work areas A and B for the two files, we give the commands as follows:

. SELECT A

. USE dB1

. SELECT B

. USE dB2

On the other hand we may allot numbers to the work areas. For allotting the work areas 1 and 2 to the files dB1 and dB2, we give the commands as follows:

. SELECT 1

. USE dB1

. SELECT 2

. USE dB2

In the example we have taken two database files but you can take any number (up to 10) of database files. In place of dB1 and dB2 you can give any other file name.

When several files are in use by the SELECT command, the file that is selected last is called the active file and other files are called non-active files. When we allot work area to multiple files the file selected last becomes the active file. In the last example, the file dB2 is selected last and so it is the active file. Suppose you have a field by name AGE in dB2. This can be referenced simply as AGE (note that AGE is a field in the active file.). Suppose you have a field by name STDNAME in the file dB1. Then this field is referenced by writing as A STDNAME (note that when we refer to a field in the non-active file we prefix it with the work area allotted to the no-active file). The symbol () 'a minus sign followed by a greater than symbol' is used to establish the relation between the non-active database file name and its field name.

You can make any non-active file, active by giving the command

.SELECT non-active-file-name.

Suppose in the example considered earlier if we give the command

.SELECT A

The file dB1 in the work area 1 becomes active and the file dB2 becomes non-active.

In this case the file in the work area 1 is made active currently. One must not give the USE command for making a file active. The USE command opens a file and a file that is once opened must not be again opened, since it is a syntax error. The SELECT Command does not open a file but simply switches between the work areas. Thus we see that the SELECT command enables us to access fields or data from as many as 10 database files.

dBASE has got a facility to give an alternate or an ALIAS name to a database file dB1. The SELECT command is written as follows:

.SELECT A

.USE dB1 ALIAS biofile

If at any time you want to make the file dB1 active you can give the command

.SELECT biofile

The above command automatically puts the file dB1 in the work area A and makes it active. Without making a file active, you can display any particular field from a non-active database can by giving command as follows:

. DISPLAY alias name ->field-name (if you have given an alias name)

or

. DISPLAY work area name -> field-name (if you have not given an alias name)

You already know that the symbol (->) is used to establish the relation between the database name (or its alias name) and its field names. For the field STDNAME in the active file dB1, we can display the field by the command

. DISPLAY stdname

If no alias names are given by the user, dBASE will assume the filename itself as the alias name. Suppose you give the following commands:

. CLOSE ALL (This command closes all files)

. SELECT B (This will select work area B)

. USE dB2 (This will assign work area B to file dB2)

. SELECT A (This will select work area A)

. USE dB1 (This will assign work area A to file dB1)

. DISPLAY STATUS

On the monitor you will see the status as follows:

Select Area   : A, Database in Use: A : dB1.dbf alias : dB1

Select Area   : B, Database in Use: A : dB2.dbf alias : dB2

This command is used to link two database files with or without a common field between them. Suppose you have to link two database files dB1 and dB2. The contents of these two are as shown in the Fig. 22.2 and Fig. 22.3 respectively.

. USE dB1

. LIST

.

Command Line ||<C:>|| dB1 || Rec: EOF/5 || ||

Type a command (or ASSIST) and press the ENTER key.

Enter a dBASE III PLUS command.

Fig. 22.2

. USE dB2

. LIST

Command Line <C:>dB2 Rec: EOF/5

Type a command (or ASSIST) and press the ENTER key .

Enter a dBASE III PLUS command.

Fig. 22.3

In this example the two files have the same number of records but completely different field types. The only relation which we see between these two files is that there is one to one correspondence between the record number of the two files. We will now link the two files using this common feature between them. The commands are given as follows:

.SELECT B

. USE dB2

. SELECT A

. USE dB1

. SET RELATION TO RECNO( ) INTO dB2

. LIST CITY, STATE, SALARY, B->NAME, B->AGE

The listing of the combined file looks as shown in the Fig. 22.4. In the above case all the fields and all the records are listed.

Command Line <C:>dB1 Rec: EOF/5

Type a command (or ASSIST) and press the ENTER key .

Enter a dBASE III PLUS command.

Fig. 22.4

Top

IN-TEXT QUESTIONS 1

1. How many databases can be used simultaneously in dBASE III+?
2. Give the name of the first work area used in dBASE III+.
3. Which command is used to select a work area in dBASE III+?
4. Differentiate between active and non-active files.
5. Choose the correct answer:

(a) What is the maximum number of work areas that can be used in dBASE

(i) 20 (ii) 10 (iii) 5 (iv) 9

(b) The first work area in dBASE is referred as

(i) W -area (ii) ‘A’ (iii) ‘A-J’ (iv) none of the above

Top

22.5 REPORT FORM

So far we have covered listing and modification of raw data. We also know how to link two files. But in all the cases discussed so far the result we get is not formatted in a presentable manner. We cannot take a print out and submit it straight away. The result we generate has to be presented in a suitable form, as we will see below. Such a task is possible through the report form.

Report form files are created and modified with the CREATE or MODIFY REPORT command. Before the command is used, a database file must be in use. The field characteristics within the selected database file are used to help you design your report form.

Both .DBF and .VUE files are used with the CREATE or MODIFY REPORT command. Once designed, the REPORT FORM filename command is used to reproduce the report on your screen. If you add the TO PRINT clause to the REPORT FORM command, the report is printed as it is displayed on your screen.

The default extension given a report form file is .FRM. However, you can assign a different extension upon creation, or you can use the RENAME command rename the file extension.

Open the file BIODATA and give the CREATE REPORT command. Instead of the word CREATE you can use the word MODIFY also.

. USE BIODATA

. CREATE REPORT

dBASE responds to the above command by displaying the following message:

Enter Report File Name:

You have to type the filename BIO and press ENTER key. dBASE displays a menu screen with five items in the main menu as follows (see, Fig. 22.5).

• Options
• Groups
• Columns
• Locate
• Exit

By default the menu item OPTIONS is highlighted. In the menu item OPTIONS there are 9 sub-items as shown in Fig 22.5. By default the sub-item PAGE TITLE is highlighted. We will discuss about each of these in detail.

There are nine sub-items under the menu item OPTIONS. They are discussed below.

CURSOR   Char:         Word: Home End Pan:  ^     ^ INSERT Char:        Ins Field:        ^N Help:         F1 DELETE Char: Del Word: ^Y Field: ^U Up a Field: ­       Down a Field:    Exit/Save:           ^End Abort:                   Esc

CREATE  REPORT  <C:>         BIO.FRM      OPT: 1/9     |

Position selection bar           Select : '             Leave menu    Field names begin with a letter and may contain letters, digits and under scores

1. Page Title: By default the cursor is over the page title item. On pressing the control key CTRL a window opens up by the side of the option menu box where you want to enter titles of further report. The titles can be up to lines of text, each line can be up to 60 characters.

2. Page width: This is used to specify the number of characters to be printed on each line of the report. The default is 80 characters.
3. Left margin: In this option you can specify the number of characters required as left margin. The default is 8 characters.
4. Right margin: Used to specify the number of characters required for right margin. The default is 0.
5. Number of Lines/Page: Used to specify the number of lines on every page of the report. The default is 58 lines.
6. Double Space Report (Y/N): Used to specify double spacing in the report. Default is single space.
7. Page Eject before Printing (Y/N): Used to skip to a new page before printing. By default a page is skipped before a report is printed.
8. Page Eject after Printing (Y/N): Used to skip a page after the report is printed. By default it does not skip a page after a report is printed.
9. Plain page (Y/N): Used to suppress/display page number and date. The default is NO which suggests that page number, page title and date are displayed on each page. By typing Y (for Yes) you will get the page title only on the first page. But the page number and date will not be printed.

After setting the above options now let us take the cursor to the second menu item GROUPS by the right arrow key. The contents of the screen will change as shown in Fig. 22.6. There are six sub-items under the menu item GROUPS. Each of them is described below.

1. Group on expression: This sub-item is highlighted by default (see Fig. 22.6). This item groups the records having a common value in a key field. New groups are started when a new value is encountered. The key field is selected in the COLUMNS menu selection.
2. Group heading: This let you enter a report heading that is displayed as each new group is encountered and displayed.
3. Summary report only: The default option under this item is NO. This includes all records in the report. If you change it to YES detail information is not provided and only subtotals are provided.
4. Page Eject after group: YES, This causes a form feed after each group is printed. The option NO prints the groups together.
5. Sub-group on expression: This selection lets you specify a secondary level of grouping for sub-groups within groups. Like the main group, sub-groups should also be indexed or sorted.
6. Sub-group heading: Lets you enter a report heading that is displayed as each new sub-group is encountered and displayed.

CREATE  REPORT  <C:>         BIO.FRM      OPT: 1/6     |

Position selection bar           Select : '             Leave menu    Enter a field or expression on which to break for the first level of subtotals.

Now let us discuss the menu item COLUMNS. You can select this item by the right arrow key. The screen will be displayed like Fig. 22.7. There are five sub-items under COLUMNS. Each of them is discussed below.

1. Contents: Enter the field name for each column. You can press ENTER or F10 key to display a field list. Select and return to the Column menu with the Right or Left Arrow key.
2. Heading : Lets you enter up to four 59-character lines as column headings.
3. Width: The column widths default to the field width or heading width, whichever is larger. You can modify the width by typing a new field.
4. Decimal places: Uses the file structure value. If you enter a smaller number, the displayed result is rounded. Only applies to numeric fields.
5. Total this column: The default is to print the total at the bottom of the report. Type NO to suppress printing.

Note that the column specifications must be given for each column. As you fill the items for the column details, you will find that a REPORT FORMAT box is opened at the lower end in the position of the cursor control menu. This shows the column heading and the column formats. The following symbols are used to represent the structure of the report as you enter it.

CREATE  REPORT        ¦<C:>¦         BIO.FRM ¦                       Column: 1 ¦      ¦

Position selection bar           Select : '             Prev/Next - Pg Up/Pg Dn. Enter a field or expression to display in the indicated report column.

The locate menu is provided to help with column definition. This menu item lists the selection that you made using COLUMNS menu. If you do not define the columns earlier the screen will display like Fig. 22.8.

CREATE  REPORT        ¦<C:>¦         BIO.FRM ¦                       Opt: 1/1 ¦           ¦

Position selection bar           Select : '                Leave menu    Step to any column immediately by selecting the column expression.

The Exit Menu box has two items as shown in the Fig. 22.9. When SAVE is highlighted, press the CTRL key. The Report File BIO.FRM is now saved and can be used for printing the report. The option ABANDON does not save the report file.

CREATE  REPORT        ¦<C:>¦         BIO.FRM ¦                       Opt: 1/2   ¦           ¦

Position selection bar           Select : '                Leave menu    Exit and save changes.

To print the Report for selected records a FOR clause or a WHILE clause can be added to the REPORT command. For saving the Report in a .TXT file, you give a TO filename clause to the REPORT commands as shown below:

.REPORT FORM BIO TO REPO.TXT

The bio report is now stored in the REPO.TXT file. This is an ASCII file and you can see by giving the TYPE command from DOS prompt.

Top

IN-TEXT QUESTIONS 22.2

1. What is the extension of report file ?
2. Which command is used for report creation?
3. What are the sub-menus of COLUMNS menu?

In this lesson we covered the basic commands and techniques for displaying data in a formatted report. The first part of this lesson discusses the multiple database concepts like file handling, work areas and the commands related to multiple database. The second part covers report generation from a database file.

22.9 TERMINAL QUESTIONS

1. What are the characteristics of multiple files?
2. How do you switch the File Status from Active to Non-Active form?
3. What is the usefulness of SET RELATION TO command? Explain with example.
4. What are five items or menus while creating a report? Explain in brief.

IN-TEXT QUESTIONS 1

1. 10 databases
2. The first work area in database is A.
3. SELECT command
4. The file that is selected last is called the active file and other files are called non-active files.
5. (a) ii(b) ii

IN-TEXT QUESTIONS 2

1. CREATE or MODIFY commands are used in report creation.
2. Contents, Heading, Width, Decimal places, Total This Column are five sub menus of COLUMNS menu.
3. (a) iii (b) iii

Top