Objectives

MS Access is used by many small and large organizations to store data. As Year 2 students, this activity will be a refresher for you as you have already taken courses in MS Access and database design.

You have also done the two NetG courses that are a requirement for ECOM 270.

This exercise is to be done prior to working on the "Store Your Information" project or the Database Activity #2 project. These two are much larger in scope and require you to develop a web-based database system using PHP and MySQL.

Guidelines

How you will submit the assignment:

1. You will submit this assignment as a nicely formatted Web Page/site. In addition, you will post this assignment on your E-portfolio site. The clearly labelled link will be on your Home Page and will be called "Perfume".
2. For Question 5, you will show me the structure of your data so I can see that this has been done. Use SnagIT to capture that portion of the screen that shows me the database structure. Then, paste the capture(s) into your web page/site.
3. For each Query and Report, use SnagIT to capture the output and paste it into the same page/site.
4. Format the web page/site nicely. Put your name, ID and section as a footer.
5. Make SURE that your name shows up in some of the queries such as Query 14 and Reports.
6. This is an INDIVIDUAL assignment. Although you may discuss this with your classmates/group members, you are responsible for the final product.
7. E-mail your instructors and let them know you have posted the assignment.

Timeframe

You will have 2 weeks to complete this assignment. Your instructors will tell you when to start this and your submission will be two weeks from that date.

Instructions

The Emirates Perfume Company wants to maintain an Employee database to keep details of all staff.

You will need to do the following:

1. Create a new database for the company. Call it “perfume.mdb”.

Save your database file as an Access version 2000 file. This will make sure that people not using Office 2007 will also be able to use it.
2. The company wants to maintain an Employee table to keep track of all staff. Therefore, they want the following details of the employee to be stored in the table:
Employee ID Join date Nationality
Employee name Education Job title
Address Basic Salary Department
Home phone number Allowances Date of birth
Mobile phone number Married?  

Create a new table and enter the field names and their data types. See the table below:

Field name Data type Description
EmpID Text A unique identity number for each employee (like your student ID)
Empname Text Full name of employee
Jobtitle Text Designation of the employee
Dept Text Department number
Joindate Date/Time Date joined the company
DOB Date/Time Birth date
Nationality Text Country of Origin
Married Yes/No Indicates whether the employee is married.
Address Text  
Homephone Text  
Mobilephone Text  
Education Memo Educational qualifications and experience
Basic Salary Currency  
Allowances Currency  
3. Assign the “EmpID” field as the primary key
4. Save the table as “Employee Table”.
5. Set the following properties for the fields in the “Employee Table”:
Field name Properties to set
EmpID
  1. Field size = 5
  2. Caption = Employee Number
    Validation rule = Must begin with the Letter "E"
  3. Validation Text = “Employee numbers must begin with the Letter "E"
  4. Required = Yes
Empname
  1. Field size = 35
  2. Caption = Employee Name
  3. Required = Yes
Jobtitle Field size = 25

Dept
  1. Field size = 2
  2. Caption = Department
  3. Validation rule = Between 1 and 4
  4. Validation text = "Should be a number between 1 and 4"
Joindate
  1. Format = Medium date
  2. Validation rule = <=Date()
  3. Validation text = "Incorrect Join date"
DOB Format = Medium date
Nationality Field size = 15
Address Field size = 40
Home Phone
  1. Field size = 15
  2. Input mask = (999)-9-9999999
Mobile Phone
  1. Field size = 15
  2. Input mask = (999)-99-9999999
Basic Salary
  1. Validation rule = >0 and <=15000
  2. Validation text ="Basic salary cannot be zero and should not exceed 15,000"
Allowances
  1. Validation rule = >0 and <=10000
  2. Validation text = "Allowances cannot be zero and should not exceed 10,000"
6. Enter the following data in the table:  
Field Record 1 Record 2 Record 3 Record 4 Record 5
EmpId E1001 E1002 E1003 E1004 E1005
Empname Saif Ahmed Peter Smith Tony Alter Tom Moody Fred Moore
Jobtitle Director Manager Manager Manager Sales Manager
Dept 2 1 3 4 3
Joindate 01/01/95 01/05/96 02/05/96 11/05/96 05/11/96
DOB 04/22/55 06/14/58 11/06/52 02/05/56 05/11/60
Nationality UAE UK USA UK UK
Married Yes Yes Yes Yes Yes
Address P.O.Box 121 P.O.Box 323 P.O.Box 324 P.O.Box 321 P.O.Box 632
Homephone 04-748742 04-637372 06-638228 04-637378 06-537321
Mobilephone 050-6464872 050-7489789 050-6467483 050-6382911 050-6381911
Education MBA M.Tech, PhD MBA Higher Diploma Higher Diploma
Basic salary $7500 $5000 $5000 $4500 $4000
Allowances $2000 $1500 $1500 $1250 $1000
Field Record 6 Record 7 Record 8 Record 9 Record 10
EmpId E1006 E1007 E1008 E1009 E1010
Empname Juma Al Shamsi Ashok Kumar Ahmed Ali Deepak Patel Tim Duncan
Jobtitle Supervisor Executive Operator Operator Technician
Dept 4 1 1 1 1
Joindate 05/11/97 06/14/98 05/11/96 01/15/96 01/11/97
Dateofbirth 05/18/65 01/25/62 08/15/60 02/20/66 05/11/65
Nationality UAE India Pakistan Canada UK
Married? No Yes Yes No No
Address P.O.Box 748 P.O.Box 7436 P.O.Box 848 P.O.Box 8489 P.O.Box 8484
Homephone 04-467974 04-748745 06-448885 04-748874 06-534687
Mobilephone 050-7854587 050-4888754 --- --- ---
Education BBA Diploma Diploma B.Sc Diploma
Basic salary $3000 $2,000 $1800 $1500 $1800
Allowances $1000 $500 $500 $500 $500
Field Record 11 Record 12 Record 13 Record 14 Record 15
EmpId E1011 E1012 E1013 E1014 E1015
Empname Mary Peters Alia Ahmed Amira Mohd. Janet Short Debbie Rodrigues
Jobtitle Executive Executive Assistant Executive Assistant
Dept 2 2 2 3 4
Joindate 01/05/97 01/09/98 02/08/98 11/15/97 08/11/97
Dateofbirth 04/22/65 06/14/74 11/06/75 02/05/68 05/11/69
Nationality Canada UAE UAE USA India
Married? Yes Yes Yes No Yes
Address P.O.Box 4888 P.O.Box 4787 P.O.Box 7811 P.O.Box 1854 P.O.Box 5854
Homephone 04-7462421 04-6374772 06-4762284 04-632148 06-5384821
Mobilephone 050-8451211 050-2514254 050-4517852 050-4121547 ---
Education CPA BBA Diploma BBA Diploma
Basic salary $2500 $2500 $2000 $2500 $1500
Allowances $1000 $1500 $1500 $1050 $500

For Record 16, you will add your own information into the database. Your EmpID will be “E5000”; Your EmpName will be your name; Your Dept will be “4”; Joindate will be November 20, 2007; Nationality will be “UAE”; Education will be “Diploma”; Basic Salary will be “3000” and Allowance will be “1000”;  

7. Create a form for the Emirates Perfume company database. Use the Forms Wizard. Select all the fields to appear on the form. The title for your form will be "Employee Data Entry Form".
8. Select the “Dept” field and Right-Click the mouse. Choose “Change to” and select the “Combo box” option.
9. Choose the “Dept” field again, right-click on the mouse and choose “Properties”. In the “Row Source Type” section choose “Value List
10. In “Row Source”, type the list of values for the “Dept” field. Each value is separated by a semicolon as shown below (Remember, there are 4 departments in the company): 1;2;3;4. See the the image below in case you are using Office 2007:

11. Enter the following records of employees using the "Employee Data Entry Form":
Field Record 17 Record 18 Record 19 Record 20 Record 21
EmpId E1016 E1017 E1018 E1019 E1020
Empname Abdul Majid Dima Baki Amna Ahmed Paul Rogers Pamela Fox
Jobtitle Executive Executive Assistant Executive Assistant
Dept 2 3 2 3 3
Joindate 05/03/96 05/01/97 02/06/97 01/05/97 04/01/96
Dateofbirth 01/18/65 05/04/70 01/03/68 02/05/68 01/03/71
Nationality UAE Bahrain UAE USA UK
Married? No Yes Yes No Yes
Address P.O.Box 4241 P.O.Box 751 P.O.Box 8214 P.O.Box 2145 P.O.Box 5474
Homephone 04-6574541 04-5412147 06-6213245 04-6412457 06-5358741
Mobilephone 050-9512471 050-1425478 050-8741254 050-4578412 ---
Education Diploma BBA Diploma BBA Diploma
Basic salary $2500 $2500 $2000 $2000 $1500
Allowances $1000 $1500 $1500 $750 $500

Queries:

12. You are the Director of Emirates Perfume Company and want to know all the employee names, their nationality and date of hire (joining) who are working in Department 3 (Marketing) and sorted according to their employee number.

Create a query to do this. Save this query as "Q12”  

Hint:
  Fields required to answer: Employee name, nationality, Date of joining, department, employee number  

Sort:
On Employee number (ascending)  

Show:
Only Employee Number, Employee name, nationality, Date of joining  

Criteria:
Working in the Marketing department only.
13. Experiment with queries using the logical operators (Google search) in the table below.

Save these queries as "Q13a”, "Q13b”, etc.

Make sure to show Employee Number, Employee name, Job title, Dept, Date of joining, DOB and nationality.
Query Criteria Expression Field Applied To Meaning
Q13a E1005 EmpID Finds information for the employee whose ID is E1005
Q13b <E1005 EmpID Finds information for the employees whose ID is between E1001 and E1004 inclusive (not E1005)
Q13c >E1005 EmpID Finds information for the employees whose ID is above E1005 (not E1005)
Q13d <=E1005 EmpID Finds information for the employees whose ID is between E1001 and E1005 inclusive
Q13e >=E1005 EmpID Finds information for the employees whose ID is E1005 and above
Q13f <>E1005 EmpID Finds information for the employees whose ID is not E1005
Q13g "USA"; or "UAE" Nationality Finds information for USA or UAE
Q13g2   Nationality Finds information for countries starting with “U”
Q13h Like "M" EmpName Finds information about people whose names begin with “M”
Q13i Between 1/1/97 And 12/31/97 JoinDate Finds information about persons who joined the company during 1997. Sort this by date.
14. Create a query to show empID, empname, job title, department for ALL employees sorted by department. Save the query as "Q14-Employees by department".
15. Create a query to show empID, empname, job title of employees whose job title contains the word "manager". Save the query as "Q15-Managers of Emirates Perfume Company".
16. Create a query to show empID, empname, join date and nationality for employees who are "Canadian" OR "American". Save this query as "Q16-North American employees".
17. Create a query to show empId, empname, nationality, joindate, department of all employees who are American and work in the Marketing department. Save this query as "Q17-Americans in marketing".
18. Create a query to show empname, department, job title, nationality, salary of all employees who work in Marketing (Dept 3) or Production (Dept 4) and earn a monthly basic salary of $3,000 or higher. Save this query as "Q18-High income employees".
19. Create a query to show empId, department, job title, joindate, education of employees who have joined in 1995 or 1996. Save this query as "Q19-Core employees".

Aggregate Queries & Queries with Calculated  fields

20. Create a query to show the number of employees in each department. Save the query as "Q20-Number of employees in each department".  
21. Create a query to show the maximum and minimum Basic salary for each department. Save the query as "Q21-Maximum and Minimum salaries by department".  
22. Create a query to show the total Basic salaries and total allowances for each department. Save the query as "Q22-Total Salary expense by department".
23. Create a query to show empID, empname, department, job title, basic salary, allowances and total gross salary. The field “total gross salary” is a calculated field. The formula for calculating total gross salary is ([basic salary]+[allowances]). Save the query as "Q23-Total gross salary of employees".

Preparing Reports 

24. The Director wants a report of all employees grouped by nationality. The report should show Empname, Job title, Joindate and Basic salary.
  1. Click on the REPORTS tab and choose "Report wizard".
  2. At the bottom, choose “Employee Table” as the table/query from which you will get the required information.
  3. Since the Director wants a report by nationality, this becomes a group. Choose nationality and click on the arrow.
  4. Sort the data within the group by the "joindate".
  5. Choose stepped layout and portrait orientation.
  6. Choose “corporate” as the "style" of the report. If “corporate” is not available, use another style that looks professional.
  7. Type "Q24-Report of Employees by Nationality" as the title of the report
25. The personnel manager of Emirates Perfume Company wants to see a report of all employees. This report is to be grouped by department. Within each department the information is to be sorted by empID.

The fields to be shown in the report are empID, empname, job title, nationality, and join date.

The title of the report should read as "List of employees by department".

Save the report as "Q25-Employees by department report".
26. The General manager wants to see a report of total salary expenditure by department.

Use the query created earlier in Question 23 to prepare this report.

The report should include sub-totals of basic salary, allowances, total gross salary for each department and a Grand total for the company as well.

The title of the report should read as "Monthly Salary Expenditure Report".  

Save the report as "Q26-Monthly salary report".