Ms-Access



MS-ACCESS

Microsoft Access is a relational database management system (RDBMS) software developed by Microsoft Corporation of America. It is also a part of office packages, which helps to develop the database as your requirement.

Ms-Access has its own "Back_End" and "Front_End". Its file format is [*.mdb].

Object of Ms-Access:

1. Table

2. Query

3. Form

4. Report

5. Macro

6. Pages

7. Modules.

Table:- It is a combination of columns and rows. In Ms-Access you can create up to 255 tables in a single database where each and every table content 256 columns and unlimited rows. It's a man of database. It holds all are records as content.

Query:- It is a conditional selection of database without a table, Query can not be created. It always fetches a record from table as the specific criteria.

Form:- It is also know as a front end of database. It is used to Manipulate the table records by using this you can update ever more then one table at a time, which the help of macro.

Report:- It is used for distribution propose or printing propose.

Macro:- It automate a particular field. It is a set of action, which can be assigned at particular event to the different objects/control.

Pages:- It is used for internet propose.

Modules:- It is a core part Ms-Access programming.

Table: A table has two different view:

1. Datasheet View:

It's the default view of table. In this view you see the actual records stored in table.

2. Design View: In this view, we design the structure of table

Parts of design view

I. Field name: In this column you can give the name of field that you want on table.

II. Data Type: It defined the type of data what you would enter in particular field. It invalid data entered you will be stopped to go forward.

➢ Text : It allows any combination of a-z, 0-9 and some symbols. Its minimum length is 255 characters. Its default length is only 50 characters.

➢ Memo : It is similar to text but lengthy up to 65535 characters.

➢ Number: It accepts only combination of number (0-9) calculation can be done.

➢ Currency: It is similar to number but it also accepts prefix or suffix.

➢ Auto Number: It provides a unique number automatically to the particular field.

➢ Date/Time: It accepts any format of date or time.

➢ Yes/No: It accepts Boolean expressing like yes/no, True/false, male/female, married/unmarried etc.

➢ OLE Object: It stands for object linking embedded. It can store picture, movie, sound etc.

➢ Hyperlink: It is used for URL and E-mail address.

➢ Look up wizard: It is used for look up control in table.

III. Description: In this column you can give additional information regarding the particular column. This information is displayed in status bar in datasheet view.

IV. Field Properties: This part you can change the properties of field as well as bound them by defining different validation rules.

SN Auto Number

Name Text

Address Text

Phone Number

Dob Date/time

Item code Text

Net Amount Currency

Toward Memo

Validation Rajbiraj

Name Field Size

Caption SN , =

➢ Field Size: You can used the field size property to set the maximum size for data stored in a field set to text, number or auto number data type.

➢ Format: You can use these properties to format the text of field value.

Character Discretion

> It used is character to capital letter.

< It used is character to small latter.

➢ Long Format: Tuesday 2 october,2007

Medium date 2 Oct 2007

Short date 2/10/2007

Input mask:- It is use to control the field while entering a date

Character Discretion

0. any single digit (0-9)

9. any single digit (0-9) or space.

# any single digit (0-9) or space.

➢ Caption:- By using this properties you can change the field heading

➢ Default value: It daffiness the default value to the particular field. It automatically inserts the value when new record is generated.

➢ Validation Rule:- You can use this property to make a validation rule for the particular field.

For Example:- "Manager" or "Accountant",

10000 or < 15000

➢ Validation Text:- You can use this properties to display own text or massage when text violets the validation rule.

For Example :- Sorry valid only 10000 to 15000.

➢ Required:-You can set this property to YES if the particular field never contain null value.

➢ Indexed: -

❖ No - No indexing.

❖ Yes (Duplicate Ok) - Indexing but duplicate value also accept.

❖ Yes (No duplicate) – Indexing but duplicate value not allowed.

C/W. Item code

Item Name

Company

Stock BL

Rate

➢ Additional Info: -

✓ Item code: should be followed by 4 digits and character eg: I0001, I0002, I0010.

✓ Item name: must set be large than 10 character and must be in upper case.

✓ Company: name may be "Samsung", "LG", "Nokia" or "Sony".

✓ Stock BL: must not in negative and not larger than 100.

✓ Rate: The minimum rate of Item is 5000.

❖ Empcode: E followed by 3 digits. For Ex. E001, E002, ........etc.

❖ EMP Name: Should be in lower case having 15 characters in length.

❖ DOB: Must greater than 1965 – 1-1 .

❖ DOJ : Must greater than 2000-1-1.

❖ Post : May be manager, Accountant, Assistant, Helper, Labor, driver, Guard.

❖ Phone No: 031- Followed by 6 digits required for eg. 031-521200

❖ Salary: Minimum salary is 2000 and maximum salary is 20000.

Look Up Tab:

Control:

A graphical object that can be placed in table form or report and assigned a bundle (Set) of action such as text box, combo box, list box, command button etc..

Text Box:

A visual basic control that allow to type something or display a string.

Combo Box:

It combine the feature of both text box and list box use a combo box when you want the option of either type a value or selecting a value from a predefined list.

List Box:

A control (VB control) that provides a list of choices. A list box consists of a list and on optional table.

FIELD PROPERTIES:

Luck Up:

I) Display Control:

Select to type of control from given list.

✓ Text box.

✓ Combo box

✓ List box.

II) Row Source Type:

Select the type of source from the given list.

✓ Table/Query

✓ Field list

✓ Value list

III) Row Source:

Define the source address or value for the combo box or list box.

Example: "Manager", "Accountant".

IV) Column count:

It a certain the number of column to be display on combo box.

For Eg.

|Post |

| | | | |

V) Column Heads:

Yes :- To display the heading of column is combo box.

No :- To hide the heading of column is combo box.

For Eg.

|Post | | | |

VI) Column Width:

You can use this property to specify the width of each column in a multiple column combo box or list box. [value in inch]

VII) List Width:

You can use this property to set the maximum number of rows to display in combo box or list box.

VIII) List Rows Properties:

You can used this property to set maximum number of rows to display so that it ascertain the total width of the combo box or list box.

For Eg.

| | |

| | |

| | |

| | |

| | |

| | |

IX) Limit to List :

You can set it to YES so that its (combo) value become limit No other entry are accepted.

Sales Date Number

Customer Name Text

Item code Text Item Id, name, qty, rate

Quantity Number

Rate Number

Amount Currency

Primary Key :

The power of relational database system as Ms-Access comes from its ability to quickly find and bring together information stored is separate table using query, form and report. In order to do this each table should include a field or set of field that uniquely identified each record stored in the table. This information is called the primary key of the table.

Once you design a primary key for a table Access will prevent any duplicate or null value from being interred in the primary key fields. And it also helps to make relationship between tables.

To Set Primary Key:

i) Open the table in design view.

ii) Select the field or fields you want to define as the primary key.

iii) Click on primary key on the toolbar.

iv) Save the table.

Relationship:

An association between two or more than two table are called relationship. There are three kinds of relationship, they are:

i) One to one Relationship.

ii) One to many Relationships.

iii) Many to many Relationships.

Importance of Relationship:

✓ It establishes a relation between two or more table.

✓ It prevent from redundancy.

✓ It defines a field unique.

✓ Record can be fetch (bring) from other table.

I) One to one Relationship:

In this relationship each record in table A can have only one matching record in table B and each record in table B can have only one matching record in table A.

Example: Admission Table Result sheet

Stud Code Stud code

Name English

Address Nepali

Phone Math

II) One to Many Relationship:

In this relationship a record is table A can have many matching records in table B but a record in table B has only one matching record in table A.

Example: Admission Receipt Table

[PM] Student code ( Student code

Name Rec. Date

Address Rec. Amount

Phone

III) Many to many Relationship:

In this relationship a record in table A can have many matching record in table B and table B has many matching records in table C.

Example: Item Table Order Table Customer Table

[PM] Item Code Ord. No. Customer ID

Name Customer ID Name

Quantity Order date Address

Rate Quantity Phone

Item Code E-male

Rate

# To make a relationship between table:

1. First create the both table A and B.

2. Click on relationship tool available on toolbar.

Or, Go to Tools Menu and click on Relationships.

3. Add Both table [A and B]

4. Select the related field of table A and drag and drop to table B on related field.

5. Check the following options:

❖ Enforce Referential Integrity.

❖ Cascades update Related fields.

❖ Cascade Delete Related Records.

6. Click on Ok.

Query: It is condition of database when you make a question to database then the answer is query. Query needs at least a table without a table query is not possible.

# To make a Query:

1. Double click on Create query using wizard.

2. Select the Name of Table and its field which you want on query.

3. Click on Next ( Next ( Finish

# Create a query using Design View:

1. Double click on create query giving Design view.

2. Add a table by which you want to make a query.

3. Click the available field and drag and drop to down at grid.

4. Save the query and run it.

Eg.

Query:

Formula:

Aount = Amt: [Qty]*[Rate]

Dis = Dis: [Amt]*5/100

Net Amount = Net Amt: [amt]-[dis]

Formula for Result sheet

Total mark = Total mark: [English]+[Nepali]+[math]+[Science]…..

Percentage: = Per: [Total mark]/400*100

Result = Result: IIF([English]>=35 and [Nepali]>=35 and [math]>=35 and

[Science] >=35,"Pass","Fail")

Division: = Division: IIF([result]="pass" and [per]>=60, "1st", IIF([result]="pass" and

[per]>=45, "2nd", IIF([result]="pass" and [per]>=35, "3rd","Try again")))

# Find Duplicate query: It is used to find the duplicate records from a table or query.

1. Click on New an query object.

2. Select Find Duplicate Wizard and click on Ok.

3. Select the name of table/query from which you want to keep duplicate or repeated records.

4. Click on Next.

5. Select its field to find duplicate.

6. Click on Next.

7. Again click on Next and then Finish.

# Find unmatched Query: It is used to list those records of table A which is not matched with table B. This query needs two table to compare with.

Table A [ Admission] Table B [Receipt]

Stud Id Stud Id

Name Date

Address Amount

Phone

Steps:

1. Click on New button.

2. Select Find unmatched Query Wizard and click on Ok.

3. Select Table A that has no related records in Table B.

4. Click on Next.

5. Select another Table (Table) which contains the related records.

6. Click on Next.

7. Select the Field of both table which piece of information is in both table and then click on Match too. [< = > ]

8. Click on Next.

9. Give the Name of query and click on finish.

# Update Query: It is used to update or modify the related table.

Steps:

1. Double click on Create Simple Query.

2. Click on Query menu and then click on Update query.

3. Drag and drop those fields which you want to update.

For Ex. Amts

Discount

Net Amts

4. Pass the expression or formula in update to field.

Field:

5. Save the query and run as many times as the number of fields are to be update.

# Delete Query: It is used to delete the records from table based the specified criteria.

1. Design the Simple Query and open it in design view.

2. Pass the criteria or conditional in criteria field.

3. Click on query menu and then click on delete query.

4. Save the query and run it to delete the record which has quantity greater than 100.

# Append Query: It is used to append or insert the records of Table A to Table B.

1. Design a Query Just as select query.

2. Click on Query Menu. and then click on Append Query.

3. Select the name of query in which you want to append.

4. Click on Ok.

5. Save the query and run it.

# Make Table Query: It makes a new table of selected field from existing table. You can create it by Query in the same database or existing database.

1. Double click on Create Query in Design View.

2. Add a table from which you want to pick a field or fields.

3. Drag and drop the required field.

4. Click on Query Menu and then make table query.

5. Enter the name of Table, which will create, on the execution of this Query.

6. Click on Ok.

7. Save the Query and Run It.

FORM: It is a form of database by this you can manipulate records or table entry. You can use form to make an entry that is automatically update to A and form B.

Table

Form

Back

# Creating A Form:

1. Double click on create form by using wizard.

2. Select the name of Table or Query for which you want to make form.

3. Also select the required field of Table or Query which you want on Form.

4. Click on Next.

5. Select a layout of form column, tabulation and click on Next.

6. Select a style of form you like and click on Next.

7. Give the name of form and click on Finish.

Eg:- * Item code

*Result Sheet

Item Name

Quantity

Rate

Amount

Discount

Inserting command buttons in form

1. Turn on the control wizard available on tool box.

2. Click command button tool and insert into form.

As soon as you place a command button you get control wizard box.

3. Select appropriate categories and action you want to happen when the button is pressed.

Categories Actions

Record Navigation Add New Record

Record Operation Delete Record

Form Operation Save Record

Report Operation etc.

Application

Miscellaneous

4. Click on Next.

5. Select Text or picture, which you want on the button. * Add

6. Click on Next.

7. Enter Name of button and the click on Finish.

In the same way insert more buttons in form for eg.

First Previous Next Last Close Exit

# Macro: Macro is a set of action that can be executive at an event or assign it to a control.

Action of Macro [Assume , that Macro is for Amount]

1. Click on New on Macro object.

2. Select on action and enter its expression.

Action Comment

Set value

Item

Item [Forms]![Item]![Amount]

Expression [forms]![item]![rate]* [forms]![item]![qty]

3. Save the Macro.

Eg. McrAmt

4. Now, tag the macro with a control on particular even you want to happen.

5. On got focus

Conditional Macro

1. Click on view menu in Macro Design view.

2. Click on Condition.

|Condition |Action |Comment |

| | | |

Discount calculation:

If Amount > 10000 then 5% otherwise (10000

2. Write the given in Action Set Value

3. In Argument Item [Form ! [Item] ![amt]![dis]

Expression [forms ! [Item]![Amt]*5/100]

4. Save the Macro and set on Got Focus of discount field.

# For Result Calculation:

Condition : [Nepali]>=35 and [English]>=35 and [science]>=35

Action : Set Value

Item : Forms ! [Result sheet]![Result]

Expression : "Pass"

Condition : [Nepali]=60

Action : Set Value

Item: Forms ! [Result sheet]![Division]

Expression : “First”

Condition : [Result]= “pass” and [Per]>=45

Action : Set Value

Item: Forms ! [Result sheet]![Division]

Expression : “Second”

Condition : [Result]= “pass” and [Per]>=35

Action : Set Value

Item: Forms ! [Result sheet]![Division]

Expression : “Third”

Condition : [Result]= “pass” and [Per] ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download