Microsoft Access

 

INTRODUCTION

Ms Access is a RDBMS (relational database management system) under windows. It is used to store and manipulate large amounts of data. The data is stored in multiple tables and access allows us to set up relationship between the tables to facilitate data retrieval.

 

Ms Access is an object oriented program i.e. everything in Ms Access is an object. Each object has properties that defines how it appears and what work it performs. Ms- Access has a number of components out of which the most important component is the table object. Tables are objects that hold the data and all related objects such as Queries, Forms and Report that are used to manage this data in tables.

Database

 A database is a collection of data related to a particular subject or purpose, such as tracking customer orders or manipulating a music collection. Using Ms Access you can manage all your data from a single database file. The database will have an extension name. MDB. with in the database file, divide your data into separate storage containers called table. It is possible to view, add and update the table data using online forms find and retrieve just the data you want using queries and analyze or print data in a specific layout using reports.

Relational databases

Ms Access is a relational databases management system. In a relational database the tables are related on common fields known as key fields. This helps in accessing data from more than one table. The relation ships may be defined at the time of table creation or at a later stage. Usually it is done right in the beginning when planning the database design.

Key fields

In Ms- Access data from more than one table can be related on a common field called key field. The key fields link the data in the two tables. Ms Access senses this relationship by comparing the key fields and if they match performs an automatic linking. Such an automatic linking is called AUTO_JOIN. Mainly there are two types of keys

i)                Primary key

ii)              Foreign key

 

Primary key

Primary key is a rule, which ensures that unique data is entered for a field and the field is not left blank (null). This makes the record unique. For example in a Bank master file, there will be only on account number for one customer, student roll numbers, receipt numbers etc. There are three types of primary key.

i)                Auto number primary key

ii)              Single field primary key

iii)            Multiple field primary key.

Foreign key

 In order to manage the data we distribute the data in many tables. The main table is referred to as the master table and the lower level table in which the related data is stored is called the transaction table. The two tables are related and must be linked based on a field, which is common. This common key field in the transaction table is called the foreign key and its values depend on the primary key values of the master tables.

Elements of a database

The important database objects are Tables, Queries, Forms, Reports, Macros and Modules.

Tables

A table is a collection of data about a specific topic, such as products, students or suppliers. Using a separate table for each topic means you store that data only once, which makes your database more efficient and reduces data- entry errors. Tables organize data into columns (called fields) and rows (called records). Ms Access has a table datasheet view, where in you can add, edit or view the data in a table. You can also check the spelling and print your table’s data, filter or sort records, change the datasheet’s appearance, or change the table’s structure by adding or deleting columns. In the Ms Access, table Design view you can create an entire table from scratch, or add, delete, or customize an existing table’s fields.

Queries

You use queries to ask questions about your data. Queries are also used to view, change and analyze this data in different ways. Queries can be used as the source of records for forms and reports. The most common type of query is a select query. A select query retrieves data from one or more tables using criteria you specify, and then displays it in the order you want. You can create a query with a wizard or scratch in query design view. In design view, you specify the data you want to work with by adding the tables or queries that contain the data, and then by filling in the design grid.

Forms

When a table is opened, it gets presented in a datasheet view. In datasheet view data is presented in rows and columns. In most cases it is better to use the form to enter or modify data. Developing a database form from scratch is a tedious task. Ms Access has a form wizard with which a form can be created after answering a number of questions about what you want to see. Columnar auto form creates a form with one record per page and fields arranged in columns.

Reports

Reports are another type of object used in Ms- Access to view and to print data. Reports can present data from a single table as well as more than one table. The difference between forms and reports is that forms are primarily used to edit or view data on your computer’s screen. When you move through form, you usually navigate from one record to another, reports on the other hand can be previewed screen as well but their main purpose is to present information in a neatly printed page.

Field types in Ms Access

1)               Text- text data type is used for text or combination of text and numbers such as addresses. Also numbers that do not require calculations, such as phone numbers, postal codes. The size is upto 255 characters. To control the maximum number of characters that can be entered, set the field size property.

2)               Memo- memo data type is used for lengthy text and numbers, such as notes  or descriptions. The size is upto 64000 characters.

3)               Number- numeric data to be used for mathematical calculations, except calculations involving money. Set the specific number type.

4)               Date / Time- date and time data type is used for dates and time. The size is up to 8 bytes.

5)               Currency- this data type is used for currency values. Use the currency data type to prevent rounding off during calculations. Accurate 15 digits to the left of the decimal point and 4 digits to the right.

6)               Auto number- this data type is used for unique sequential (incrementing by 1) or random numbers automatically inserted when a record is added.

7)               Yes/no- this data type is used for fields that will contain only one of two values, such as yes/ no, true/ false, on/ off.

8)               OLE object- objects (such as Microsoft word, excel spreadsheets, pictures, sounds or other binary data) created in other programs using the OLE protocol, that can be linked to or embedded in Microsoft access table.

9)               Look up wizard- this data types creates a field that allows you to choose a value from another table or from a list of values using a combo box. Choosing this option in the data type list starts as wizard to define this for you.

Properties

 

Format:- you can use the format property to customize the way numbers, dates, times, and text are displayed and printed. For example, if you have created a price text box, you can set its format property to currency and its decimal places property to 2 or auto.

Input mask:- you can use the input mask property to make data entry easier and to control the values a user can enter in a text box control. You could create a input mask for a phone numbers fields that exactly how to enter a new number. It is often easier to use the input mask wizard to set the property.

Default value:- you can use the default value property to specify a value that is automatically entered in a field when a new record is created.

Validation rule:-you can use the validation rule property to specify requirements for data entered into a record, field, or control. When data that is entered violates the validation rule setting, you can use the validation text property to specify the message to be displayed to the user. Use the validation rule and validation text properties don’t  apply to check box, option button, or toggle button controls when they are in an option group. They apply only to the option group itself.

Enter an expression for the validation rule property setting and text for the validation text property setting. The maximum length for the validation rule property setting is 2048 characters. The maximum length for the validation text property setting is 255 characters.

 

<> 0                                                            Entry must be a nonzero value.

> 1000 Or Is Null                                           Entry must be blank or greater than

                                                                                1000.

Like "A????"                                                 Entry must be 5 characters and

                                                                                 begin with the letter "A".

>= #1/1/96# And <#1/1/97#                             Entry must be a date in 1996.

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 or in any control bound to the field, and the value cannot be null . for example you might want to be sure that a last name control has a value for each record. When you want to permit Null values in a field , you must not only set the required property to no but, if there is a validation rule property setting , it must also explicitly state “ validation rule or is null’. The required property does not apply to auto number fields.

Creating a tables

In order to create a table, we must first of al design the table i.e. the field that must be present in the table and the type of data that must be stored in those fields.

Creating a table using design view

1)     Open the database in which the table is to be created.

2)     The database window is displayed with tabs to create different databases objects.

3)      Click on the table tab and click on new. A dialog box is open.

4)     Select design view and click on ok. A table structure is displayed.

Type the field structure. The table structure contains field name, data types of the field and the description and the field properties. The top pane displays the properties of the field selected in the top pane. Field name can be upto 64 characters long. The structure that you created must be saved. To store the data in a table first open the database in which the table is stored. Click on the table name, click on open. Then the datasheet window will be displayed one the screen to enter the details. After typing the details in a table close the datasheet window by selecting close button. The data is now stored in the table.

 

Creating the table in datasheet view

A new table can be created by datasheet view also

1)     open the database in which the table is to be created.

2)     The database window is displayed with tabs to create different database objects.

3)     Click on the table tab and click on new. The new table dialog box is displayed.

4)     Select datasheet view and click on ok. The table structure is displayed.

5)     Double click on the title field1. it is highlighted and you can type the field name you need

 

Creating the table in table wizard

 

Ms access allows you to create a table easily and quickly by using table wizard. Ms access includes numerous table templates that you can use to create both business and personal database tables. The table wizard can be a great time saver when you are creating tables. It helps you to

1)      Choose a table from list of business or personal samples.

2)      Select fields for a table.

3)      Rename the fields and change their order.

To start the table wizard

1)      Select the tables tab in the database window and then click new.

2)      Double click table wizard in the new table dialog box.

3)      Select business or personal.

4)      Now you choose the specific fields for your table. You can easily modify the sample tables by selecting only certain fields or by renaming the fields to more appropriate for your needs.

5)      Finally you name your table and determine how to set a primary key.

MODIFICATIONS TO TABLE DESIGN

Inserting a field

i)                Click on the row beneath where you want to add a field.

ii)              Click on insert rows button.

iii)            Type the name of the field in the blank row.

Deleting a field

i)                Click on the field you want to delete.

ii)              Click on delete button.

iii)            A warning will appear, click yes.

Renaming a field

    Select the field name you want to change and enter the new name.

 

Moving the field

Click on the field row that you want to move and drag to the new location.

Changing the data type

i)                Click on the field whose data type you want to change.

ii)              Scroll on the down arrow in data type column.

iii)            Click on the new data type

 

Freezing columns

When there are many fields in a table, all fields cannot be displayed at a time in the datasheet window. But for the purpose of displaying the content of any field always on the screen, even when scrolled to the right, the fields can be frozen. To freeze a column, click on the column which is to be frozen and select freeze columns form format menu. To unfreeze the columns select unfreeze all columns from format menu.

 

Hiding columns

By default, the datasheet window displays all fields’ contents in different columns. If particular field content is not to be displayed that column can be hidden. To hide a column, click on the column and select hide column from format menu. To unhide the column, which are hidden, select show columns from format menu.

 

QUERIES

Queries are used to retrieve and display specific records in a table. They can also be used to display specific fields of specific records, which meet the condition specified. A query displays a subset of als or specific fields and records in a table. When a change is made to the data, which is displayed by the query, this change is reflected in the table also. Because of this dynamic relationship between the query and the tables, the subset of the data displayed by the query is called DYNASET.

 

Create a select query with one table

 

Let us start with a simple that creates a query to how selected fields and records from one table.

1)     In the database window click on the query tab and click on new to open the new query dialog box.

2)     Double click on design view or click on ok.

3)     From the table list , select the table, click on add to add the table to the tables list and then click on close.

4)     You will see a query window.

5)     Drag the fields from the field list to the columns in the bottom half of the window in which ever order that you would want to see them.

APPEND QUERY

It is easy to append records from one table to another using an append query. There is no need for the tables to have the same number of fields or same field names. However the two tables must have matching fields and if the table contains a primary key then data to be appended must not have similar values for the primary key fields.

 

UPDATE QUERY

Update query can be used to make global changes to a table instead of using edit, replace, or change one record at a time.

 

DELETE QUERY

A collection of query can be deleted by using a delete query.

 

CALCULATE QUERY

When you create a select query, you can add your own fields to the query grid to show calculate values. For example in the salary table you could create a field Ta that calculates 10% of the basic salary.

i)                click on the queries tab in the  database window and select new.

ii)              From the dropdown list of tables select salary.

iii)            Drag the asterisk (*) from the field list window to the first column of the query grid. This will ensure that all fields of the salary table are listed when this query is run.

iv)             In the second column of the query grid types as in below.

Ta: [basic]*10/100

Third column=da: [basic]*20/100

Fourth column=total: [basic] + [ta] + [da]

 

Save and run the query.

QUERRY PARAMETER

 

The condition specified for the query is constant. Suppose the condition must be entered at run time i.e. during the execution of the query, then query parameters must be specified. If a query parameter is entered, then while executing a query, it will ask the user to enter a value based on which the result will be displayed.

      For example, if the user wishes to view the students of different cities then instead of typing different city names every time in the design view and then executing the query. We can set a parameter. Select the city field and in the criteria, enter the message [enter city]. The square bracket is required. Now execute the query. Every time when executing the query, it will ask the user to enter city.

 

CREATE A FORM IN DESIGN VIEW

1)     to create a form in design view , open database which has been created earlier and than selecting forms form objects bar and clicking on new.

2)     A new form dialog box will appear. Select design view and table, which has been created earlier. Form layout screen appears.

3)     Select all fields from the table box by highlighting them and drag them onto the form area.

4)     Now , we will use the label from toolbox to give a title to the form . set the font size and text alignment from the property . Right click on the label control and select property.

5)     If you want to display the current date in the form, select the text box control from the toolbox and drag and drop it to the form. The text box control will have a label and the box itself.

6)     Click on the label and change the content to date. Right click on text box control and select properties.

7)     In the properties box , type =datevalue(now()) in control source and change the format to longdate.

8)     Switch to form view and run the form.

CREATE A REPORT IN DESIGN VIEW

To create a report using design view , open database, which  has been created earlier and then selecting reports from objects bar and clicking on new.

1)     A new report dialog box appears. Select design view and a table, which has been created earlier. Report layout screen appears.

2)     Select all the fields by highlighting them and drag them onto the report area.

3)     Now we will use the label from toolbox to give a title to the report. Select label from toolbox. Type a title and set the font size and style and alignment from properties by right clicking on the control.

4)     If you want to display the current date in the form, select the text box control from the toolbox and drag and drop it to the report. The text box control will have a label and the box itself. Click on the label and change the content to date. Right click on the text box portion and select properties.

5)     In the properties box, type = datevalue (now ()) in control source and change the format to long date.

6)     Using label, type the page footer.

7)     Switch to print preview. Now report is complete.

 

PRINTING A REPORT

To print a report, perform the following steps.

i)                Select the report in the database window, or open the report in design view print preview.

ii)              On the file menu, click print.

iii)            Enter the settings you want in the print dialog box.

iv)             Under printer, specify a printer.

v)               Under print range specify all pages or range of pages.

vi)             Under copies, specify the number of copies.

vii)           Click ok.