Employee Compensation

There are 5 questions in the exam. You need to use the related MS Access files for Questions 1 to 3. In the end, you have to save it as Access_QuestionY_LastFirst that Y is the question number.

Questions 4 and 5 are from the SQL part. You need to write the SQL codes for these questions in an MS Word or a Text Document file (a complete explanation can be seen at the beginning of Question 4 and 5).

There are different types of questions. Canvas will assign a different set of questions to everyone with a different Excel file.

Submit your final files to the Canvas under the specified link before the deadline (3 hours after the start date).

The time of this exam is limited, so try to concentrate on your work to be able to finish it on time.

If you are run out of time, save your work as is, and submit it before the deadline. There would be no exception!

During the exam, you cannot chat, discuss, talk, email, and share anything with other students. Any type of communication is considered cheating.

Note: this is a timed quiz. You may check the remaining time you have at any point while taking the quiz by pressing the keyboard combination SHIFT, ALT, and T... Again: SHIFT, ALT, and T...

Question 120 pts

Employee Compensation

You have been hired by a local small manufacturing company to analyze employee compensation. There have been several complaints that employee salary and bonuses are completely arbitrary and not tied to their years of service, job title, or annual review score. You will analyze data by creating several queries to help substantiate or disprove the complaints.

Create a Query with a Calculated Field

You will create a new query that returns each employee’s total compensation along with a total for all compensation.

1. Open the file Access_Problem1 and save it as Access_Problem1_LastFirst.
2. Create a new query named qryTotalCompensation that includes all fields (six fields totally) from tblCompensation
3. Add a new calculated field named Total Compensation, which is a total of Salaryand Bonus
4. Set the format of the new field to Currencywith 0
5. Add a new Total rowto qryTotalCompensation query for the Salary, Bonus, and Total Compensationfields to find the sum of each field.

Create a Query with Grouping

You need to create a new query that returns total compensation grouped by Employee Title along with the highest and lowest paid employee with each Title.

1. Create a new query named qryTotalByTitlethat is a total query of all employees by title.
2. Add Employee Title and Salaryfields from tblCompensationtable to the query.
3. In the third column, add a new field and name it Topto find maximum salaries for each title.
4. In the 4^thcolumn, add a new field and name it Bottomto find minimum salaries for each title.
5. Include the following grouping and aggregations and then run the query:

• Employee Title = Grouped by
• Salary = Totalof Salary
• Top (new field) =Highest Salary
• Bottom (new field) =Lowest Salary

Create a Query to Analyze Possible Discrepancies

You need to create a new query that returns individuals who received a high rating and are getting paid less than the average salary.

1. Create a new query named qryOperatorsthat includes all fields from tblCompensation
2. Set the criteria so that the query will display all operatorsthat are making less than a salary of $40,000and have a Review rating higher than 5.
3. Save and close the file. Submit Access_Problem1_LastFirst to the Canvas.

Question 220 pts

Sports Jersey Sales

You are the new clothing sales manager for the local sports store. You need to create a form that displays each customer’s information. You also have been asked to create a professional looking report for management that contains this information.

Create a Form for All Customers

You will create a new form that displays all customers.

1. Open the file Access_Problem2 and save it as Access_Problem2_LastFirst.
2. Use the Form toolto create a new form with the record source oftblCustomer table.
3. Save the form as frmCustomers.
4. Edit the formatting of frmCustomers by making the following layout changes:

• Change the title to Customer Record.
• Set the form theme to Ion.
• Delete email label and text box.
• Delete the existing subform

Create a Report for Orders

Create a report that shows each customer, their order details, and total number of items ordered.

1. Create a report that includes the following (Hint:Use the report wizard):

• First Name and Last Name from tblCustomers
• Order ID and Quantity from tblOrders
• Item and Size fromtblProducts

1. View the report by tblCustomerwith No grouping and set the sort order to Order IDin ascending order.
2. Add asubtotal (use sum function) for Quantity.
3. Save the report as rptCustomers.
4. Change the report title to Customer Orders.
5. Verify that all data columns line up and there is no overlap of labels or data.
6. Remove the Summary for xxx subtotal label and change the Sum label to Total Quantity for each customer.
7. Save and close the file. Submit Access_Problem2_LastFirst to the Canvas.

Question 330 pts

Hotel Rental

You have been hired to help keep track of the rentals for a local hotel that allows their guests to check out the different beach and pool equipment by showing their room key. You will need to help them edit some of the queries your predecessor started to set up but never completed.

Create Lookup Table

You need to create a new lookup table to track rentals by rental type. You do not have time to catalog every rental item, so this is a step closer by knowing what type of equipment each guest checked out.

1. Open Access_Problem3 and save as Access_Problem3_LastFirst.
2. Create a new table using “Table Design”. Name the table as tblCategoryand add the following data fields:

• Field name:CategoryID, Data Type: AutoNumber, Set this field as the primary key
• Field name:CategoryName , Data Type: ShortText, Set the field size to 25

Open the table in Datasheetview and add the following in CategoryName records:

• Beach Equipment
• Sport Equipment
• Pool Equipment
• Towels

1. Save and close the table.

Add Validation

To make sure employees are entering valid data, you will need to put in some data validation points.

1. Open tblCustomerstable and make the field RoomNumberrequired.
2. Create a validation for RoomNumber that a user must enter a room number between 100and 599. (Hint: You can use AND between two criteria)
3. Create a validation message when a user tries to enter a number outside of the allowed range Room Numbers must be between 100 and 599.

Control User Inputs

You need to further help employees enter valid data by making sure users enter phone numbers in the correct format and by providing a dropdown list of categories for them to select.

1. Open tblCustomersand add a new field PhoneNumber with Short textdata type. Then, add a phone number input mask to have appropriate hyphon and parenthesis.
2. Add a new customer record with the following data:

• First Name = Joe
• Last Name = Brown
• Room Number = 462
• Active = Yes (Check the box)
• Phone Number= 305-555-1245

1. Save and close the table.
2. Open tblRentalsand change the fieldRentalType to be a lookup of the Category Names you created in tblCategory (The table you created in steps a through d).

Hint 1: Choose the CategoryName field as the field you want to include in your lookup field.

Hint 2: To make sure you successfully created the lookup field, open your table in datasheet view and check if you have a list in RentalType field for each record.

1. Open tblRentalstable in Datasheet view and add a new rental record with the following data:

• Customer ID = 25
• Rental Type = Towel
• Checkout = Today (Insert today’s date)
• Due = 2 Days from Today
• CheckIn = null (Leave it blank)

1. Save and close the table.

Generate Date Data and Set Query Parameter

You need to generate a query that returns rentals that are within a given month provided by the employee.

1. Open qryRentalByMonthquery.
2. Add a new field Monththat is calculated by returning the monthof the CheckOut field. Make sure to name the field as Month.

Hint 1: Use the appropriate built-in function.

Hint 2: Consider “firstdayofweek” and “firstweekofyear” as 0.

1. You need to create a parameter queryin this step. Create a new prompt Enter month numberin the Month field that employees must enter when running the query that will only return rentals where the month field equals the input.
2. Save and close the query.

Use Advanced Functions

You need to generate a query that returns active rentals. You also need to add a column that determines if rentals were returned on time or not.

1. Open qryActiveRentals.
2. Add a new field and name it Activethat checks to see if the CheckIn date is null. 
3. Set the criteria to only return results where theActivefield is -1.
4. Save and close the query.
5. Open qryRentalByMonth query.

Hint: If you set the prompt correctly in step p, you will see the enter parameter dialog box pops up, click ok to open the query and switch to design view to do the next step.

1. Add a new field and name it Status. Use an appropriate built-in function that compares the Return field against the Due field and returns either On Time or Overdue based on the result of the calculation. Return the results as follows:

• If checkIn>Due, return Overdue
• If checkIn<Due, return On Time

1. Save and close the file. Submit Access_Problem3_LastFirst to the Canvas.

Question 410 pts

SQL 1

For this problem, you need to upload only one file that contains two separate codes for each part of the question. The file type can be MS Word or Text Document.

In this problem, refer to the attached picture that depicts a Microsoft Access database. 

Note that the field UnitsOnOrder in the Products table is the product’s total number of units on order from suppliers, whereas the Quantity field in ProductsOrders tables indicates the number of units of a product in a particular customer order. The ProductUnitPrice field in the Product table is the price charged to customers (or the unit price). The OrderDate field in the Orders table contains only a date, not a specific time of day.

Part 1:

The operations group has requested a report showing, for each product, the difference between the number of units in stock and the reorder level; in particular, negative values of this difference indicate products that need to be reordered.

To satisfy their request, write a SQL query that shows the name of each product, the unit price, the number of units in stock, the reorder level, and the difference between the units in stock and reorder level (named “UnitStatus”). Sort the results alphabetically by product name.

Part 2:

The accounting department would like to determine the value of inventory currently on order from suppliers, itemized by product.

Create a query that shows the name of each product, its unit price, the number of units on order from suppliers, and the total dollar value of the units on order (called “ValueOnOrder”).

Products that currently have zero units on order from suppliers should not appear in the query output. The value of each unit on order is considered to be the same as the corresponding ProductUnitPrice charged to customers.

SQL 2

For this problem, you need to upload only one file that contains two separate codes for each part of the question. The file type can be MS Word or Text Document.

In this problem, refer to the attached picture that depicts a Microsoft Access database. This database shows college enrollments for a single semester. The gender field in the Students table contains “M” for males and “F” for females, and the grade field in the Enrolls table contains a numerical grade, such as 4.0 for an “A”, 3.0 for a “B”, and so forth. Departments in the Courses table are identified by their names, which are unique.

Part 1:

Assuming a fee of $450 per credit, write a SQL query to calculate each student’s tuition. It should show each student’s student number, name, and total tuition for the semester (you need to call it "Tuition").

Make sure to save the Students table as STU, the Enrolls table as ENR, the Courses table as CRS, and the Sections table as SEC.

HINT: The total tuition for The semester is the multiplication of the total number of credits by the tuition fee per credit.

Part 2:

Write a SQL query to find the number of female enrollments in each department.

The same woman signing up for two different courses in the same department counts as two separate enrollments.

The query should show the name of each department and its number of female enrollments (you need to call it "TotalWomen"), and it should be sorted from largest to smallest number of enrollments, largest first.

Make sure to save the Students table as STU, the Enrolls table as ENR, the Courses table as CRS, and the Sections table as SEC.

HINT: In order to find the number of female enrollments, you have to count the total student numbers assigned to enrolled females.