BUAD 2020 - Assignment #5 Database Queries - Microsoft Access.

plsss help I am unsure how to do this assignment I just need some assistance

Download the A5_SalesCo.accdb database file and review the tables, fields and relationships to gain a better understanding of the data. For your convenience, the relationships are posted below. Create and execute queries that will answer the following 12 questions. You may use the graphical design view of Access to write your queries for this assignment. Save each of the queries to the database file - using the naming convention of Q01,Q02,Q03Q12 - corresponding to the question number. Questions 112:4 points each. 1. Working in the Accounting area of the business, you are interested in getting a list of Customers along with their outstanding balance (cust_balance). Order the data by the customer last name. Format the balance to display with a currency format. 2. You find that listing all customers does not make it easy to see who to focus our collection efforts on, re-write Q1 to display only those customers with a balance greater than $3,000 and order the data in descending sequence by the customer's balance. 3. You were just given your sales territory (Connecticut) and you need to get a list of all of your customers in that state with all of their vital information. 4. As a sales manager, you are interested in getting a list of all invoices for the 4th Quarter of 2017 (October 1 - December 31). Include the invoice number, date and total from the invoice table, also display the employee name (first and last) from the Employee table and calculate and include the commission earned using the formula of (inv_total * emp_comm). Format the display of any currency type data to display as such. 5. As a salesperson, you want a list of all product SKU, description and prices for our Exterior (prod_type), Water based (prod_base), Primer (prod_category) products. 6. As a salesperson, you need to get all of the pertinent information for all varnish type products before talking with one of your customers. A Varnish type product has the word varnish somewhere in the produce description. Order the data by product description. 7. As the inventory manager, you need to see any products that need replenishment. A product needs replenishment if the prod_QOH is less than the prod_MIN column. Display the product's SKU, Description, QOH and MIN. 8. As an HR analyst, you have been tasked with reaching out to your most junior employees and need a list of employees hired after Dec 31st,2010 who have a title of Associate or Driver. You need their full names along with their emails. Order the data by last name. Do NOT display the employee's' date of hire nor their title. 9. As an HR analyst, you need to know who the managers of the company are (by having the word "Manager" as part of the employee's title. Create a list that displays the Employee number, name and title. 10. As the newly hired VP of marketing, you need to get a count of customers in each of the states covered by your company. Order the data by State. 11. To find out which states provide the most sales, you need to get a total of all sales (inv_total) for each of the states. Order the data by sales in descending sequence. 12. You need to identify your 5 best employees (in terms of sales) and their total sales. Create a Word document to assemble a simple to grade solution document. Use the naming convention, Q01, Q02, Q03 ... etc. Also, add the following data for each of the questions (queries); Create a Word document to assemble a simple to grade solution document. Use the naming convention, Q01, Q02, Q03 ... etc. Also, add the following data for each of the questions (queries): - Identify the question number - Copy and paste a screen shot of the design view for your query - Copy and paste the query results - when more than 10 rows of data are returned from the query - snip only the first 10 . - Do NOT give me a full screen shot for these copy and paste images. Crop (or only snip) the relevant part of the screen. - Separate each question's solution by some visual delimiter (row of underscores, , or a line) Download the A5_SalesCo.accdb database file and review the tables, fields and relationships to gain a better understanding of the data. For your convenience, the relationships are posted below. Create and execute queries that will answer the following 12 questions. You may use the graphical design view of Access to write your queries for this assignment. Save each of the queries to the database file - using the naming convention of Q01,Q02,Q03Q12 - corresponding to the question number. Questions 112:4 points each. 1. Working in the Accounting area of the business, you are interested in getting a list of Customers along with their outstanding balance (cust_balance). Order the data by the customer last name. Format the balance to display with a currency format. 2. You find that listing all customers does not make it easy to see who to focus our collection efforts on, re-write Q1 to display only those customers with a balance greater than $3,000 and order the data in descending sequence by the customer's balance. 3. You were just given your sales territory (Connecticut) and you need to get a list of all of your customers in that state with all of their vital information. 4. As a sales manager, you are interested in getting a list of all invoices for the 4th Quarter of 2017 (October 1 - December 31). Include the invoice number, date and total from the invoice table, also display the employee name (first and last) from the Employee table and calculate and include the commission earned using the formula of (inv_total * emp_comm). Format the display of any currency type data to display as such. 5. As a salesperson, you want a list of all product SKU, description and prices for our Exterior (prod_type), Water based (prod_base), Primer (prod_category) products. 6. As a salesperson, you need to get all of the pertinent information for all varnish type products before talking with one of your customers. A Varnish type product has the word varnish somewhere in the produce description. Order the data by product description. 7. As the inventory manager, you need to see any products that need replenishment. A product needs replenishment if the prod_QOH is less than the prod_MIN column. Display the product's SKU, Description, QOH and MIN. 8. As an HR analyst, you have been tasked with reaching out to your most junior employees and need a list of employees hired after Dec 31st,2010 who have a title of Associate or Driver. You need their full names along with their emails. Order the data by last name. Do NOT display the employee's' date of hire nor their title. 9. As an HR analyst, you need to know who the managers of the company are (by having the word "Manager" as part of the employee's title. Create a list that displays the Employee number, name and title. 10. As the newly hired VP of marketing, you need to get a count of customers in each of the states covered by your company. Order the data by State. 11. To find out which states provide the most sales, you need to get a total of all sales (inv_total) for each of the states. Order the data by sales in descending sequence. 12. You need to identify your 5 best employees (in terms of sales) and their total sales. Create a Word document to assemble a simple to grade solution document. Use the naming convention, Q01, Q02, Q03 ... etc. Also, add the following data for each of the questions (queries); Create a Word document to assemble a simple to grade solution document. Use the naming convention, Q01, Q02, Q03 ... etc. Also, add the following data for each of the questions (queries): - Identify the question number - Copy and paste a screen shot of the design view for your query - Copy and paste the query results - when more than 10 rows of data are returned from the query - snip only the first 10 . - Do NOT give me a full screen shot for these copy and paste images. Crop (or only snip) the relevant part of the screen. - Separate each question's solution by some visual delimiter (row of underscores, , or a line)