This is the solution for Database Activity #1. Please mouse over the links for answers and tips for this activity. Because of the graphics on this page, it may be a little slow to load.

Solutions

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 “SecNumber-YournameandID.mdb”. For example, if you are Noura in Section 3, your file will be called "Sec3-Noura99000000.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

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: