Microsoft-Access 2003

What is MS-Access ?

MS-Access is a database application program under the windows operating system. It is a member of MS-Office Package developed by Microsoft Corp. USA. It is a RDBMS(Relational Database Management System).

Database

A database is collection of related information about a system or an object. A database system, which is used to manage and manipulate the information that is in the database system is known as DBMS(Database Management System). The main object of the database table, which is used to collect and store the data.

Tables

In all types of RDBMS or DBMS system tables are the primary object and store the information. A table stores the data in tabular format with rows and columns.

Parts of a table

1. Fields(Columns): The name of the column, which holds the data, A table can have one or more than one fields.
2. Record(Row): Collection of a data horizontally, which has been organized in a tabular format is known as a record. A record always has related data. In a table there can be more than one row.
3. Data : The actual data this is being stored in the table

Starting MS-Access

1.          click on Start button

2.          Click on Run

3.          Type the Msaccess from the run dialog box.

4.          Click on Ok button

5.          If you want make new database file then select blank database and type the database file name

6.          Click on Create

7.          If you have already made the database file then select that existing database from open a file list.

8.          Then you will see the database window.

Creating a new table

You can create a new table in different way.

1.        Creating a table by using datasheet view.

i.                Click on Table object at database window

ii.                   Click on New …    or Click on Insert --> Table ...

iii.                  Select datasheet view from the dialog box

iv.                 Click on Ok button

Then a data sheet design view will appear.

v.             Rename the field into required field name by click double click or right button click and rename command.

vi.           Save the table and enter the records.

Renaming Field

Steps:

1. Right button click at required field heading

2.        Click on Rename

or,  Click on Format menu --> Click on Rename Column

3.        Type new name and press Enter key

Inserting Columns

Steps

1.        Click on Insert menu

2.        Click on  Column

Creating a new table by using design view

Steps:

1.        Click on Table object at data base window

2.        Click on New…

3.        Select design view from the dialog box

4.        Click on Ok button.

Then a design view window will appear.

5.        Type the required field name and select required data type.

6.        If necessary type the description also

7.        You can also set the field properties for the field.

8.        At last save the table and enter the records.

Data Type

Data type refers what types of data can be store in a particular variables or fields. There 10 different types of data types in MS-Access.

1.        Text: -  Any  written text up to 255 characters in length, number that you won't be using in arithmetic calculations, and certain numbers such as zip codes, phone numbers, or product codes that contain letters, hyphens, or other nonnumeric characters.

2.        Number : - True number which can calculates the values. It contains the up to 19 digits values. You can use the Field Size, Format, and Decimal Places field properties on the General tab to control the size and appearances of numbers.

3.        Currency : - Number data type which contains the dollar amounts.

4.        AutoNumber : - A number that's assigned automatically and never changes again. Use the new values property on the General Tab to control whether numbers are assigned incrementally or randomly.

5.      Date / Time  :- It contains the date and time value. You can use the Format property on the General tab to control the appearance of the date.

6.        Hyperlink : - Hyperlink addresses. It insert the hyper link of the cell text.

7.        Lookup : - Values that come from another table, a query, or a list of values you supply. It can be used to make the drop down list box.

8.        Memo : - It is a character data type. It contains the large bodies of text up to 64,000 characters in length.

9.        OLE Object : - Any OLE object, such as a picture, sound, or word processing document or other application programs.

10.    Yes / No :- A true or false value only. It contains the tick mark for true or false.

Field Properties

You can use the field properties to input the data

Field Size : - Lets you specify the maximum length of text allowed into the field or the acceptable range of numbers. The default size for text is 50 and for numbers is long integer.

Format : - Lets you define the appearance of data in the field. For example, You can show a date as 2/14/1999 or as February 14, 1999

Input Mask : - Lets you define a pattern for entering data into the field. For help with creating the mask for a text or date/time field, click the Build button after selecting this property. The Input Mask Wizard will guide  you through each step.

Caption : - Lets you define an alternative name for the field that will be used in datasheet view and as labels when you create forms and reports.

Decimal Places : - Lets you specify the number of digits to the right of the decimal separator in a numeric field. Choose "Auto" to have the Format property determine the number of decimal places  automatically.

Default Value : Lets you define a value that's automatically inserted into field; you can type a different value during data entry, if necessary.

Validation Rule : - Lets you create an expression that tests data as it comes into the field and rejects faulty entries.

Validation Text : - Defines the error message that will appear on the screen when faulty data is entered into the field. When writing the validation text, it's best to indicate which field is invalid so the user can more easily understand what's wrong during data entry.

Required : - If set to Yes, the field cannot be left blank.

Indexed : - Lets you choose whether to index this field and whether to allow duplicates in the index.

Allow Zero Length : - If Yes, the field will accept an "empty string" as a valid entry, even if the Required property is set to Yes. The empty string will appear as two quotation marks with nothing in between (" ") when first typed into the field; those quotation marks will disappear when you move the cursor to another field.

Input Mask

The following table shows some useful input mask definitions and examples of values you can enter into them.

Input mask definition	Examples of values
"(0977)-"000-000000	(0977)-081-525482
(9999)-999-999999!	(0977)-081-525482
(000) AAA-AAAA	(206) 555-TELE
>L<??????????????	Nepalgunj
00/00/00	01/01/06
00/00/0000	01/01/2006
"Emp-"000	Emp-018
"Rs."99999	Rs.18021

Lookup Wizard

The lookup wizard data type lets you to make the list box or combox box to store the list of the items in a field.

Steps:

1. Type a field and select the lookup wizard
2. Then automatically lookup dialog box will appear.
3. Tick on the 'I will type in the values that I want.'
4. Click Next and type the required  value in Col 1
5. Click on Next --Finish

Inserting Object

You can insert the objects or picture, clip art on the field. First prepare the field with OLE data type.

Steps:

1. Open the table datasheet view.
2. Right button at the cell which contains the object
3. Click on Insert Object
4. Then a dialog box will appear and select required application or click on browse to select files
5. Select files and click on Open

Primary Key

A primary key is a field (or group of fields) that uniquely identifies each record. A primary key  can consist of two or more fields. When two or more fields define a primary key, Access doesn't consider records to be duplicates unless the combined contents of all the fields in the primary key are identical.

Steps

1. Select the field you want to use as a primary key
2. Click the Primary key  from the standard tool bar

Or,

1. Click on Edit -->Click on Primary Key

3. Creating a table from Table Wizard

Table Wizard provides the predefine data base file where different table can be achieves. You can select required fields from these table to create new table.

Steps:

1. Click  on Table Object from the database window
2. Click on New...
3. Select the Table Wizard
4. Click on Ok button.

Then a table wizard will appear

5. Select Business or Personal data base
6. Select Sample Table from the list and then select required fields from Sample Fields list and Click on [ >  ] button for one field and [ >> ]  button for all fields.
7. If you want to rename these fields then Click on Rename Field and type the new name and click on Ok button.
8. Click on Next  and  select the radio button for primary key or not.
9. Click on Next -- > Next and select the radio button to open in design view, or datasheet view for data entry
10. At last click on Finish button

4. Import Table

You can import a table from other database file to access by  using Import Table comand.

Steps:

1. Click on Table object
2. Click on New...
3. Select Import table from the dialog box
4. Click on Ok button.
5. Then a import dialog box will appear.
6. Select required file from drive or folder and click on Import button
7. Select table and click on Ok button.

5. Link Table

The Link Table can be used to link the table from another database file to MS-Access. When you enter the record in the table in access then that record automatically update that linked table which is another table.

Steps

1. Click on Table Object
2. Click on New ...
3. Select Link Table from the dialog box
4. Click on Ok button
5. Select required database file and click on Link button
6. Select required table and click on OK button.

Sorting Records

The sort command lets you to sort the records in ascending or descending order in alphabetically or numerically.

Steps:

1. Select required field to which you want to  sort
2. Click on Record menu
3. Click on Sort --> Select Sort Ascending or Sort Descending

 Or, Select Ascending or Descending icon from standard tool bar.

Filtering Records

The filter command lets you to filter the required records for printing or viewing records.

There are two types of  filters

1. Filter by selection

The Filter by selection command lets you to filter all matching records. It filters the selected records on the field.

Steps:

a.                   Select required record  and click on Filter by selection from  Standard tool bar

Or, Click on Record menu 

      Click on Filter and select the Filter by selection command.

2. Filter by Form : - It lets you to filter the records by form.

Steps:

a.                   Click on Record menuà Filter à Filter by form

Deleting Record

The delete command lets you to delete a record form table in Access.

Steps

1. Select required row or record
2. Click on Delete Icon from standard tool bar
3. Or, Click on Edit menu and click on Delete Record
4. Click on Yes

Deleting Column

Steps

1. Click on Required Field
2. Click on Edit menu
3. Click on Delete Column

Finding Records

The find Record command lets you to find the required record from table or query.

Steps

1. Click on Find button from the Standard tool bar.                                                                                                                                         Or,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

1. Click on Edit menu
2. Click on Find...
3. Type the record and select the required field
4. Then click on Find Next
5. If you want to replace that record then click on Replace and type the new record
6. Click on Replace

Query

A query is a tool for asking questions about data in your tables and for performing actions on data. You can use queries to combine or join data from many separate but related tables.  Query can also  help you  to calculate, change, delete or add large amounts of data in one fell swoop. Finally , you can use queries as the basis for your forms and reports. So query is also called heart of the access.

Creating a query by using design view

Steps:

1. Click on Query object from the database window
2. Click on New ...
3. Select Design view from the dialog box
4. Click on Ok button

Then a select query design view.

5. Select required table from the show table dialog box and click on Add
6. The click on Close button
7. Double click at field name or pick the field and drag the mouse for field: row
8. If you want to give the formula then give the formula also

9. To run the Query

• Click on Query menu
• Click on Run Query

or,

Click on Run Query ( ! ) button at standard tool bar

10. Save the query.

Creating a query by using Simple Query Wizard

Steps:

1. Click on Query object for the database window               
2. Click on New...
3. Select Simple Query Wizard from the dialog box
4. Click on  Ok button

Then a simple query wizard dialog box will appear                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

5. Select required table or query
6. Select field and click on [ > ] button for one field and [>>] button for all fields.
7. Click on Next button
8. Type the title for the query name
9. Tick on required option for query design view or open datasheet view.
10. Click on Finish button.

Formula:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

To find product (total)  : Qty * Rate

To find discount  : total*5/100

To find discount for condition :iif(qty>=10,total*10/100,0)

To find Total Amount          :total-discount

To Find DA (Daily Allowance ) : Salary*5/100

To  Find TA (Travel Allowance ) : Salary * 2/100

To Find HRA (House Rent Allowance) : Salary * 12/100

To Find MA (Medical Allowance) : Salary * 3/100

To Find PF (Providence Found ) : Salary * 10/100

To find SP (Service Period ) : Abs(int((now()-date_of_join)/365))

To find total Salary : (salary+da+ta+ma+hra)-(Pf+tax)

Result Calculate

1. Find Total Marks :  [english]+[math]+[nepali]+[science]+[health]
2. Find Percentage   :        [total]/500*100
3. Find division: IIf([percentage]>=60,"First",IIf([percentage]> =45,"Second",IIf([percentage]>=35,"Third","Failed")))
4. Find result: IIf([english]>=33 And [math]>=33 And [nepali]>=33 And [science]>=33 And [health]>=33,"pass","Fail")

To Find Fine : fine: (([return_date]-[issue_date])-7)*2

To find Payments  : Abs(Pmt([Rate]/100,[Nper],[PV]))

Cross Tab Query Wizard

You use cross tab queries to calculate and restructure data for easier analysis of your data. Cross tab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information one down the left side of the datasheet and another across the top.

Steps:

1. Prepare the table and query also
2. Then click on Query object from the  database window
3. Click on New
4. Select Cross Tab query wizard
5. Click on Ok button

Then a cross tab query wizard dialog box will appear

6. Select required table or query and click on Next
7. Select a field to which you want to put at row heading and Click on Next
8. Select a Field to which you want to put at  column heading and click on Next
9. Select field to which you want to calculate and also select the function
10. Click on Next and type the query name and click on Finish

Select Query

Select query is the most common type of query. It retrieves data from one or more tables and displays the results in a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.

Note : - You have already done the select query from query design view and query wizard.

Action Queries

An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries:

·                     Delete Queries : -      A delete query deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries, you always delete entire records, not just selected fields within records.

Steps:

1.                  Open required qury at design view.

2.                  Click on Query menu

3.                  Click on Delete Query

4.                  Run the query from query menu or standard button

5.                  Click on Yes

·                     Update Queries : -    An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.

Steps:

1.                  Open required query table in design view.

2.                  Click  on Query menu

3.                  Click on Update Queries

4.                  Type the new value or data at required field of Update to row.

5.                  Then run the query from query menu or standard tool bar.

6.                  Click on Yes

·                     Append Queries : - Append query adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information into your own database, you'd like to append it to your Customers table.

Steps:

1.                  Open query table at query design view.

2.                  Click  on Query menu

3.                  Click on Append Query

Then a Append query dialog box will alppear

4.                  Select table name and database file whether the current database or another database if you want to append from another database then click on browse and select the database file.

5.                  Click on Ok  and run the query and click on  Yes

·                     Make-Table Queries : -   A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for creating a table to export to other Microsoft Access databases or a history table that contains old records.

Steps:

1.                  Open query table at query design view.

2.                  Click on Query menu

3.                  Select Make table Query

4.                  Type the new name and click on Ok button

5.                  Run the query and click on Yes.

Parameter Query

Parameter query is a query that when run displays its own dialog box prompting you for information, such as criteria for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Access can then retrieve all records that fall

Steps:

1.                  Open required query table at query design view.

2.                  Click on Query menu and click on Parameter query

3.                  Then type the value and data type at parameter query dialog box.

4.                  Click on Ok and run the query.

Form

Forms let you display and enter data in a convenient format that resembles fill-in-blank forms. Your form can be plain and simple or quite elaborate with graphics, lines, and automatic lookup features that make data entry quick and easy. Forms can even include other forms (called sub form) that let you enter data into several tables at once.

Creating a form by using Design View

Steps:

1. Click on Form Object
2. Click on New...

3. Select Design view
4. Select query or table from drop down list box.
5. Click on Ok button.

Then a form design view window will appear

6. Select field from table and drag them on the form
7. Use tool box for the label, text, insert picture etc.
8. Save the form and Run.

Creating  a Form by using Form Wizard

Steps:

1. Click on Form Object
2. Click on New...
3. Select Form Wizard
4. Select table or query from the drop down list box

Then a form wizard dialog box will appear

5. Select table or query from the drop down list box
6. Select field and click on [>] button for one field and [>>] for all field
7. Click on Next
8. Select required layout of the form and click on Next
9. Select required style of the form and click on Next
10. Type the title of the form and select or tick on whether design view or form view
11. At last click on Finish.

Toolbox

The tool box lets you to select different controls  to draw or place on the form. The tool box controls are given below.

The Property of the Controls

Each tools has own properties. You can format, input mask, set the event etc from the property dialog box.

Steps:

1. Right button click at object which is placed on the form
2. Click on Property...

3. Select required option
4. Specify the other option

Creating a form by using Auto Form(Columnar, Tabular and Datasheet)

 Columnar  Forms :  In a columnar form, each field appears on a separate line with  a label to its left; only one record is shown on each screen. The wizard fills the first column with as many fields  as will fit on a single screen, then it fills the next column with as many fields as will fit, and so forth. ]

Tabular Forms : -  Show the form as a table. As you can see, tabular forms display fields in a horizontal row, with field labels at the top of the form. Each new row represents a new record.

Datasheet View :- A datasheet view initially displays data in datasheet view, much as it appears when you open a table, or run a query, or when you use the Form View toolbar button to switch to datasheet view in any form. This type of form is often used as the basis for sub-forms described in a moment.

Hierarchical Form :- Sometimes you will want to work with related tables in your forms. For example, you might want to design an order form that includes customer and order information, along with details about the products ordered. A hierarchical form showing data form tables that have a one-to-many relationship is perfect for jobs like this.

This form wizards can create hierarchical forms in two basic flavors: a main form and sub-forms, or a main form and linked forms. ( A sub-form is a separate form that's embedded in a main form.).

Steps:

1. Click on Form Object
2. Click on New...
3. Select Auto Form : Columnar, Tabular or Datasheet view.
4. Select required table or query from the drop down list box.
5. Click on Ok button.

Then automatically created a  form

Creating a form by using Auto From (Pivot Table)

A pivot table lets you summarize large amounts of  data, much as a Cross tab query does. But a Pivot Table is more flexible than a Cross tab query because it lets you interactively switch the row labels, column labels, and summary calculations as needed.

Steps:

1. Click on Form object
2. Click on New...
3. Select Auto Form Pivot Table
4. Select table or query from the drop down list box
5. Click on Ok button
6. Then a pivot table form will appear.
7. Place the fields in row  and column heading .

You can also create a pivot table form by using Wizard

1. Click on Form object
2. Click on New ...
3. Select Pivot Table Wizard
4. Select required table or query from the dialog box
5. Click on Ok button.
6. Click on Next
7. Select field or fields and click on [>] button for one record and [>>] for all the records.
8. At last click on Finish button.

Creating a Chart

Charts convert the numbers in your data to useful graphs that help to clarify the meaning of those numbers. You can create chart in two ways:

Step 1.

1. Click on Form object from the database window
2. Click on New...
3. Click on Auto Form : PivotChart
4. Select the table or query to which you want to crate a chart.
5. Click on OK button.
6. Place the fields in chart document.

Step 2.

1. Click on Form  object from the database window
2. Click on New..
3. Select Chart Wizard and select the table or query form the drop down list box.
4. Click on Ok button

Changing the Style of a Form

Suppose you used the form wizards to create a form that has the Ricepaper or international style, and now you decide that the Standard style would look better. You needn't re-create the form to change its style. Just folow these steps:

1. Open the form in design view
2. Click on Format menu
3. Click on Auto Format
4. Select required style and Click on Ok button.

.0

Report

Reports let you print or preview data in a useful format. Like forms, reports can be plain or fancy. Examples include mailing labels, lists, envelopes, form letters, and invoices. Reports also can present query results in an easy-to-understand format. For instance, you can print sales by customer, receivables aging, and other management information for use in making business decisions. Reports are the read only format.

Creating report by using design view

Steps:

1. Click on Report object from the database window
2. Click on New...
3. Select Design View
4. Select table or query from the drop down list box
5. Click on Ok button

Then a Report design view window will appear.

Report Sections :- Report sections similar to those in forms. But reports also can have grouping sections, which allow you to break data into separate groups. Each group can have its own header and footer.

Report Header : - Printed once at the beginning of the report (Example, a cover page).

Page header : Printed at the top of each page.

group

Detail : - Printed once for each record in the underlying table or query.

Page Footer :- Printed at the bottom of each page

Report Footer :- Printed once at the end of the report. Often used to display grand totals and the end of a report that includes subtotals.

Page: -   The Page object lets you to design or make a pages for the web page. It generates page, form automatically without using any piece of code.

Steps for creating page

1. Click on Page object
2. Click on New…
3. Select the design view
4. Click on Ok button

It also displays a design form just like the form of report and form.

Design as you have done in form design and report design.

5. At last save your page in your folder in require drive.
6. To run this page open internet explorer.

Macro : - The macro object can be used to create the command for any action. For example, opening  table, query, report, message box, quite the program, maximize window, minimize window, linking and opening any applications etc. After creating a macro which can be called in form object.

Steps:

1.                  Click on Macro Object

2.                  Click on New

3.                  Then select the required command from the action list.

4.                  Then specify the required option and save the macro.

5.                  Open the macro for action or call the macro at command button or toggle button at form object.