Question
Use the Access_Practice.accdb database to complete the following tasks in Access. LO 4-2 The database contains three tables containing information about this companys sales process:
Use the Access_Practice.accdb database to complete the following tasks in Access. LO 4-2
The database contains three tables containing information about this companys sales process: Inventory, Sales, and SalesItems. Use the Relationships window to link the tables together.
The SalesItems table records the quantity and price of each item sold on each sale (sales may include more than one item). Calculate the extended amount of sale (call it Amt) for each item (Quantity * UnitPrice). Include InvoiceID, InventoryID, Quantity, and UnitPrice in the query. Name the query Item_Extension_Calculation.
Calculate the total dollar amount of each sale. Include InvoiceID, InvoiceDate, CustomerID, and EmployeeID from the Sales table and the Amt from the Item_Extension_Calculation query. Name the query Sale_Amount_Calculation.
Calculate total sales for each inventory item.
Calculate total sales.
Calculate the month in which each sale occurred. Include InvoiceID and InvoiceDate from the Sales table. Name the query Sales_Months. (Hint: Look for the Month function in the expression builder.)
Calculate the sum of sales for each month.
This problem continues Problem 8. Use the Access_Practice.accdb database that you have been working on to complete the following tasks. 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 employees first name and the first five letters of the employees 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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started