Learning Center
Plans & pricing Sign in
Sign Out



  • pg 1
									                                                                     MICROSOFT ACCESS 2000

                            Microsoft   ACCESS 2000
                                         in easy steps

       Microsoft access is a powerful windows-based relational database management
system (RDBMS), which you can use to create and modify database tables, data entry
forms, reports and queries (customized requests for information from one or more

Definition of database
       A database is a collection of data as well as programs required to manage that
data. The data is stored in a database in a tabular format, across rows and columns in
someway as in a spreadsheet.
       A database management system takes care of complex calculations, sorting and
other tasks that perhaps could not be performed using spreadsheet.

Relational database management system (RDBMS)
       A database management system is software that helps in creating and
maintaining a database. A database management system built on the concept of the
relational model is called a 'Relational database management system'. An RDBMS
ensures that data is stored efficiently in the database and also easily retrievable. A
relational database is made up of 'relations' or 'tables'.

Database window
       A database window appears whenever you wish to create a database or open an
existing one. It always displays on its title bar the name of the open database.

Objects of relational database
       MS-Access is based on RDBMS. Therefore, to have a better understanding of
working in access, we should familiar with four objects of RDBMS as described
below: -
   1. Tables
   2. Queries
   3. Forms
   4. Reports

Starting with MS-Access

                                        SS COMPUTECH                                63
                                                                     MICROSOFT ACCESS 2000

   1. Move to the start button and select programs option.
   2. From programs, select MS-Access program.
   3. Immediately, a Microsoft Access window opens on the screen. Inside the
       window, you will see another box. This box asks whether you wish to create a
       new database or open an existing one.
   4. Select "Blank Database" and click OK.
   5. Finally, a new database opens 'db1.mdb' file, which is the default file name of
   6. In the file name box, type the name of the database.
   7. Click on the create button.
   8. A database box appears whenever you wish to create a database. This box is
       the control centre of your database. You can use the Database box to open the
       objects contained within a database, i.e., Tables, Queries, Forms, Reports,
       Macros and Modules.

       Table is a collection of information on a specific topic. In a table, the
information is stored in rows and columns.
   1. Field: Each column in table represents a field. A field stores only a specific
       category of information.
   2. Record: Each row in a table is called a record and it consists of a number of
       related fields. Each field contains some bits of data about the record.
There are two different types of view available in TABLE object.
They are: - 1. Design View 2. Datasheet View.

Data Types
       Since there are different types of data items, we have to define each data item
separately, indicating the type of data we would like to store the field name for it. All
the different types of data items and corresponding field names have to be defined at
the time of creating the table the table design view.

The following are the different types of data types
1. Text: Stores 255 characters of alphanumeric data. The default size of text data type
   is 50 characters.

                                    SS COMPUTECH                                    64
                                                                       MICROSOFT ACCESS 2000

2. Memo: Stores data that exceeds 255 characters. This data type is allows the user to
   store up to 64,000 characters.
3. Number: Stores numeric data that is to be used in calculations later. The default
   number data type is long integer.
4. Date/Time: Stores the date or time
5. Currency: Stores currency values.
6. Auto number: Automatically generates serial or random numbers (the numbers
   never repeat).
7. Yes/No: Stores a logical or Boolean value of Yes/No or True/False.
8. Hyperlink: Used to store URLs, email addresses or links to other files on the
9. Ole object: Stores pictures, photographs of employees, Word, Excel or other such

Field properties
          In RDBMS, some characteristics or "properties" can be assigned fields and
tables. is entered as well as stored.
1. Field size: Defines the size of the fields.
2. Format: Formats the field properties according to its data types.
3. Input mask: This property is used to control the values that can be entered by a user
in a field. It provides a pattern or mask for the data that being entered into a field.
4. Caption: User can define caption of the fields, which is not valid for any other
calculations or performances.
5. Default value: User can set default value, which fields will contain before entering
the data.
6. Validation Rule: When the data to be entered into a field has to be accepted,
provided it meets certain conditions, the condition has to be specified under this
7. Validation text: The message to be displayed in case the condition specified in the
'Validation Rule' is not met, may be specified here.
8. Required: You can use the required property to specify whether a value is required
in a field. If this property is set to yes, when you enter data in a record you must enter
a value in the field and the value cannot be null.

                                        SS COMPUTECH                                  65
                                                                       MICROSOFT ACCESS 2000

9 Indexed: The indexed property uses the following settings: -
No (Default) No index.
Yes (Duplicates OK) the index allows duplicates.
Yes (No Duplicates) the index doesn't allow duplicates

Enforcing constraints
        A constraint is a limitation or restriction that is enforced on fields for verifying
data that is being entered into a table.
1.Primary key constraint
        A primary key constraint is one, which ensures that the user dose not enters the
duplicate values in the field on which this constraint is enforced.
2.Check constraint
        Users can set some validation rules or check constraints that check the validity
of the data entering the database. These include conditions that are placed on the data
being entered into the table. There are two types of check constraints - one that is
called a field-level check constraint.
3.Not null constraint
        This constraint is used to ensure that the user does not enter a 'NULL, value
into a field. A 'NULL' value is best defined as the absence of any value in field in a
Field this constraint can therefore, be used to ensure that the user is forced to enter a
value into some field in a table.

Sub data sheet
       Sub data sheet is a datasheet and that is nested within another datasheet and
that contains data related or joined to the first data sheet. You can add sub datasheets
to a table or query in a Microsoft access database, or a form by using a sub form in an
access database or Microsoft access project.

Updating tables
Adding a field
    Select insert menu.
    Inside the pull down menu click on the column option.
       You will notice that a new field, Field1 will be appeared on your datasheet.
Deleting a field
    Select the field, which you want to delete from the datasheet.

                                      SS COMPUTECH                                    66
                                                                   MICROSOFT ACCESS 2000

    Then click on the delete column option from the Edit menu.
    Immediately the selected field will be deleted from the datasheet.
Changing column width
The Standard width of the field is 15.6667.
      To change the width of first you have to select the column width option from
         the format menu.
      When you click on the column width option the column width box appears.
         You can specify the width of the column in column width window.
      Click OK after measuring the width
Changing row height
The standard height of the row in MS-Access table is 12.75. You can change the
height of these rows as per your requirement.
      Click on the format menu.
      Select and click on the row height option.
      Enter the height of the row in the row height box.
      Then click ok. You can notice that height of the row is changed.

       Relationship is an association between common field (columns) in two tables.
A relation can be One to one, one to many or many to many.
The following steps have to be followed to create a relationship between the tables.
      Select relationship option from tools menu. Immediately the relationship
       window and insert table box appears on the screen.
      Add the tables from the insert table box to the relationship window.
      Drag the common field, from one table to another table. Finally the relationship
       is created between the tables.

To delete the relationship the following steps have to be followed
      Close any tables that you have opened. You can‟t modify or delete
       relationships between open tables.
      If you haven‟t already done so, switch to the database window or you can press
       Ctrl+W to switch to the Database window from any other window.
      Select the Tools menu, and then select Relationship option to open the
       Relationships window.

                                    SS COMPUTECH                                  67
                                                                       MICROSOFT ACCESS 2000

        If you want to delete the relationship, select the relationship line and then press
         DELETE key.

         A set of criteria applied to data in order to show a subset of the data or to sort
the data. In Microsoft access, you can use a variety filter records such as 1. Filter by
selection 2. Filter by form 3. Filter by excluding selection 4. Advanced filter

         Queries are a systematic way of retrieving information from a database.
Access's Query feature can look for the information that meets the criteria or condition
you specify and displays the information on the screen. This information will be
displayed in a simple format.
There are two different types of view available in Querries object.
They are: - 1. Design View 2. Datasheet View.
Performing various calculations using queries
   1. Create and design the Table in your database file.
   2. Select Queries option from the Database window and click OK.
   3. Immediately Show Table box appears. From that select the table and add to the
   4. In the queries design view, select the table and fields.
   5. Then give the Query i.e., addition, subtraction, multiplication or division etc.,
What is an SQL query and when would you use one?
         An SQL query is a query you create using an SQL statement. Examples of
SQL-specific queries are the union query, pass-through query, data-definition query,
and sub query.
         In general, every RDBMS uses a language called „SQL‟ (an acronym for
„Structured Query Language‟) for “requesting” and retrieving this information from
the database. However every RDBS has its own dialect or flavour of SQL. The SQL
that is native to MS-Access is called MS-Access SQL, and is different from the SQL
that an Oracle database understands, or the SQL that is used to interact with an SQL
server database.


                                      SS COMPUTECH                                    68
                                                                    MICROSOFT ACCESS 2000

       Forms present the information stored in a database in an easy to use format.
Through Forms, you can view, enter or update information in a database. If you wish
to view records in your database, Forms present one record at a time on the screen.
But if you wish to enter or update information in your database, Forms become a well-
designed data entry form.
Creating the Form using the Form-Wizard
   1. Create and design the Table in your database file.
   2. Select create form by using wizard.
   3. From the Form wizard, select tables or queries and select the fields.
   4. Click finish button.

       Reports provide the most flexible way of viewing and printing information in
your database. They enable, you to display detailed information in almost any format.
Like queries, reports also take information from other objects of your database, i.e.,
tables or queries and then organize that information according to your instructions.
Creating the Reports using the Report-Wizard
   1. Create and design the Table in your database file.
   2. Select create reports by using wizard.
   3. From the Report wizard, select tables or queries and select the fields.
   4. Click finish button.

                                    SS COMPUTECH                                   69

To top