Ms Access Notes ( Microsoft Access Notes )

How to open the MS-Access?
Ans. To open the MS-Access, we have to perform the following steps:
...Go to the Start option and open the All Programs menu
... From Programs menu, go to the MS-Office (software package) option and open the MS-Office Access

Project-1: Create a Table by using the Design view option.
Field name & Data type:

Field Name
Data Type

ID
Auto Number

Name
Text

Father’s_Name
Text

Address
Memo

Phone_no
Text or Number

Date_of_Birth
Date/Time

Total_Marks
Number

Course_fees
Currency

Steps: The following steps are required to create the Table in the Design view option:
... Go to the File menu of the MS-Access and select the New option
... Open the Blank database from the New option
... Create the Blank database with a particular name in a particular place
... Now go to the Table (under objects) of the created database
... To open the Design view option, enter/double click on this option
.... Input the field names and data types in the Design view option
... Go to the File menu and select the Save option to save the created Table with a particular name
... Click the Ok button and select the No (there is no Primary key defined) option
... Close the Design view option and open the created Table to input the data

Table name: STUDENT

Project-2: Create a Table by using the Wizard option.
Field name & Data type:

Field Name
Data Type

Customer_ID
Auto Number

Company_Name
Text

First_Name
Text

Last_Name
Text

Billing_Address
Text

Phone_Number
Text

Steps: The following steps are required to create the Table by using the Wizard option:
... Go to the File menu and open the Blank database from the New option
... Create the Blank database with a particular name and go to the Table (under objects)
... To open the Wizard option, enter/double click on the ‘Create a table by using wizard’ option
... Select the particular table category (e.g. Business/Personal) and the particular sample Table
... Then choose the sample fields and click the Next option
... Give a particular Table name and click the Next option
... Finish

Table category: BUSINESS
Table name: CUSTOMER

Project-3: Create a Table by entering data.
Field name & Data type:

Field Name
Data Type

ID
Number

First_Name
Text

Last_Name
Text

Department
Text

Basic
Number

Steps: To create the Table by entering data, we require some steps as follows:
... Go to the Table (under objects)
... To open the ‘Create table by entering data’ option, enter/double click on this option
... Rename the column (input your required field names) and enter the data in the Table
... Go to the File menu and select the Save option to save the Table with a particular name
... Click the Ok button and select the No (there is no Primary key defined) option
... Close the Table

Table name: EMPLOYEE

Project-4: Create a Table by using the following field names.
Field name & Data type:

Field Name
Data Type

EMP_Code
Auto Number

Name
Text

Is_Indian_Citizen
Yes/No

Picture
Ole Object

Others
Hyperlink

Steps: The following steps are required to create the Table:
... Go to the Table (under objects)
.. To open the Design view option, enter/double click on this option
... Input the field names and data types in the Design view option
... Go to the File menu and select the Save option to save the Table with a particular name
... Click the Ok button and select the No (there is no Primary key defined) option
... Close the Design view option and open the created Table to input the data
To insert the Ole Object in the Table -
... Open the created Table
... Go to the Picture cell (data type of Picture field name is Ole Object)
... Right click on the cell (where you want to insert the Ole Object)
... Go to the ‘Insert Object’ option
... Select the particular option (e.g. Create new/Create from file)
... Select the ‘Create from file’ option and click the Browse button
... Select the object
... Ok
... Ok

To open the Ole Object: To open the Ole Object -
... Open the particular Table
... Double click on the cell (where the Ole Object is inserted)
... The inserted picture (Ole Object) will appear

To insert the Hyperlink in the Table -
... Open the created Table
... Go to the cell where the field name is Others (data type is Hyperlink)
... Right click on the cell (where you want to insert the Hyperlink)
... Select the Hyperlink option and go to the ‘Edit Hyperlink’ option
... Select the file and click the Ok button

To open the Hyperlink: To open the Hyperlink -
... Open the particular Table
... Double click on the cell (where the Hyperlink is inserted)

In Yes/No data type - Check boxes will appear in the created Table

Table name: COMPANY

Project-5: A. Create a Table to display a fixed set of values by using the ‘Lookup Wizard’ option.
Field name & Data type:

Field Name
Data Type

Code
Auto Number

Name
Text

Age
Number

Gender
Lookup Wizard

City
Lookup Wizard

Steps: The following steps are required to create the Table:
.... Go to the Table (under objects) and to open the Design view option, enter/double click on this option
.... Input the field names and data types (the data type will be Lookup Wizard for gender and City field name)
... Select the suitable option in the Lookup Wizard Table (e.g. I will type in the values that I want) and click the Next option
... Input the data in Column 1 and click the Next option
... Finish
.... Go to the File menu and select the Save option to save the Table with a particular name
... Click the Ok button and select the No (there is no Primary key defined) option
... Close the Design view option and open the created Table to input the data

Table name: COMPANY

B. Create a Table named City to display the values from a Table (City Master) by using the Lookup Wizard option.
Create a ‘City Master’ Table:
Field name & Data type of City Master Table:

Field Name
Data Type

City
Text

Steps: The steps are -
.. Go to the Table (under objects)
... To open the Design view option, enter/double click on this option
.... Input the field names and data types in the Design view option
.... Go to the File menu and select the Save option to save the Table with a particular name
... Click the Ok button and select the No (there is no Primary key defined) option
... Close the Design view option and open the created Table to input the data

Table name: CITY MASTER

Create a City Table:
Field name & Data type of City Table:

Field Name
Data Type

ID
Auto Number

Name
Text

Age
Number

City
Lookup Wizard

Steps: To create the City Table, we require some steps as follows:
1. Go to the Table (under objects)
2. To open the Design view option, enter/double click on this option
3. Input the field names and data types in the Design view option (Lookup wizard is the data type of City field name)
4. Select the particular option in the Lookup Wizard Table (e.g. I want the lookup column to lookup the values in a Table or Query)
5. Click the Next button and select the ‘City Master’ Table, which must provide the values for lookup column
6. Again click the Next button and select the particular field from the available fields
7. Next
8. Sort the records in ascending or descending order and click the Next button
9. Next
10. Give a label for the Lookup column
11. Finish
12. Go to the File menu and select the Save option to save the Table with a particular name
13. Click the Ok button and select the No (there is no Primary key defined) option
14. Close the Design view option and open the created Table to input the data

Table name: CITY

Project-6: Create a Table by using the following field names.
Field name & Data type:

Field Name
Data Type

Code
Text

Name
Text

Date_of_Birth
Date/Time

Phone_No
Text

Course_Fees
Currency

Steps: We have to carry out the following steps to create the Table:
... Go to the Table (under objects)
... To open the Design view option, enter/double click on this option
... Input the field names and data types in the Design view option
.... Go to the File menu and select the Save option to save the Table with a particular name
... Click the Ok button and select the No (there is no Primary key defined) option
After these steps, data types will be formatted in the Design view option.

1. Code: Data type - Text
.... Default value----------- “SYCTC/”
.... Validation text---------- Please type the correct format as SYCTC/000

2. D_O_B: Data type - Date/Time
Format: DDD (Day).DD (Date).MMM (Month).YYYY (Year), [ddd\, dd\, mmm\, yyyy]

3. Phone_ no: Data Type - Text
.... Go to the ‘Input mask’ format bar and click the button
.... Select the Phone no. and click the Next button
... Select the place holder character (e.g. @, ≠, -)
... Next
.... Next
... Finish

4. Course_ fees: Data Type - Currency
... Format – “Rs”.0.00

After the formatting of data type in the Design view option -
... Again save the changes and close the Design view option
... Now open the created Table to input the data
Table name: STUDENT

Project-7: Create a Relationship between two or more Tables.
Field name & Data type of Student Table:

Field Name
Data Type

Reg_no
Text

Name
Text

Address
Text

D_O_B
Date/Time

Steps: The following steps are required to create the Student Table:
... Go to the Table (under objects) and to open the Design view option, enter/double click on this option
.... Input the field names and data types in the Design view option
... Now select a particular field name (which is the same field name of the Course Table) and set the same field name with a Primary key (to set the Primary key, right click on the particular field name in the Design view option and select the Primary key option)
... Go to the File menu and select the Save option to save the Student Table with a particular name
... Click the Ok button
.... Close the Design view option and open the Student Table to input the data

Table name: STUDENT

Field name & Data type of Course Table:

Field Name
Data Type

Reg_no
Text

Course
Text

Marks
Number

Steps: We have to carry out the following steps to create the Course Table:
.... Go to the Table (under objects) and to open the Design view option, enter/double click on this option
... Input the field names and data types in the Design view option
.... Go to the File menu and select the Save option to save the Table with a particular name
... Click the Ok button and select the No (there is no Primary key defined) option
... Close the Design view option and open the created Table to input the data

Table name: COURSE

4 Create a Relationship between Student and Course Table:

Table name: STUDENT

Table name: COURSE

Steps: We have to carry out some steps to create Relationship between two Tables.
The steps are -
.... Go to the Tools option of the Toolbar
.... Select the Relationship option
... Select the Tables from the Show table
... Add
.... Connect the field (dragging the particular field name in which the Primary key is present)
... Create
... Go to the File menu and select the Save option to save the Relationship layout
... Now open the Student (Primary key is present in Student Table) Table and see the relationship structure

The created Relationship between the two Tables -

Table name: STUDENT

Project-8: A. Create the two Tables named Student and Course.
Field name & Data type of Student Table:

Field Name
Data Type

Reg_no
Text

Name
Text

Address
Text

D_O_B
Date/Time

Steps: The following steps are required to create the Student Table:
.... Go to the Table (under objects) and to open the Design view option, enter/double click on this option
.... Input the field names and data types in the Design view option
.... Now select a particular field name (which is the same field name of the Course Table) and set the same field name with a Primary key (to set the Primary key, right click on the particular field name in the Design view option and select the Primary key)
.... Go to the File menu and select the Save option to save the Student Table with a particular name
... Click the Ok button
... Close the Design view option and open the Student Table to input the data

Table name: STUDENT

Field name & Data type of Course Table:

Field Name
Data Type

Reg_no
Text

Course
Text

Marks
Number

Steps: We have to carry out the following steps to create the Course Table:
.... Go to the Table (under objects) and to open the Design view option, enter/double click on this option
... Input the field names and data types in the Design view option
.... Go to the File menu and select the Save option to save the Table with a particular name
.... Click the Ok button and select the No (there is no Primary key defined) option
.... Close the Design view option and open the created Table to input the data

Table name: COURSE

B. Display the Reg. no., name and course for those who are in CITA course using the Select Query option.
Steps: To create the Select-Query table, the following steps are required:
..... After creating the Student and Course Table, go to the Query (under objects)
..... From the New option of the Query open the Design view option
.... Click the Ok button and select the created Tables from the Show table
..... Add
.... If you select more than one Table then connect the common fields
.... Double click on the field names (which you want to select)
.... Use the Criteria to insert any condition
.... Go to the File menu and select the Save option to save the Query with the particular name
... Click the Ok button and close the Design view option of the Query

Query-CITA Table:

Project-9: A. Create a Table named Order.
Field name & Data type:

Field Name
Data Type

Customer’s_name
Text

Product
Text

Quantity
Number

Steps: We have to carry out some steps to create this Order Table:
Go to the Table (under objects)
... To open the Design view option, enter/double click on this option
... Input the field names and data types in the Design view option
... Go to the File menu and select the Save option to save the Table with a particular name
... Click the Ok button and select the No ( there is no Primary key defined) option
... Close the Design view option and open the Table to input the data

Table name: ORDER

B. Calculate the total quantity of products by using the Crosstab Query.
Steps: To calculate the total quantity of products by using the Crosstab Query, the following steps are required:
.... Go to the Query (under objects) and open the New option
.... Select the ‘Crosstab query wizard’ and click the Ok button
... Select the particular Table and click the Next button
.... Select the row heading field (double click on Customer’s_ name) and click the Next button
... Select the column heading field (like Product name)
... Next
... Select the function (like: Sum, Max, Min)
... Next
... Input the Query name
... Finish

Crosstab-Query: ORDER

Project-10: A. Create a Table named Order.
Field name & Data type:

Field Name
Data Type

Customer’s_Name
Text

Product
Text

Quantity
Number

Steps: To create the Order Table, the following steps are required:
..... Go to the Table (under objects)
.... To open the Design view option, enter/double click on this option
... Input the field names and data types in the Design view option
... Go to the File menu and select the Save option to save the Table with a particular name
... Click the Ok button and select the No ( there is no Primary key defined) option
... Close the Design view option
..... Open the Order Table again to input the data

Table name: ORDER

B. Find out the duplicate records from the Order Table by using the Duplicate Query.
Steps: We have to carry out some steps to find out the duplicate records from the Order Table.
.... Go to the Query (under objects) and open the New option
... Go to the ‘Find Duplicates query wizard’ option
.... Click the Ok button and select the particular Table
... Next
... Select the field name (duplicate-value field) and click the Next button
... Select the additional field name and click the Next button
..... Input the duplicate query name
... Finish
Duplicate-Query Table: ORDER

Project-11: A. Create two Tables named Student and Admission.
Field name & Data type of Student Table:

Field Name
Data Type

Reg_no
Text

Name
Text

Address
Text

D_O_B
Date/Time

Steps: To create the Student Table, the following steps are required:
.... Go to the Table (under objects)
... To open the Design view option, enter/double click on this option
... Input the field names and data types in the Design view option
... Go to the File menu and select the Save option to save the Table with a particular name
.... Close the Design view option and open the Student Table to input the data

Table name: STUDENT

Field name & Data type of Admission Table:

Field Name
Data Type

Reg_no
Text

Course
Text

Steps: To create the Admission Table, the following steps are required:
... Go to the Table (under objects)
.... To open the Design view option, enter/double click on this option
.... Input the field names and data types in the Design view option
... Go to the File menu and select the Save option to save the Table with a particular name
.... Close the Design view option and open the Admission Table to input the data
Table name: ADMISSION

B. Find out those students who are not admitted by using the Unmatched Query.
Steps: We have to carry out some steps to create this Unmatched Query Table. The steps are-
... Go to the Query (under objects)
... Open the ‘Find unmatched query wizard’ from the New option
... Click the Ok button and select the required Table
.... Next
... Select the required Table (again) and click the Next button
.. Select the matching field in each Table
... Then click the [< = >] button
... Next
... Select the other fields and click the Next button
.... Input the Unmatched Query Table name
.... Finish

Unmatched Query Table: ADMISSION

Project-12: A. Create a Table named Employee. Field name & Data type:

Field Name
Data Type

Name
Text

Basic
Number

DA
Number

TA
Number

HRA
Number

PF
Number

Steps: We have to carry out the following steps to create the above Table:
a) Go to the Table (under objects)
b) To open the Design view option, enter/double click on this option
c) Input the field names and data types in the Design view option
d) Go to the File menu and select the Save option to save the Table with a particular name
e) Close the Design view option
f) In this case, open the created Table and input the data only for Name and Basic field name

Table name: EMPLOYEE

B. Using Update Query we can easily update any record.
Conditions: The conditions are-
I. DA will be calculated as 10% of Basic
II. TA will be calculated as 5% of Basic
III. HRA will be calculated as 10% of (Basic + DA)
IV. PF will be calculated as 1.75% of Basic

Steps: To create the Update Query, the following steps are required:
..... Go to the New option of the Query (under objects)
.... Select the Design view option and click the Ok button
.... Select the particular Table and add the Table from the Show table
... Double click on the fields, which you want to update (such as DA, TA, HRA, PF)
.... Right click on the blank portion
... Go to the query type and select the ‘Update Query’ type
.... Go to the update portion and write the mathematical calculations
... The calculations are -
DA = [Basic] * 0.1
TA = [Basic] * 0.05
HRA = ([Basic] + [DA]) * 0.1
PF = [Basic] * 0.0175
... Go to the File menu and select the Save option to save the Query with a particular name
.... Ok
... Close the Design view option of the Query object
... Double click on the created Query again
... Then click the Yes button to run this type of action query
... Again click the Yes button to update these records
... Go to the Table object again and open the Employee Table to see the updated Employee Table

Table name: EMPLOYEE

Project-13: A. Create a Table named Employee.
Field name & Data type:

Field Name
Data Type

Name
Text

Dept.
Text

Basic
Number

DA
Number

TA
Number

HRA
Number

Gross
Number

PF
Number

Net
Number

Steps: To create the Employee Table, the following steps are required:
... Go to the Table (under objects)
... To open the Design view option, enter/double click on this option
... Input the field names and data types in the Design view option
.... Go to the File menu and select the Save option to save the Table with a particular name
... Close the Design view option
... Now open the created Table and in this case only input the data of Name, Dept. and Basic
Table name: EMPLOYEE

B. We can easily update any record by using the Update Query.
Conditions: The conditions are-
1) DA will be calculated as 10% of Basic who are working in ACC dept., 20% of Basic who are working in PDN dept. and 30% of Basic who are working in MKT dept.
2) TA will be calculated as 5% of Basic.
3) HRA will be calculated as 10% of (Basic + DA)
4) Gross will be calculated as Basic + DA + TA + HRA
5) PF will be calculated as 1.75% of Gross
6) Net will be calculated as Gross—PF
Steps: We have to carry out the following steps to create an Update Query Table:
..... Go to the New option of the Query (under objects)
.... Open the Design view option and click the Ok button
.... Select the particular Table and add the Table
... Double click on the fields, which you want to update (such as DA, TA, HRA, Gross, PF, Net)
... Right click on the blank portion and select the Update query from the query type
... Go to the update area (right click on the update area and go to the ‘Build’ option)
... Write the mathematical calculations in the build option and the mathematical calculations are -

DA = IIF ([Dept] =”ACC”, [Basic] * 0.1, IIF ([Dept] =”PDN”, [Basic] * 0.2, IIF ([Dept =”MKT”, [Basic] * 0.3)))

TA = [Basic] * 0.05,
HRA = ([Basic] + [DA]) * 0.1,
Gross = [Basic] + [DA] + [TA] + [HRA]
PF = [Gross] * 0.0175,
Net = [Gross] - [PF].

... Go to the File menu and select the Save option to save the Query with a particular name
.... Click the Ok button and close the Design view option of the Query object
.. Double click on the created Query again
.... Then click the Yes button to run this type of action Query
.... Again click the Yes button to update these records
.... Go to the Table object again and open the Employee Table

Table name: EMPLOYEE

Project-14: A. Create a Table named Student.
Field name & Data type:

Field Name
Data Type

Name
Text

Course
Text

Course_fees
Currency

Paid
Yes/No

Steps: To create a Student Table, the following steps are required:
.... Go to the Table (under objects) and open the Design view option
... Input the field names and data types in the Design view option
... Go to the File menu and select the Save option to save the Table with a particular name
... Close the Design view option and open the Table to input the data

Table name: STUDENT

B. Delete those students who have been paid their course fees by using the Delete Query.
Steps: We have to carry out the following steps to create a Delete Query Table:
... Go to the New option of the Query (under objects) and open the Design view option
.... Click the Ok button
.... Select the particular Table from the Show table and add the Table
... Double click on those fields, which you want to delete
... Right click on the blank portion and select the Delete query type from the query type
... Go to the Criteria option and write Yes/No for deletion
... Go to the File menu and select the Save option to save the Query Table with a particular name
... Click the Ok button and close the Design view of the Query
.... Double click on the created Query again
.... Click the Yes button to run this type of action query
... Again click the Yes button to update these records
.... Go to the Table again and open the Student Table

Query Table: STUDENT

Project-15: A. Create a Table named Student.
Field name & Data type:

Field Name
Data Type

Reg_no
Text

Name
Text

Address
Text

Phone_no
Text

Steps: To create a Student Table, the following steps are required:
..... Go to the Design view option of the Table (under objects) and input the field names and data types
..... Go to the File menu and select the Save option to save the Table with a particular name
.... Close the Design view option and open the created Table to input the data

Table name: STUDENT

B. Using this Student Table creates a Form by using the Form Wizard.
Steps: The following steps are required to create a Form by using the Form wizard:
.... Go to the New option of the Form (under objects) and open the Form Wizard
... Select the Table and click the Ok button
.... Select the fields
.... Next
.... Select the layout
... Next
... Select the style and click the Next button
... Input the Form name
... Finish

Form name: STUDENT

Project-16: Create an Auto-Form (e.g. Columnar, Tabular) by using a Student Table.
A. Create a Table named Student.

Table name: STUDENT (This project is same as Project-15).

B. Create a Form by using the Auto-Form: Columnar
Steps: We have to carry out some steps to create a Form by using the AutoForm - Columnar:
.... Go to the New option of the Form (under objects) and select the ‘AutoForm - Columnar’ option
.... Select the Table and click the Ok button
... Go to the File menu and select the Save option to save the created Form with a particular name
.... Ok

AutoForm - Columnar: STUDENT

C. Create a Form by using the Auto-Form: Tabular
Steps: To create an AutoForm-Tabular, the following steps are required:
.... Go to the New option of the Form (under objects) and open the ‘AutoForm-Tabular’ option
.... Select the Table and click the Ok button
... Go to the File menu and select the Save option to save the Form with a particular name
.... Ok

AutoForm - Tabular: STUDENT

Project-17: A. Create a Table named Student (This project is same as Project-15).

Table name: STUDENT

B. Create a Form with command buttons by using the Design view option.
Steps: To create a Form by using the Design view option, the following steps are required:
a) Open the Form (under objects) and go to the New option
b) Open the Design view option with the particular Table
c) Then click the Ok button and drag the fields on the Form
d) Go to the Toolbox and click on command button (at first make the ‘Control Wizard’ active)
e) Drag the command buttons on the Form (where you want to placed the buttons)
f) Select the categories (Record navigation or Record operations) and the actions (e.g. Save, Delete, Add etc.)
g) Click the Next button and select the button character (e.g. Text or Picture)
h) Next
i) Finish
j) Go to the File menu and select the Save option to save the Form with a particular name

Form name: STUDENT