MS Access is a powerful database management system that functions in the windows environment and allows to create and process data in a database.Some of the key features are:
Data entry and update:Access provides easy mechanisms for adding,changing and deleting data,including the ability to make mass changes in a single operation.
Queries:Using Access,allows to ask complex questions concerning data in the database very easily and receive instant answers.
Forms:Access helps to produce attractive and useful forms for viewing and updating data.
Reports:Access contains a feature to create sophisticated reports easily for presenting data.
Database is a collection of data organized in a manner that allows access,retrieval and use of that data.Access allows to add,change and delete data in the database.
A database consists of a collection of tables in Access.The rows in the tables are called records.A field contains a specific piece of information within the record.
Click on Start button.
Click on All Programs.
Click on MS Office.
Click on MS Access.
Understanding Access Window
Title bar:It shows the name of the displayed database.
File tab:It displays file commands as New and Open.
Quick Access Toolbar:It displays quick access buttons to Save,Undo and Redo commands.
Status bar:displays information about the current object or view.
Create a New Table
By entering data into a table,you can start a database.Tables consist of columns and rows that intersect to form cells for holding data.Each row is considered to be a record in a table.You can use columns to hold fields in a table.
Click on create tab on the ribbon.
Click on table button.
Access opens a new table in datasheet view.
Double-click the column header to create a field name.
Type a name for the field.
Press Enter key from keyboard.
Click on next column.
Select the data type for the field.Eg. select Text,if you want to add Name field.
If you want to change the data type,click on the arrow button andame column.
change the data type according to your need.
After selecting the data type,its properties will be displayed in the lower pane.
After selecting the data type,press the TAB key to move the insertion point to the description column.
Press TAB key again to move to the Field Name column in the second row.
Type the text in field name column.
Press the TAB key to move to the Data Type column.
The word ‘Text’ is currently displayed in that field.
Press the TAB key to move the insertion point to Description column,if you want the data type as text.
Type the text for the description column.
Repeat steps to make the remaining entries in the table to complete it.
Setting a Primary Key
A primary key is a key that differentiates the records in a file.The data stored in a key field contains data that is unique to a specific record.A student record,example,would use Roll number as a key field because it uniquely identifies each student.
In each new table that you create,you’ll want to set one field as the primary key.Access uses this key to relate this table’s records to those in another table.
Choose the field that you want to set as the primary key.
Click on the primary key button.
The field will be set as the primary key,indicating by a small key in the field of selector column.To remove it,select the primary key field and click on the primary key button again.
Save a Table
After creating a new table,it must be saved to make it a permanent part of the database.
Right click on the table’s tab.
Click on Save.
Type a name for the table.
Click on OK.
The table remains open,and its new name appears on its tab.
Insert and Delete fields
New fields can be inserted and existing removed into a field list.A new row appears in the grid-above the one selected.
Select the field where you want new field should appear above.
Click on design tab.
Click on insert rows.
A new row appears above the selected row.
Type a field name and choose a field type.
Select a field you want to delete.
Click on design tab.
Click on delete rows.
The row will be deleted,along with any data that the fields have.
Understanding Data Type
Each field has a data type that defines what you can store in it.Data entry is restricted to valid entries for the type you choose,which helps to prevent data entry errors.Example,you cannot enter letters in a field set to Number,and you must enter valid dates or times in a date/time field.
Text:This is a general purpose field containing any data.It has a limit of 255 characters and cannot be used for numeric calculation.
Memo:This has a limit of 63,999 characters;used for detailed,descriptive fields.
Number:This type stores numeric data that you can use in calculations.It can also hold symbols,as decimal points and commas.
Date/Time:This type only stores numbers representing valid dates and times.
Currency:Stores currency data that you can use in calculations.
Auto Number:stores a sequencial number for each record.
Yes/No:The value -1 represents Yes,and the value 0 represents No,but the field can be formatted to display values as
True/False or Yes/No.
OLE Object:stores objects created in another application-as Word or Excel-that you can link to or fix in an Access table.
Hyperlink:You can link to websites,email addresses,files on your computer,files on any other location.
Attachment:You can attach data files.
Change Data Type
The data type of a field can be changed anytime to better represent data.Field types should be set before entering data into table,field type can be changed anytime.
Click on the down arrow to ope the Data Type list for the field.
Click on new type.
To save the changes to the table,click on save.
Click on yes to allow the deletion of records that violate the new field type’s rules.
No can be clicked to abandon the change.
Rename a table
A table can be renamed at any time.Access automatically updates all references to the table throughout the database.The table must be closed in order to rename it.
Right click on the table name in the navigation pane.A short cut menu appears.
Choose rename from the shortcut menu.The table name appears in edit mode.
Type the new name for the table and press enter key from the keyboard.
The new name appears on the table.
Click on the table name in the navigation pane.
Click on Home tab.
Click on delete button from record group.
A confirmation dialog box opens.
The table will delete.
Adding Records to a table
The first step is to create a table by building the structure and saving the table.The second step is to add records to the table.The table must be open so that the records can be added to the table.The table displays in datasheet view.In datasheet view,the table is represented as a collection of rows and columns called a datasheet.
Double click on table in the customer:database window.
Type the customer id in the first customer id field.
Press the tab key to complete the entry for the customerid field.
Type the following entries by pressing the tab key after each one to complete the record.
After typing the last entry,press the tab key.
The insertion point comes to the customerid field in the second row.
Add the remaining records by following the same steps you used to add first record.
Click on save,to save the changes.