ms access data ba

7/31/2019 Ms Access Data Ba

1/26

Database Management with MS Access

Learning Objectives

Concepts of database management systems (DBMS)

Relevance in agricultural research

Developing simple applications in database management using

MS Access


2/26

Definition

Database management systems are the means to transform data into information for analysis,

evaluation, and decision making.

The word database nowadays implies a computer based system. But, in general databases

have existed in different forms. Some familiar examples are:

Paper sheets in files Scientists Literature Reference cards

A book

Books in a library

Telephone directory

Dictionary

Maps

Soil samples

DNA samples Letter Dispatch register

Video library, etc.

All of these are organized sets of information with some kind of an indexing system which

facilitates access to and search for specific information ( eg. indexing systems for files,

papers/letters; table of contents in a book; the alphabetical order of arrangement of words in

dictionary; the alphabetical order and classification system in a telephone directory; subjector author based classification of reference cards by individual scientists; the classification of

books in library; the coding systems used by scientists for storing and retrieving samples;

etc.). These examples illustrate how information is organized in databases and accessed for

use.

A database management system is a computerized database with features similar to the above

databases. It is a software programme that permits the creation of databases and work with

the data in them.

Definition: Database management systems (DBMS) are computerized systems for

organizing, storing, retrieving, updating and analyzing large and related data quickly and

efficiently to provide useful information for decision-making for specific purposes and

it ti


3/26

Relevance to agricultural research

Data are valuable resources in agricultural research. They are generated at considerable

expense. Much of the agricultural research data is generated over several seasons through

multidisciplinary and multilocational research, and needs to be stored and managed

efficiently. Field research, even at the individual level, involves recording data in a number of

experimental plots or farmers fields and over several seasons. DBMS enable such research

data to be organized and managed efficiently both at the individual and multiuser level for

purposes of analysis, decision-making and presentations..

Further, the users of the research data can be diverse and not limited to those who generated

the data. Researchers like those involved in developing simulation models for decision

support, technology transfer or in designing agricultural policy and development

programmes often use data generated from others research to save on time and costs. For

such users, and also for individual researchers DBMS help to:

separate data from applications (the need to do this is becoming more and moreimportant in modern research when data have to be shared between diverse users and

applications)

avoid unnecessary duplication of data

perform two basic functions reading and editing on the data routinely and

consistently. Both require systematic and consistent identification of the data. Thiscan be difficult without a DBMS when data are large and complex

when data are large and complex they need to be carefully structured to identify

specific data and eliminate redundancy and inconsistency in the databases. Database

management systems permit this.

Database management concepts are central to the newer tools of agricultural research likeGIS (in natural resources management research), and bioinformatics (in plant and animal

science research).

Some example areas of agricultural research and policy where databases are useful because


4/26

Experimental station management

Bioinformatics/genomics/proteomics

Database types

The most familiar way of representing data about entities (distinguishable real world objects

with common properties; examples of entities are students, employees, states, districts,

farmers, field plots, fertilizers, rainfall, etc.) is in the form ofTables or data files

Tables are two-dimensional structures consisting of columns and rows. The columns are

called Fields and the rows are called records in database terminology. Thus, in a Table,

data are stored in fields and records.

A field is the basic unit of representing information about an entity. Each field refers to one

attribute or property of the entity. For example, name is a field or attribute of entity

employee in an employee table or data file;, age is another such attribute for the sameentity; temperature and rainfall are fields/attributes of the entity weather in a weather table

or data file; plot no., dates of sowing are fields/attributes of the entity, experimental plot

data file; district name, area, etc are data fields corresponding to the entity districts in a

districts table or data file and so on.

Records are actual data (text, numbers, dates, etc.) for each attribute/field in the Table. Each

record is one set of attribute data, that is, the data of all fields for one entity (eg. text,numbers, dates, etc. representing say name, age, date of birth, etc. for any one employee in

the employees data file, or soil type, texture, available water capacity etc., in soils data file,

district number, name and area in a districts data file, etc.).

In a table each record is one row of data, and fields are the column headin s.

Table of Districts

District Area (000 ha) Population

Number of fields =

number of columns


5/26

Commonly used database structures for organizing data in tables are of two types:

Flat file (one table)

Relational (many tables)

Flat File Databases

In aflat file database, all the information is in one table of one array of rows and columns like

the Table of Districts in the example above. A typical example of a flat file database is a

spreadsheet (MS Excel Table)

The Advantages of a flat file data structure are that it is simple and easy to implement when

the number of records (rows) and attributes (fields) are few.

However, if there is repetitive information in the table ( like similar data for different years -

see table below) and if there are a large number of attribute data (more columns) the flat file

database structure can be unwieldy:

District Area(000ha)

Population(000)

Year Area_rice (ha) Prod_rice(tonnes)

Aaaa 2000 25000 1990 10000 15000

Aaaa 2000 25000 1991 10500 18000

Aaaa 2000 25000 1992 11000 11900

Bbbb 2500 23578 1990 11235 20345

7

The disadvantagesof a flat file data structure are that there can be :

too much information in one table if there are a large number of multiple attribute/fields data (table width can increase to several screen widths) making it unwieldy

i i f d i l ( d d )


6/26

As the variety of information increases, or if there are multiple entries for any one type o

data and information needs to be duplicated for some fields, the table (flat file) may grow totoo large a size to facilitate easy retrieval and updating of information. It also becomes

cumbersome to update the database frequently and errors are likely. Flat file database

structures are not suitable for such situations.

Relational Databases

Relational databases eliminate the problems of redundancy and inconsistency of data

encountered in flat file design by splitting data into several tables and establishing relations

between them. The splitting of data into several tables is such that each table represents one

entity

A collection of related tables is a relational database.

In a relational database, two tables which form relationship are referred to as the primarytable and the related table. The tables can be related if they have a common field. The

following examples illustrate this.

Employee database

Employees

Pay

Performance

Training

Broad categories/themes of

related information about

Primary Table

(list of employees

with basicinformation of

each as in service

book)

leave

Relational databases example 1


7/26

to individual employees.. This field is usually the EmployeeID field. Using this common

field, the data in all the Tables can be connected to create useful information like salary slips,

leave and performance reports, etc., for the employees.

District

fruits

Cerealsvegetables

Fertilizer use

Weather

Livestock

Agricultural production and resources database of districts

Primary

Table (list

of districts)

Entities/Related Tables

with annual data for

districts

Relational databases example 2

In example 2, the Primary table, District, holds data about the districts by using data fields

like DISTID, Name, Area, etc. The related tables hold data for different districts on themes

like annual area and production of different cereal crops, fertilizer use etc. The Tables can be

related if they have the field DISTID in common with the primary table, District. Once

related useful information like crop yield, input and weather variations, etc, across districts

can be obtained.

In a relational database:

Related fields form a Table

Related tables form a database

Records in any two tables can be connected through a common field that appears in


8/26

Database structure

Relational databases follow well defined rules of structure to maintain data integrity. If the

structure design is good, it allows easy modifications and data retrieval, and minimizes scope

for data entry errors. Crucial to good design of database structure are the determination of :

(i)

(ii)

(iii)

Tables and keys

Relationships between the tables, and

Integrity rules which determine the consistency and efficiency of the database.

Tables and Keys

The first step in creating the database structure is to develop a list of all the data fields

required for the database and group them into different tables by themes. The grouping

should allow identification of the Primary and Related Tables. The tables can be related if

they have a common field.

No.

Relating Tables in a database (example 1): Employee data

Table 1-Base data(service book)

Table 2 Pay

data

ID Name Division Designation

1001Aaaa Agronomy Asoc Professor

1002 Bbbb Genetics Professor

1003 Cccc Engineering Asst.Professor

ID Month Basic HRA DA

1 1001 January 10000 1500 4000

2 1001 February 10000 1500 40003 1002 January 8000 1200 3200

4 1002 February 8000 1200 3200


9/26

contains only a single value. The order of the rows and columns is not important. They can

be viewed or added in any order. The field ID. is called the Primary Key of the Table. In

Table 2, records can be uniquely identified by a single field only when the serial numbercolumn (No., First column) is included in the table. So, in this table the field NO. is the

primary key. The field ID in Table 2 ( which is the common field with Table 1) is called the

Foreign Key.

Notethat in the above tables, the information content of a table does notdepend on

The order of the rows; or

The order of the columnFurther, the rows and columns of a table can be rearranged at will without affecting the

table's information content)

Primary Key and Foreign Key

Primary key is a Field (attribute/column) that uniquely identifies each record inthe table

OTE:

Choice of primary key is usually obvious from the structure of the Table.

If there is no easy natural choice for a primary key add a column containing a

unique identifier (serial number)

Data in every column of the row must be dependent on the primary keyForeign key is the Field in the related Table that is common with the Primary key

in the primary table.

The primary key and the foreign key connect the primary and related Tables .

Relationships

In the above example, in the primary table (Table 1), for every data value in the primary key

field (ID) there are several values in the Related Table (Table 2) in the foreign or common


10/26

Types of Relationships

A Relationship is created between two tables by creating a join between them through the

common field. Records between two tables are then connected through the common

field. Creating the relationship allows viewing and extracting data from the two different

tables as if they were from one large table.

(i) A one-to-many relationship exists between two tables when one record in theprimry table matches zero, one or many records in the related table.

(ii) Many-to-many relationships can also occur. In such relationships, each record

in the primary table can have many matching records in the related table and

one record in the related table has many matching records in the primary

table.

Similarly in the districts database in the following example, DISTID is the primary key in the

primary table and the serial number field (NO). Is the primary key in the two related tables.

DISTID is the foreign key in the related tables. The primary and the related tables 1 and 2 are

related by a one-to-many relationship. The three tables can be used to relate the cereals

production and fertilizer use in any district and year as the field DISTID is common between

them.

Basic data of districts Primary table

Area and production of cereals Related Table 1

Relating Tables in a databases (example 2): Database of agricultural

production and resources in districts

DISTID Distname Statename

1001 aaaaa bbbbb

NO DISTID Year Rice_ka Rice_kp Wheat_a Wh


11/26

Database integrity

In a relational database, integrity must be maintained between data in the primary and relatedtables.This implies that:

(i) There cannot be any values in the foreign key field of the related table,

corresponding values for which do not exist in the primary table.

(ii) Any editorial changes to the data values in the primary key field of the primary

table must be automatically reflected in the corresponding data values in the

foreign key field of the related table. The user should not have to reopen each

related table to make the required changes in the foreign key field.

(iii) If one record is deleted from the primary table, then all the records in the related

tables with corresponding data in the foreign key field should be deleted

automatically.

All of these essentially mean that that there must be consistency between data in the primary

and related tables to maintain valid relationships. For such consistency the data must follow

specific rules called data integrity rules.

Database Integrity Rules

(i) in all tables the primary key must be unique and that there should be no null values

in this field. (primary key integrity)

(ii) each non-null foreign key value in the related table must match a correspondingprimary key value in the primary table (referential integrity)

(iii) the data types of primary and foreign key in a the primary and related tables must

match perfectly (domain integrity)


12/26

Summary of database design

Designing the database structure summary of steps

Identify all relevant entities (Tables) and attributes of each entity (fields) required toproduce the needed information

Group related fields into tables (one table for one theme or set of related fields)

Determine the relationships between the tables. (primary and related tables)

Determine the properties of each field (field name, data type, description, etc.)

Determine the primary key of each table - unique identifier for a row

Include a common field with the primary table in the related tables

Avoid data redundancy in all tables:

No table must have duplicate rows or columns

No duplication of data between tables

No derived or calculated data fields

Eliminate columns that do not depend on the primary key

Create new tables if necessary

Identify the common field between the Primary and related tables. This is usually the

primary key in the Primary Table. The same (common) field in the related table iscalled the Foreign key

Ensure that the data types of the primary and foreign key are the same.

Connect the tables by joining the primary key from primary table with foreign key of

related table to create relationships.

Add data to tables


13/26

Database management system (DBMS)

The database tables, keys and relationships (which constitute the structure of the

database) and the software that facilitates their creation, storage, retrieval and linkages,

together constitute a database management system. A DBMS may also be called a database

management programme. MS Access is one such database management programme, that is

powerful, efficient and flexible enough for developing information systems for a range of

purposes. In addition to storing and arranging information Access helps to run reports that

lead to conclusions. Using Access, data can be entered directly into a database or importedfrom another programme, sorted, indexed and organized in whichever way needed, and

reports can be created quickly using all or part of the data. It is possible to customize data

entry into simple filling the blanks on a form and also run queries that extract subsets of the

data that meet specified conditions.

MS Access terminology

MS Access is an object-oriented programme; that is everything in Access is an object,including the database application itself. All objects of the database are stored in a single data

file, and the filename has an .MDB extension. The objects are managed through a Database

window. An important feature of Access is that it comes with several database wizards. These

are mini programmes that interact with the user about his needs for creating different objects,

and then create the database structures that match them. The user only needs to choose among

a range of options and enter the actual data. Excellent help are also features available in MS

Access for users to learn and deploy the various functions. Because of such user-friendly

tools for designing each object, Access is an appropriate database management tool for both

novices and professionals.

The first step in developing a DBMS using Access is the creation of the database file.

This file contains all the data in the form of several tables. It also contains files for

customized data entry forms, reports and queries that constitute the database application.

Thus a database file in Access contains files which can be: Tables, Forms, Queries or Reports

Tables are the core primary building blocks of a database. A Table is very much like

a data table or spreadsheet containing rows (records) arranged in different columns (fields).

At the intersection of field and a row is the individual bit of data for a particular record,

called a cell. Each database file can have many tables. Each Table is collection of similar

data. All the data entered into a database end up in a Table for storage. The specific Tables


14/26

entry based on the needs and some flexibility in their changes is possible, but it is better to

plan them in advance.

Queries are questions that users ask of the data stored in the Tables to obtain

information. They facilitate seeing data from several Tables simultaneously. They also

facilitate seeing only that part of the data in the Tables that is desired at a particular time by

filtering out the data that may not be needed.

Reports are designed to be printed. They are specially formatted collections of data,

organized in a specified way to summarize the data. A report can extract data from many

Tables, perform simple calculations on the data and present results in a neat format. Reports

can be created at any time. They need not always be planned beforehand.

Tables, forms, reports and queries are created in separate steps, but they are related.

Tables are the core of the DBMS, with the others having to do with entering, extracting or

presenting the data in the Tables.

Multiple Tables can be created in Access to reduce redundancy in a database. Bycreating Forms, Queries and Reports, data can be extracted from more than one Table easily.

This can be done best when there is a well-defined relationship between the Tables. When

two Tables have a common field they can be related.

Defining relationships: A relationship is defined in MS Access by adding the tables to be

related to the Relationships window, and then dragging the key field from the primary table

and dropping it on the key field in the other table. The kind of relationship that Microsoft

Access creates depends on how the related fields are defined:

- A one-to-one relationship is created if the related fields are both primary keys.

- A one-to-many relationship is created if the related fields are a primary key and a

foreign key.

The accompanying chart shows the type of relationships that can be established in an

national level district wise agricultural resources database. Notice the 1 and infinity sign nextto the primary key and foreign key (DIST). This implies a one-to-many relationship between

the two tables. The signs appear in relationships whenever referential integrity (another

feature of MS Access) is enforced in relationships. Enforcing referential integrity prevents

data entry mistakes and ensures that information in the two fields matches.


15/26

Fig1. Tables and their linkages in a district database

Using the above Tables, forms (Fig 2) reports can be generated from queries for

specific information about say the average irrigated area under rice in past five years in each

district of a State (Fig 3) or the rice yield variations during kahrif and rabi and the

corresponding variations in rainfall and fertilizer use during the past five years (Fig 4).


16/26

Fig 2: Form for entering data of rice


17/26

Irrigated area under crops

DISTRICT RICE CEREALS PULSES OILSEEDS SUGARCANE COTTON

Chengalpattu

Summ or 'DIST' = 83 (4 detail records)ary fAvg. 229.0475 234.3175 5.87 60.12 13.715 0.09

South Arcot

Sum ary for 'DIST' = 84 (4 detail records)mAvg. 187.54 192.9575 0.7425 52.95 48.6175 8.0225

North ArcotSum ary for 'DIST' = 85 (4 detail records)mAvg. 78.935 59.9025 1.4275 27.84 21.5425 0.99

Salem


Coimbatore

Sum ary for 'DIST' = 87 (4 detail records)mAvg. 21.455 39.625 2.2625 69.2275 11.93 10.9875Tiruchirapalli


Tanjavur


Madurai


Ramanthapura

Sum ary for 'DIST' = 91 (4 detail records)mAvg. 59.6575 60.5025 0.0725 4.535 0.0975 0.4525Tirunelveli


The Nilgiris

Sum ary for 'DIST' = 93 (4 de ail records)m tAvg. 0.14 0.1425 0 0.135 0.0025 0.0025Kanyayakumari


18/26


19/26

Case study

Objective:

To design a prototype district level Rice Production Information System (RPIS) for

Andhra Pradesh using MS Access. The DBMS should facilitate sorting and filtering data by

district, region, years and also allow queries on variations in rice yields. It should also

contain appropriate Forms for entering data in the available formats, and Reports and graphs

for presenting the results of analysis.

Given:

(i) List of districts

(ii) Data sheet of rice production during 1993-97

(iii) Data sheet of rice area during 1993-97

Step 1: Conceptual design of database structure(identification of tables, keys, relationships)

(a) List all the fields of interest and identify their data types

The fields are

(i) DISTID (identification number of district - integer)

(ii) DISTNAME (name of district - character)

(iii) YEAR (integer)(iv) RICEKA ( area under rice in kharif season - number)

(v) RICERA ( area under rice in rabi season - number)

(vi) RICEKP (production of rice in kharif season- number)

(vii) RICERP (production of rice in rabi season - number)

(b) Group data fields into tables by theme

Two tables will be required based on the two themes of data districts, area and production

of rice.

Table 1 will be the the districts table and will contain the fields DISTID and DISTNAME.


20/26

(d) Identify the keys in each table and the relationships

In table 1, since DISTID takes unique values and identifies each row uniquely it is the

primary key in table 1.

In Table 2, none of the listed fields will take on unique values and can therefore be a primary

key. With the existing set of fields it will not be possible to uniquely identify a record. So, a

new field (serial Number, NO) is introduced to be the primary key.

DISTID which is the common field between Table 1 and Table 2 can be used to connect or

relate the records in the two tables. The relationship between the two tables can be

established by connecting DISTID in Table 1 with DISTID in Table 2. When this is done

DISTID becomes the foreign key in Table 2.

Step 2: Create the database file in MS Access

(i) Start Access from START menu on task bar of Windows or from MS Office

Shortcut toll bar.

(ii) Create a blank database by selecting the NEW DATABASE option on FILE

menu.

(iii) In the dialogue box that is displayed select BLANK DATABASE and ClickOK.

(iv) The database window is displayed

(v) Specify the file name in the dialogue box as APRICE. This file is saved with a.MDB extension is APRICE.MDB. Note that the file name appears on the

database window.

Step 3. Create tables

(a) Table 1: Design and create the following Table with DISTID and DISTNAME as two

fields. The data type of DISTID is number (integer) and of DISTNAME is text. Make

DISTID theprimary key field.

In the database window, select TABLES on the bar. Since this is a new database, the window

that appears is blank. (For an existing database, the created objects will be displayed). Of the

3 buttons on the right side of the Window, select


21/26

for the Fieldthat is to be created. (Data types can be Text, Memo, Number, Date, Currency,

Auto number, Yes / No etc.). For Table 1, Row 1 will be DISTID (data type integer) and

Row 2 DISTNAME (data type character). Descriptions about the fileds can be filled in theirrespective rows. (Giving description is optional. Users normally would expand the code, give

the units of data, source etc., in the column for Description)

Then for the Field to be designated as the Primary Key (DISTID), Click on the extreme left

of its row. The row is highlighted. Select the Edit menu option from the top bar and select

Primary Key. A key symbol appears on the left side of the selected field. (Alternately the

primary key selection tool is also available on the MS Access tool bar and can be clicked

directly)

Save the Table with the name APDISTRICTS. The Table APDISTRICTS is now listed in

the database window.

Select DISTRICTS and select view in the Top bar and select Datasheet View. The table is

displayed with a blank row. (Alternately double clicking on DISTRICTS in database window

also opens the table with the blank row. Data can be typed into each column of this row (orpreviously existing data can be edited). Records can be sorted in ascending or descending

order for each column after they are entered. Specific records can be selected from the record

toolbar that is displayed at the bottom of the Table. Columns can be moved or resized by

selecting the fields and dragging them to be desired location. Columns can also be hidden if

required.

Open the Table and enter the data directly on screen as shown below for DISTID and

DISTNAME.

DISTID DISTNAME

101 Srikakulam

102 Vizianagaram

103 Visakhapatnam

104 East Godavari

105 West Godavari

106 Krishna107 Guntur

108 Prakasam

109 Nellore

110 Kurnool

111 Ananthapur

112 Cuddapah


22/26

(b) Table 2: Design the second table for data of annual rice area and production in kharif

and rabi seasons by following the same procedure as for Table 1. The table will have the

following fieldsNO, DISTID, YEAR, RICEKA, RICEKP, RICERA, and RICERP

NO will be the primary key field. Choose the data type of this as Autonumber.

Choose data type of all other fields is number.

Save this Table with the name APRICE

(Note: RICEKA= Area under rice in Kharif; RICEKP= Production of rice in KharifRICERA= Area under rice in Rabi; RICERP= Production of rice in rabi)

Since this is a wide and long table, it would be convenient to enter data through forms (seestep 5)

Step 4: Creating relationships

To create the relationships between the two tables APDISTRICTS and APRICE in the

database, select RELATIONSHIP option from the TOOLS menu (or from the Access Tool

bar). A Relationships window appears on the computer screen. When this is done for the firsttime, the Show table dialogue box also appears with a drop down list of the Tables that have

been created. At other times the dialog box has to be invoked by selecting Show Table from

the TOOL bar.

Select the Tables that are to be related (APDISTRICTS and APRICE) by double clicking on

drop down list. After selecting the Tables click the close button of the Show Table box. For

each table that is selected, a Table field box is displayed in the Relationships window. The

box displays a list of the fields in each table. The primary key of each Table will be in boldletters.

Select and Drag the primary key of the primary Table (DISTID in APDISTRICTS) to the

foreign key (variable with the same name as primary Key) of the related Table (DISTID in

APRICE). A relationships detail window appears. The window displays the names of the

related fields and their respective tables. Click on Enforce Referential Integrity, Cascade

update Related Fields and Cascade Relate Deleted fields to ensure that :

1. For every record in the foreign key field in the related Table, a corresponding record

exists in primary key field in the primary table.

2. Changes made to primary key, are automatically made for the foreign key, and

3. Whenever a record in the primary key field is deleted, all corresponding records in


23/26

FORM2 for Area data (Please see Data sheet 2).

Note that data from both forms will be entered into Table 2, APRICE

Click on the FORMS tab in the Database Window. The Forms window appears with the

three buttons, OPEN, DESIGN, NEW, just as in the case of Tables. If there are any old

forms created these will also be displayed. Otherwise, the window is blank.

To create a new form, first click on NEW. A new form dialog box is displayed. Select

FORM WIZARD and keep answering the dialogue boxes that appear. The first dialog box

asks questions about the Tables that are to be selected and the fields from each selectedTable that need to be included in the form.

To create FORM1 Production Form,

(i) select APDISTRICTS from the Table list down menu on the form wizard. The

efields of this Table (DISTID and DISTNAME) appear in the Available Fields

box below the selected Table.

(ii) Select the field DISTID from the Available fields box and click on the > buttonto transfer it to the Selected Fields box for the form.

(iii) Similarly select the Field DISTNAME from the available fields box and transfer it

to the selected fields box.

(iv) Select the second table, APRICE. The fields of this table (NO,DISTID,YEAR,

RICEKA,RICERA,RICEKP, RICERP) appear in the available fields box.

(v) Since FORM1 is for production data, select successively the fields YEAR,

RICEKP and RICERP in the available fields box and transfer them to the selected

fileds box as in (ii) and (iii) above(vi) Click on the Next button and select the form layout

(vii) Click on Next button and enter the form name, PRODUCTION FORM.

(viii) At the last dialogue box, click Finish..

To create Form 2 AREA FORM:

Repeat (i) to (viii) above with the exception that at Step v select YEAR, RICEKA and

RICERA and in step vii name the form ARE FORM.

Step 6: Entering data


24/26

(Queries are also Tables, with the difference that they are in response to questions asked of

the data in the Tables. But unlike in Tables, the tables that are seen on the screen are notstored. What is stored is the question or the query. Each time the query is invoked, it is run

and the output is seen as a table on the screen.)

(i) Select QUERY tab on the database window, and select NEW for a new query.

The new query dialogue box appears.

(ii) Select Simple Query Wizard

(iii) Select Tables and fields options (DISTID and DISTNAME from APDISTRITS

table and YEAR, RICEKA, RICEKP, RICE KA and RICERA from APRICEtable.

(iv) Assign the query a name (riceyield query) as in the case of FORMS above.

(v) In the databse window, open the query. A table with data in selected fields

appears.

(vi) Select design view in the query window, for setting criteria, sorting records and

inserting new fields (rice yields RICEKY and RICERY) that are derived from the

selected fields by calculations.(vii) Create new fields RICEKY ( for rice yield in kharif) and RICERY (rice yield in

rabi) in the Fields row of the query design view as:

RICEKY: [RICEKP]/[RICEKA]

RICERY: [RICERP]/[RICERA]

(viii) Create a parametric query to display information by district name. To do this, in

the query design view, in the criteria row and field DISTNAME type:

[ENTER DISTNAME]

Save and open query to display result. The query will prompt the user for the name of thedistrict and output the data of the selected fields for the district

Step8: Prepare reports

Prepare a chart showing rice kharif and rabi productivity trend by district(Yield vs year) the

results of the query:

Select Reports button in database window, select new, select chart wizard, select the query

and fields and follow the instructions as they appear just as in the case of forms and queries.

Additional exercise: Prepare Reports summarizing the production in

(a) Coastal Andhra (DIST 1-9)


25/26

DATA SHEET 1

Rice Production in Andhra Pradesh from1993 to 1997

1993 1994 1995 1996 1997No. District

Kharif Rabi Kharif Rabi Kharif Rabi Kharif Rabi Kharif Rabi

1. Srikakulam 387 4 388 4 412 5 327 3 278 3

2. Vijayanagaram 195 6 231 3 319 5 297 4 186 3

3. Visakhpatnam 104 9 121 9 204 10 197 13 80 9

4. East Godavari 921 590 544 461 577 563 402 615 429 492

5. West Godavari 671 695 709 733 591 666 376 759 630 673

6. Krishna 731 365 738 386 644 346 797 100 699 394

7. Guntur 960 14 755 48 825 17 928 20 845 52

8. Prakasham 278 118 165 166 149 83 224 186 124 222

9. Nellore 112 557 133 473 190 302 159 472 128 542

COSTAL ANDHRA 4059 2358 3784 2283 3911 1997 3705 2472 3399 2397

10. Kurnool 180 12 108 31 163 24 217 36 177 2411. Anathapur 54 53 39 33 64 25 96 72 25 37

12. Cuddapah 140 30 107 35 135 28 162 38 78 27

13. Chittor 75 178 67 118 91 91 147 176 85 137

RAYALASEEMA 449 303 321 217 453 168 622 322 415 225

14. Rangareddy 56 32 50 31 47 35 58 52 49 43

15. Nizamabad 200 26 220 35 206 84 270 107 135 49

16. Medak 136 28 117 64 111 78 143 76 83 5217. Mahbubnagar 73 53 76 45 102 89 176 90 101 78

18. Nalgonda 420 227 369 280 346 166 475 300 370 150

19. Warangla 219 50 290 89 226 96 360 119 97 83

20. Khammam 329 34 409 43 272 32 428 48 252 17

21. Karminagar 281 99 246 191 260 252 379 129 289 388

22. Adilabad 124 6 107 10 64 19 129 16 29 12


26/26

DATA SHEET 2

Rice area in Andhra Pradesh from1993 to 1997

1993 1994 1995 1996 1997No. District

Kharif Rabi Kharif Rabi Kharif Rabi Kharif Rabi Kharif Rabi

1. Srikakulam 191 1 205 2 219 2 183 1 186 1

2. Vijayanagaram 111 2 129 1 136 2 131 1 130 1

3. Visakhpatnam 95 3 101 3 115 4 114 4 96 3

4. East Godavari 237 158 243 157 260 158 262 160 214 154

5. West Godavari 235 188 264 197 270 198 269 199 247 193

6. Krishna 255 117 275 122 260 108 279 111 258 116

7. Guntur 291 5 288 17 273 6 292 6 295 14

8. Prakasham 93 41 71 64 60 47 78 62 62 74

9. Nellore 42 184 49 161 71 143 57 170 49 182

10. Kurnool 67 6 50 14 65 8 69 15 66 9

11. Anathapur 24 25 19 17 27 14 37 31 36 1812. Cuddapah 45 20 38 13 47 12 35 17 48 13

13. Chittor 36 70 34 48 38 45 58 75 42 57

14. Rangareddy 26 13 22 13 22 18 27 21 24 16

15. Nizamabad 99 12 94 16 102 34 106 42 82 21

16. Medak 72 20 54 27 64 41 68 40 48 26

17. Mahbubnagar 43 32 41 28 54 46 76 47 53 36

18. Nalgonda 133 92 125 102 130 66 159 120 149 6319. Warangla 86 22 104 38 94 42 1360 49 43 35

20. Khammam 130 15 154 17 116 13 164 19 121 9

21. Karminagar 105 43 92 66 104 88 125 105 103 38

22. Adilabad 59 3 58 4 63 7 58 6 31 5

ms access data ba

Documents