computing

DATA FILES/ACCESS/Assn1Access_Instructions.htm

ACCESS Assignment 1 - BISI 2150; Dr. Venkatraman

 

Step 1: From my eLearn Content page, download the Microsoft Access Data file

Access Assignment Data File ACCDB File  is the link on the eLearn Content Page

Open the Access File by double clicking on it. It contains 1 Table called Employees.

Step 2a: Create a Department Table with 4 fields/columns:  (Tip: In Create Tab, Select Table Design)

• DeptCode     Data Type: Number; Then in Field Properties Set Field Size to Integer;  Also Set DeptCode as Primary Key which means that no 2 departments can have the same DeptCode

• DeptName    Data Type: Text

• Location        Data Type: Text

• TelNum         Data Type: Text; Under Field Properties, Set Input Mask as Telephone Number - it will appear as !\(999") "000\-0000;;_

 

Step 2b: Create a Form for Department Table. (Tip: In Create Tab, Select Form)

• Select the Department Table, then select FORM in the CREATE Tab Ribbon.

• Save it immediately as Department Form.

• After saving it, open the form by clicking on it, and input by typing the following Records/Data/Rows:

11,INFORMATION SYSTEMS, Atlanta, 1235557687

22,ACCOUNTING, Boston, 2345569098

33, MARKETING, Chicago, 4567780980

44, HUMAN RESOURCES, Los Angeles, 3458833325

55, RESEARCH, Miami, 2107352345

 

Step 2c: (More Practice!) Create a form  for Employees table and save it as Employees Form. I already typed the Employee data, so you don't need to. But if a New Employee were to join the company, then we can use this form!

 

Step 3a: Create/Design the following Queries: (Tip: In Create Tab, Select Query Design)

• Select Query Design in the CREATE Tab Ribbon.

• Select Table(s) needed for Query & Add. Then Close the small window. You are now in the Query Design Screen.

1. List the Firstname, LastName, Location, and Salary of Employees (Ascending Last Name order) located in San Francisco. Save as Query1.

2.  List the Firstname, LastName, Location, and Salary of Employees who make less than or equal to $75,000. Save as Query 2

3. List the Firstname, LastName, Title, Location, and Salary of Employees (Descending Salary order) located in San Francisco or Tallahassee. Save as Query3.

4. List the FirstName, LastName, and Salary of Employees who are not located in San Francisco. Save as Query4

5. List the Firstname, LastName, Title, and Salary of Employees (Descending Salary order) who are Good Sales Representatives. Save as Query5.

6. List FirstName, LastName, Salary, & Performance if the Employee is Excellent OR makes a salary < $75,000. Save as Query6.

7. List FirstName, LastName, Salary, Performance & Gender of all Female employees whose performance is Good or Excellent AND make less than $70,000. Save as Query7

8. Find the total number of employees, and the average salary of all employees. Save as Query8.

9. Find the total number of employees, and the average salary of employees in Tallahassee. Save as Query9.

10. Find the total number of employees, and the average salary of employees in Each Location. Save as Query10.

11. Find the total number of employees, and the minimum, maximum, and average salary of employees in Every City (Hint: Group by Location). Save as Query11.

12. List the LastName, FirstName, Salary, and Tax for Each Employee - total 4 columns. Tax is 30% of the Salary - and is called a derived Column or a calculated field. Save as Query12.

13.  List the LastName, FirstName, Salary, and a Salary Status Column for Each Employee - total 4 columns. Print the Word HIGH (under the  SalaryStatus Column) if the salary of the employee is greater than or equal to $75,000, otherwise Print "Low". Save as Query13

14. Same as Query 8, but don't indicate any Status if Salary is less than $7,5000. Save as Query 14.

Step 3b: Create a Report for Each Query Above. (Tip: In Create Tab, Select Report)

• Highlight the Query, then select REPORT in the CREATE Tab Ribbon.

• Save It (Example: rptQuery1, rptQuery2, etc.)

• Go to Design View of the Report, & MODIFY the REPORT HEADER to include YOUR FIRST & LASTNAME (s). Also type  today's date also on the header.