Question

[The following information applies to the questions displayed below.] Use the Access_Practice.accdb database to complete the following tasks in Access. Microsoft Access is required for this problem. Use the database to complete tasks a through g in Access. Answer the questions below after completing tasks d, e, and g, respectively. rev: 03_05_2021_QC_CS-254504 Problem 4-9 [LO 4-2] Go to the Relationships screen and connect the five tables, enforcing referential integrity. Calculate the total sales for each customer. Include CustomerID and CompanyName from the Customer table and the calculated sale amount from the Sale_Amount_ Calculation query. Name the query Total_Customer_Sales. Generate an e-mail user name for each employee using the first letter of the employee’s first name and the first five letters of the employee’s last name, e.g., Rod Smith = > rsmith. Include EmployeeID, EmployeeFirstName, and EmployeeLastName in the query. Name the query Employee_Email_Generator. Calculate the total sales for each month. Determine which customer had the highest average sales amount. (Hint: Sort in descending order.) Assume the employees earn a 5 percent commission on sales. Calculate the total commission due to each employee. Use two queries to do these calculations. Required: e. Which customer had the highest average sales amount? What specifically was their average amount? f. Which employee had the highest earned commission? What specifically was their commission amount?

Answer 1

To complete the tasks a through g in Access, you will need to open the Access_Practice.accdb database in Microsoft Access and navigate to the appropriate screens to create queries and perform calculations. Here are some general steps to follow:

a. Go to the Relationships screen and connect the five tables, enforcing referential integrity. This involves linking the primary keys in one table to the foreign keys in another table, so that the data is consistent and accurate. To do this, you will need to open the Relationships screen, add the tables to the design grid, and create relationships between the appropriate fields. You will also need to enable the "Enforce Referential Integrity" option to ensure that the data is consistent across tables.

b. Calculate the total sales for each customer. To do this, you will need to create a query that joins the Customer and Sale tables, calculates the sum of the sales for each customer, and includes the CustomerID, CompanyName, and calculated sale amount in the results. You can use the "Sale_Amount_Calculation" query to calculate the sale amount for each sale.

c. Generate an email username for each employee. To do this, you will need to create a query that concatenates the first letter of the employee's first name with the first five letters of their last name, and includes the EmployeeID, EmployeeFirstName, and EmployeeLastName in the results.

d. Calculate the total sales for each month. To do this, you will need to create a query that joins the Sale and Product tables, calculates the sum of the sales for each month, and includes the month and calculated sale amount in the results. You can use the DatePart function to extract the month from the SaleDate field.

e. Determine which customer had the highest average sales amount. To do this, you can sort the Total_Customer_Sales query in descending order by the calculated sale amount, and select the top row to find the customer with the highest average sales amount.

f. Calculate the total commission due to each employee. To do this, you will need to create two queries: one that calculates the total sales for each employee (by joining the Sale and Employee tables, calculating the sum of the sales for each employee, and multiplying by 0.05), and another that summarizes the results by employee and includes the EmployeeID and calculated commission amount. You can sort the results in descending order by the commission amount to find the employee with the highest earned commission.

Note that these are general guidelines, and the specific steps may vary depending on the structure and content of the Access_Practice.accdb database. It is important to follow best practices for database design and to test the queries and calculations to ensure they are accurate and reliable.

Step-by-step explanation: