GROUP B (DATABASE PRAC 1)

Practical No 1: Creation of a database with a simple query using Access

Create an employee database using MS Access and add the following two tables and queries into the
same database using New Blank Database option.
1. Name the table 1 Employee_Master and add the following fields
Emp_no, Emp_name, Address, City, Pin_code, Mobile_no, Gender, Dept_name, Designation
2. Name the table 2 Salary_master and add the following fields
SalarySheet_no, Emp_no, Basic, Da, Hra, Ta, Gross_salary, Insurance_Premium,
Loan_instalment, Income_tax, Total_ded, Net_salary
Enter at least 10 records in both the tables and perform following simple query
Calculate the net salary of each employee.
Display the list of employees with the following fields – Emp_no, Emp_name, Designation and net
salary.

Steps to Create a database  applying query on the same:-
1. Start; Program;MS Office; MS Access 2007
2. Click on Office button ; new ; Blank Database ; Specify Location were to
save ; Create.
3. Click on Datasheet tab; views ; Click on Design View
4. Save the Table with name Employee_Master.
Field Name     Data Type
Emp_no          Autonumber
Emp_name      Text
Address            Text
City                   Text
Pin_code           Number
Mobile_no        Text
Gender              Text
Dept_name        Text
Designation        Text

5. Add the following fields in it.
6. Save the Table by pressing CTRL+W
7. Similarly create another Table Salary_Master with following fields in it.
Field Name                 Data Type
Emp_no                    AutoNumber
Basic                         Number
Da                              Number
Hra                             Number
Ta                               Number
Gross_salary              Number
Insurance_Premium   Number
Loan_instalment         Number
Income_tax                 Number
Total_ded                    Number
 Net_salary                  Number

8. Enter at least 10 records in the table
9. Set the relationship between both tables by clicking in Database Tools;
Relationships ; add both tables ; set one to one relationship between them ;
save it.
10. To Calculate the Net salary of each employee Click on Create Tab ;Query
Design ; add Table Salary_Master.
11. Now click on Design tab ;view ; SQL view.
12. Type following Update command in it:
UPDATE SalaryMaster
SET Gross_Salary = Basic+DA+HRA+TA,
Total_Deduction = Gross_Salary-Insurance_Premium-Loan_Installment-
Income_Tax,
Net_Salary = Gross_Salary-Total_Deduction;
13. Or We can also use design view, Add the required fields and in Update to type
the following:-
14. Save the query ; run it by double clicking on query. Allow the Updating and
open the table to view the changes
15. To display the list of Employees having Emp_No, Emp_Name, Designation
and Net_Salary ; Click on Create Tab ;Query Design ; add both the Table.
16. Save the query ; run it by double clicking on query.