NAME______2012 Fall Semester Access Practical

Create a new database named First_Last_Employee. First should be your first name and Last should be your last name. Save your file to your flash drive/jump drive and also to your laptop. If you do not save your file to your flash drive and you cannot recover it through the email you will send me at the end of class, you may receive a 0 for the practical examination grade. You may NOT share jump drives with other students.

Part A

Create 2 tables according to the below requirements. Provide a user friendly message for the field Salary.

Table Name:Employee

  1. EmpIDPrimary Key (PK), Auto number
  2. EmpFirstRequired, Text, No more than 50 characters
  3. EmpLastRequired, Text, No more than 50 characters
  4. EmpAddress1Required, Text, No more than 100 characters
  5. EmpAddress2Optional, Text, No more than 100 characters
  6. EmpCityRequired, Text, No more than 100 characters
  7. EmpStateIDRequired, Number, (FK – Foreign Key), Must correspond to a valid state id in the state

Table

  1. EmpZipCodeRequired, Text, Zip code format (Any kind of zip format)
  2. EmpPrimaryPhoneRequired, Text, Phone format
  3. EmpAnnualSalaryCurrency, between $1000 and $250,000 where $1000 is included and $250,000 is also

included

The below table should be loaded from the delimited file at the location:

Table Name:CampusLocation

  1. StateIDPrimary Key (PF), Auto number
  2. StateNameRequired, Text, no more than 50 characters
  3. StateCDRequired, Text, no more than 2 characters

Part B

For each of the above tables, create a form. Each form should permit the 4 CRUD operations. The delete operation should be implemented with a button. The Employee form should have a drop down for the user to select the State in which s/he lives. Enter at least 2 employees from 3 different states. (For example, 2 from Missouri, 2 from New York, 2 from Maryland.)

Example Sample Data:

1.Sallie Taylor4260 Lindell Boulevard, Saint Louis, Missouri, 63108, 314-555-1212, $120000

2.Paul Stemn4260 Lindell Boulevard, Saint Louis, Missouri, 63108, 314-555-1111, $43,000

3.Alex Stangl1050 Park Avenue, New York, New York, 10028, 917-462-4222, $92,000

4.Kim Sirl-Stangl1050 Park Avenue, New York, New York, 10028, 917-435-6537, $87,000

5.Barbara Day422 Bluff Drive, Belleville, Illinois, 62223, 618-344-5487, $34,000

6.Harold Lane32 Winding Way, Chicago, Illinois, 60001, 847-456-3444, $78,000

Part C

Generate a Report using the Report Wizard that looks similar to the one below named EmployeeTelephoneTreeByState. Notice that the employee names appear in alphabetical order and that the headers have been modified.

Part D

Create a query called HighlyCompensatedEmployees that can be used to Generate the Below Report. The report should include the details shown for employees, who earn more than $50,000.

Then create a report using that query as input, also called HighlyCompensatedEmployees. The query should select the data and the report should format the data.

CLOSE YOUR ACCESS FILE and email it to .

  • Answer the additional questions at the end of the exam.
  • DO NOT LEAVE class until I confirm that your file has been received.
  • KEEP the copy on your jump drive, on your laptop and the email as back-ups.

Short Answer Questions:

  1. A ______is a field in one table that is a primary key in another.
  1. The file extension of a Microsoft Access database is ______.
  1. The most common file extension of a delimited file is ______.
  1. The term in MS Access that indicates the DBMS will select the primary key is ______.
  1. To adjust the name of a label on a Form, one should be in the ______view.
  1. To quickly add records to a table without having to use the form, one can use the ______view.
  1. Marsha intends to only be able to use Campus locations that exist in the database, but the software currently allows her to enter any location id in to her table of Jobs. What did Marsha likely forget to do?
  1. What are the 4 operations all businesses must be able to perform on data?
  1. What are the 3 primary layers of a software application?
  1. What is the term MS Access uses for GUI?
  1. To add another field to a table and restrict the size or data type of that field, one should be in the ______of the table.
  1. Give 2 examples of likely business rulesassociated with a field called caloriesPerServing. ______
  2. How does one designate a foreign key in MS Access? (On what screen should one be?) ______
  3. Write the validation rule that designates a numeric field must be greater than 32 and less than 100 ( both are included)

______

15.A ______is a set of logically related data stored in a shared repository.

16.Software that creates and manipulates data is a ______

17.A ______database model uses controlled redundancy to create fields that provide linkage

relationships between tables in a database. These fields are called ______keys.

18.A ______is a set of fields that are grouped together for a specific purpose.

19.A ______is an individual piece of data.

20.A ______occurs when data becomes inconsistent within a particular file or table.