A Basic introduction to Microsoft Access
MS Access is a database management tool that enables one to have good command of data
collected. The programmed enables one to retrieve, sort, summarize and report results
speedily and effectively. It can combine data from various files through creating relationships,
and can make data entry more efficient and accurate.
Microsoft Access (MS Access) enables one to manage all important information from a single
database file. Within the file, one can use:
• Tables to store your data.
• Queries to find and retrieve specific data of interest.
• Forms to view, add, and update data in tables.
• Reports to analyze or print data in a specific layout.
• Data access pages to view or update, the data.
In MS Access, data is stored once in one table, but can be viewed from multiple locations.
When the data is updated in a Table, Query or Form, it is automatically updated everywhere it
appears.
Establishment of MS Access database
All MS Access databases files are saved with extension .m d b.
A database should have a separate table for every major subject, such as pedigree records,
Production data or Treatment information. Data should not be duplicated in multiple tables.
Microsoft Access provides three methods to create a database
„ Database Wizard (though easy, the wizard offers limited options to customize the
database)
„ Using a template (This method works best if one can find and use a template that
closely matches the specific requirements)
„ Creating a database directly (This is the most flexible method, but it requires one to
define each database element separately).
Create a new Access database
After creating a new database, this should be saved by the name which reflects the content of
the database. Upon saving the database, the MS Access database window opens with the
Tables tab-active (i .e in such a way that the next activity should be to create a table).
Creating a table
Tables are the data storage facilities in MS Access. Each table contains rows called records
and columns called fields.
A record is a collection of facts about a particular animal or event. Each record in a table
should be unique. To distinguish one record from another, tables can contain a primary key
field.
A field is a single kind of fact that may apply to each animal or event. For example, date of birth
is a field in a table on animal information.
The fields in a database have settings that determine the
- type of data they can store,
- how the data is displayed,
- what can be done with the data.
For example, field settings can ensure that birth dates are entered with two numbers for the
month, two numbers for the day, four numbers for the year, and slashes in between:
01/04/2006.
One important setting for fields is the data type, which could be a number, text, currency, and
date/time. The data type limits and describes the kind of information in the field. The data type
also determines the actions one can perform on a field and how much memory the data will use.
Fields also have properties that control the details of information inside them, including a
character length, a default value, and a validation rule that makes sure the data meets
certain criteria.
Tables may be created by either:
- Table wizard,
- Design view
- Entering data in a spreadsheet.
Creating Tables using design view
Creation of a table by design view is a user customized way of making data storage tables.
Each field in the design view of a table corresponds to a column in the datasheet view of a
table.
Designing a table involves:
- Entering unique names of the columns of the table in the “field name” column of the
design view. Names of fields and objects in Microsoft Access can be up to 64
characters long. They can include any combination of letters, numbers, spaces, and
special characters except a period (.), an exclamation point (!), an accent grave (`), and
brackets ([ ]). They also can't begin with leading spaces
- In the Data Type column, declare the type of data that will be entered in that column.
The default is Text; or click in the Data Type column, click the arrow, and select the
data type.
- In the lower portion of the window Field properties, can be further specified. Microsoft
Access allows one to distinguish between two kinds of blank values: Null values and
MS-Access Basic 3
zero-length strings The Required property determines whether users can leave a field
blank, resulting in a Null value. The Allow Zero Length property determines whether
Text or Memo fields can contain a zero-length string.
- Define a Primary Key field (You don't have to define a primary key, but it's usually a
good idea. If you don't define a primary key, Microsoft Access asks if you want Access
to create one for you when you save the table)
- Save the table by a name of your choice, (related to the information the table)
- Finally, close the design view to return to the database window where the columns of
the table that you have just created are displayed .
Note:
To insert new fields within the table, in design view, click in the row below where you want to
add the field, and then click Insert Rows on the toolbar.
To add the field to the end of the table, click in the first blank row.
To change from one view to another, make a selection on the View menu or click the arrow next
to the View button and choose from the list that appears.
To view, enter, and change data easily and directly in a table, create a ‘form’
Creating data entry forms
A form is a type of a database object that is primarily used to enter or display data in a
database. Most forms are bound to one or more tables and queries in the database. A form's
record source refers to the fields in the underlying tables and queries.
A form:
- Focuses on one record at a time
- Can display fields from more than one table
- Can also display pictures and other objects
- Can contain a button that prints, opens other objects, or otherwise automates tasks
Data entry forms can be created either using a form wizard or in the design view. Once a table
with fields is available, it is easy to create a form using the wizard:
o Click on the forms tab
o Double click on the create form by using a wizard.
o This will lead you through a series of steps until you finish creating the form.
The form created is linked to the table and information entered in the fields of the form is stored
in the table.
A form need not contain all the fields from each of the tables or queries that it is based on.
When you open a form, Microsoft Access retrieves the data from one or more tables, and
displays it on the screen with the chosen layout in the Form Wizard, or with the layout that you
created on your own in design view.
Queries
A query is a derived item in the database meant to answer specific questions that relate to the
information in the database. Queries are handy during data processing.
To find and retrieve just the data that meets conditions that you specify, including data from
multiple tables, create a query. A query can also update or delete multiple records at the same
time, and perform predefined or custom calculations on your data.
A query requests data from the database. At its simplest, a query merely fetches all data
from a single table. But as you create more complex (and more typical) queries, you can
assemble exactly the data you want (i.e. unique sets of data that you require at any given time).
Queries can also be used to execute mathematical and logical functions to obtain certain
information in the database.
Queries are derived from and linked to tables or other queries. (Due to these linkages, they
tend to largely inflate the size of the database and should thus only be used to execute the
intended functions, and stored only if updated information is to be retrieved)
There are various types of queries for different uses:
o Select queries- used for extracting specific information from a large multi-information table. They can also be helpful in merging related information from different tables.
o Make-Table queries- used for making sub tables from the main table(s) and queries.
o Update queries- important in adding information in the fields of a Table.
o Append queries- used to copy records from one table/ query to another.
o Delete query- to PERMANENTLY remove unwanted content from the table.
NOTE:
delete query should not be used unless one surely will not require the information to be
deleted.
To run a simple Select query: In the Database window, click Queries under Objects. Click the
query you want to open. Click Open on the Database window toolbar.
Caution: It's a good idea to make a copy of the data you are changing or moving in an action
query, in case you need to restore the data to its original state after running the action query
In Design view,
- Choose the tables or existing queries that contain the fields to use
- Select and drag those fields to a grid (The fields can come from just one table, or from
multiple tables, the fields specified for a query control the data that the query retrieves)
- Specify criteria and other settings, such as whether to sort the results
(
To test a new query in Design view. Just click the Run button on the Query Design toolbar.
This will switch you to the results view.
If you don't see the results you want, click the Design button to return to Design view.
Note:
To stop a query in progress (after you start it), press “CTRL+BREAK”
